Get Stored Procedure Last Run Time

Hello!

Quick script to get the last time a stored procedure was executed in the database. The reason for the seemingly over-engineered script is that different query plans can be generated, meaning that stored procedures can appear more than once in the list.

SELECT OBJECT_NAME(qt.objectid) AS [StoredProcedure]
,qs.execution_count AS [Execution Count]
,qs.creation_time AS [Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
INNER JOIN (
SELECT OBJECT_NAME(qt.objectid) AS [sproc]
,qs.execution_count AS [count]
,MAX(qs.creation_time) AS [latest]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE qt.[dbid] = DB_ID()
GROUP BY OBJECT_NAME(qt.objectid)
,qs.execution_count
) AS subquery ON OBJECT_NAME(qt.objectid) = subquery.sproc
AND qs.creation_time = subquery.latest
WHERE qt.[dbid] = DB_ID()
AND subquery.sproc LIKE '%%'
GROUP BY OBJECT_NAME(qt.objectid)
,qs.execution_count
,qs.creation_time
OPTION (RECOMPILE);

Happy scripting!

Preventing Plan Regressions During Testing Slides

Hello!

Thanks to everyone who turned up at last nights SQL Supper to see me talk about preventing plan regressions during database testing. The testing framework I demoed was written by me, but the decision whether to make it open source or not is not mine. So the best I can do today is share the slides, and hopefully the framework will go online and be improved upon by the community.

RECOVERY vs NORECOVERY vs STANDBY

When restoring a database you can specify one of three recovery options; RECOVERY, NORECOVERY and STANDBY. Today I’m going to touch briefly on the RECOVERY and NORECOVERY options before delving deeper into the STANDBY mode as it has a few features that separate it from the either two options that are worth explaining.
Continue reading “RECOVERY vs NORECOVERY vs STANDBY”

Sup Dawg, I Heard You Like NULLS…

Quick note on NULLS: NULL means “Unknown Value”, not “0”. So adding a value to NULL results in NULL:

1 + NULL = NULL

Because NULL means “I Don’t Know”, adding two NULLS together does not equate to the same value; it’s still two indeterminate values.

NULL + NULL != NULL

You can work around this in T-SQL by using COALESCE.


declare @i VARCHAR(8) = NULL
declare @j VARCHAR(8) = NULL
SELECT @i

SELECT @i + 'bob'

SELECT COALESCE (@i, 'bob')

if @i = @j
select 'yes'
else
select 'no'

Returning Values From Query Plans Using C#

Hello and welcome to yet another “Ronseal” title for a post…. and whilst this may not be something you’ll have to do regularly, searching for values in a query plan may be useful when running unit tests for SQL: you may be using it to confirm that a certain operator is used in the query plan, or whether a seek or scan is used… the possibilities are really endless. Continue reading “Returning Values From Query Plans Using C#”

Restore Database Using PowerShell

Quick script to restore AdventureWorks to a local instance. This script was designed to run on a local box, so the connection was trusted and therefore no username/password was required. This also goes some way to explain why I am using the location of the master database files as the same place to restore the AdventureWorks database! But with only a few changes it can easily be altered to restore any database, and the file location of the mdf/ldf’s can also be altered to reflect different locations.

The script needs to be saved in the same location as the .bak file for it to run successfully.

#restore adventureworks
$path = get-childitem $PSScriptRoot -include "AdventureWorks2014.bak" -recurse
$SQLDatabase = $path.Name
$SQLDatabase = $SQLDatabase.TrimEnd(".bak")

$SQLConn = New-Object System.Data.SQLClient.SQLConnection
$SQLConn.ConnectionString = "Server=$SQLServer; Trusted_Connection=True"

try
{
$SQLConn.Open()

write-host "success" -ForegroundColor Green
$SqlConn.Close()
}
catch {
Write-warning "An exception was caught while attempting to open the SQL connection"
Break
}

$SQLConn.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command = $SQlconn.CreateCommand()
$Command.CommandTimeout =0
$Command.CommandText = "select top 1 physical_name
from master.sys.master_files
where database_id = 1"
try{
$Reader = $Command.ExecuteReader()
while ($Reader.Read())
{
$r = $Reader.GetValue($1)
$r = $r.ToString()
$r = $r.TrimEnd("\master.mdf")
}
$SQLConn.Close()
}
catch
{
write-host "something went wrong"
write-host "$_"
}

$SQLConn.Open()
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand
$SQLcmd = $SQLconn.CreateCommand()
$sqlcmd.commandtimeout=0
$SQLcmd.CommandText="IF EXISTS(select * from sys.databases where name='$SQLDatabase')
ALTER DATABASE $SQLDatabase
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE $SQLDatabase
FROM DISK = '$path'
WITH  FILE = 1, REPLACE,
MOVE N'AdventureWorks2014_Data'
TO N'$r\AdventureWorks2014_Data.mdf',
MOVE N'AdventureWorks2014_Log'
TO N'$r\AdventureWorks2014_Log.ldf'"

$starttime = Get-date
try{
$SQLcmd.Executenonquery() | out-null
write-host "aventureworks deployed" -ForegroundColor Green
}
catch{
write-warning "An Exception was caught while restoring the database!"
write-warning "$_"
write-warning "attempting to recover the database"
}
Write-Host "Press Any Key To Exit"
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
(Get-Host).SetShouldExit(0)

Happy Scripting!

“Fun” with T-SQL String Functions

… and when I mean “fun” I mean “staring blankly at the screen while I figure out what I want to do and how to do it”.

I’ve written about string function many times before, and have used them extensively in many scripts I have written, but there’s always something new that comes up. Today, I wanted to get the file location of where the database files are stored and create some more files using dynamic SQL. Continue reading ““Fun” with T-SQL String Functions”

Enterprise Schema Big-Ish Data Scripts

Hello!

Last week I presented for the first time at a SQL User Group (SQL Supper.) There was a lot of content, and plenty of demos, and a few people came up to me afterwards and asked for the scripts. In case you were too shy to ask, or just want a simple database that uses partitioning and Clustered Columnstore Indexes, the scripts attached will provide you with such a thing.

final

The “00_final_create_dataload_and_populate” script will take some time to run. It basically creates a db that the later demos load data from. The “00_Reset_Partition_CCI_DB” clears all the objects out of the database that is created in the “03_final_create_partition_cci_db”. You will want to uncomment the T-SQL and run it in the “03_final_create_partition_cci_db”, and then from here on in you can just run the  “00_Reset_Partition_CCI_DB” to get the database to a working order.

Hopefully that explains how it all hangs together. Any questions let me know.

Forcing SQL Server To Order Operations

The subject of my last post was to do with returning DBCC CHECKDB results from a temp table, which was populated by running DBCC DBINFO against each database hosted on the SQL instance. I shared a query that returned any databases that had not had a CHECKDB run against it for over 7 days. The query is below:

 
select dbi.Value from #db dbi
WHERE field LIKE '%lastknow%'
AND (CASE WHEN isDate(dbi.value) = 1 THEN CAST (dbi.Value AS DATETIME2(3))END) < DATEADD (DAY,-7, GETDATE())

That “CASE” statement stands out quite a bit doesn’t it. Let’s explore why this query needs a CASE expression. Begin by creating a temp table that contains a sample of the data stored in the temp table. Continue reading “Forcing SQL Server To Order Operations”

Making “Check Last CHECKDB Date” A LOT Easier

One of the most important duties of a DBA is to ensure that CHECKDB is run frequently to ensure that the database is both logically and physically correct. So when inheriting an instance of SQL, it’s usually a good idea to check when the last CHECKDB was last run. And ironically enough, it is actually quite difficult to get this information quickly, especially if you have a lot of databases that you need to check. The obvious way is to run DBCC DBINFO against the specific database. This returns more than just the last time CHECKDB was run, and it is not especially clear which row returned tells us the last CHECKDB (FYI the Field is “dbi_dbccLastKnownGood”.) Continue reading “Making “Check Last CHECKDB Date” A LOT Easier”