T-SQL Tuesday#17: It’s all about APPLYcation this time

imageIt’s time for another round of T-SQL Tuesday and this round of the revolving blog party is being hosted by Matt Velic [Blog | Twitter].

APPLY – That is the topic for this month’s T-SQL Tuesday! The APPLY operator was added to the T-SQL repertoire and which has resulted in lesser use of cursors for a large number of diagnostic scripts that CSS uses to collect data while working on SQL Performance issues. In this blog, I shall share a few examples of such queries that we use to collect data while working on SQL Performance cases.

TOP Query Plan Statistics

The following query gives you a list of the SQL batches/procedures with their CPU usage, Query/Batch duration and Physical Reads rank. This query helps identify the TOP CPU/Duration/Read consuming queries by making use of system DMVs. The output below is useful for the following reasons:

1. I get the usecount of the procedure/batch and if this batch is called multiple times and the use count of a Compiled Proc cached object is only 1, then the plan is not being re-used. This now tells me that I need to look at reasons behind inability of plan re-use.

2. I get the total and average resource usage statistics for each of the queries listed in the output.

3. A quick glance at the output gives me an idea of the most expensive queries on the instance w.r.t. reads or/and CPU and/or query duration.


LEFT(p.cacheobjtype + ' (' + p.objtype + ')',35) AS cacheobjtype,


p.size_in_bytes/1024  AS size_in_kb,

PlanStats.total_worker_time/1000 AS tot_cpu_ms,

PlanStats.total_elapsed_time/1000 AS tot_duration_ms,







LEFT(CASE WHEN pa.value = 32767 THEN 'ResourceDb' ELSE ISNULL(DB_NAME(CONVERT(sysname,pa.value)),CONVERT(sysname,pa.value)) END,40) AS dbname,


CONVERT(nvarchar(50), CASE WHEN sql.objectid IS NULL THEN NULL ELSE REPLACE(REPLACE(sql.[text],CHAR(13),' '),CHAR(10),' ') END) AS procname,  REPLACE(REPLACE(SUBSTRING(sql.[text],PlanStats.statement_start_offset/2+1,CASE WHEN PlanStats.statement_end_offset=-1 THEN LEN(CONVERT(nvarchar(max),sql.[text]))

ELSE PlanStats.statement_end_offset/2 - PlanStats.statement_start_offset/2+1 END),CHAR(13),' '),CHAR(10),' ') AS stmt_text












ROW_NUMBER()OVER ( ORDER BY stat.total_worker_time DESC ) AS CpuRank,

ROW_NUMBER()OVER ( ORDER BY stat.total_physical_reads DESC ) AS PhysicalReadsRank,

ROW_NUMBER()OVER ( ORDER BY stat.total_elapsed_time DESC ) AS DurationRank

FROM sys.dm_exec_query_stats stat

) AS PlanStats

INNER JOIN sys.dm_exec_cached_plans p

ON p.plan_handle =  PlanStats.plan_handle

OUTER APPLY sys.dm_exec_plan_attributes ( p.plan_handle ) pa

OUTER APPLY sys.dm_exec_sql_text ( p.plan_handle ) AS sql



OR PlanStats.PhysicalReadsRank<50

OR PlanStats.DurationRank<50)



ORDER BY tot_cpu_ms DESC

Top Queries with Similar Query Hash and Query Plan Hash


SELECT TOP 10 query_plan_hash, query_hash,

COUNT (distinct query_plan_hash) as 'distinct query_plan_hash count',

SUM(execution_count) as 'execution_count',

SUM(total_worker_time) as 'total_worker_time',

SUM(total_elapsed_time) as 'total_elapsed_time',

SUM (total_logical_reads) as 'total_logical_reads',

MAX(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1,CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text])) ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1 END), CHAR(13), ' '), CHAR(10), ' '))  AS sample_statement_text,

MIN(CAST(query_plan as varchar(max))) AS 'ShowPlan XML'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as sp

GROUP BY query_plan_hash, query_hash

ORDER BY sum(total_worker_time) ASC;

This is a query which can help you identify queries which have the same query plan hash

SQL Server Books Online topic “Finding and Tuning Similar Queries by Using Query and Query Plan Hashes” has more information on this topic. The query hash feature was added in SQL Server 2008 which made it easier to troubleshooting performance issues caused by ad-hoc queries which differed in just literal values. RML Utilities does a similar task by creating query hash but now if you are troubleshooting on the server, you can do this using DMVs without having to capture a profiler trace.

The right operand supplied to the Apply operator is a function of one or more column values that are present in the left operand. So basically, the right operand is a table-valued expression of which is evaluated once for each row that appears in the left operand. The Cross Apply and Outer Apply are the two flavors of the Apply operator. So if I wanted to simulate an Apply Operation without the Operator itself, it would require the use of temporary tables or table variables.

I use the APPLY operator a lot while parsing XML data like Process Monitor traces or XML query plans which make life a lot easier and saves me from writing a huge bunch of T-SQL code.

How to get File Space used information

There are multiple ways to get this done. Starting from SQL Server 2005, you can get this information using the system logs to get the similar kind of information using system catalogs.

This can be achieved using sys.master_files and FileProperty function. The query below will give you the same information as you see in the Object Explorer window when you do the following: Right Click database name –> Tasks –> Shrink –> Files.

EXEC sp_MSforeachdb 'SELECT DB_NAME() as database_name,
name as [File Name],
physical_name as [Physical Name],
size/128.0 as [Total Size in MB],
size/128.0 - CAST (FILEPROPERTY(name,''SpaceUsed'') as int)/128.0 AS [Available Space in MB]. [file_id]
FROM sys.database_files;'

How to fetch all Login Failed messages from default traces

I was recently investigating a login failed issue on a SQL instance and required to quickly find out all the Login Failed events that had occurred on the server. I could have scoured the SQL Errorlogs but I didn’t want to do this due to the bloated size of the Errorlogs. An easier way would be to read the Default Traces (available from SQL Server 2005 and above) and look for all the Audit Login Failed events (event class = 20) using fn_trace_gettable function.

The query that can be used to get this information is given below:

declare @path varchar(1000)

select @path = path from sys.traces where id = 1

select trc.Spid, TextData, State, Error, StartTime, EventSubClass, LoginName, NTDomainName, NTUserName, ClientProcessID, HostName, ApplicationName

from fn_trace_gettable(@path,DEFAULT) trc

where EventClass = 20

order by StartTime desc

Script to remove a constraint and drop columns

Recently there was a need to get rid of a replication column after replication was disabled on a SQL Server 2000 database. The customer wanted to get rid of a column for all the tables which replication had added. And the flip side was that this column had a constraint defined on it. I looked at the constraint names and found that they had a common sub-string “msrepl_tran”. So by joining the system catalogs syscontraints and syscolumns, we were able to create the necessary T-SQL scripts to drop the constraint and the column from the tables involved.

select 'ALTER TABLE [' + OBJECT_NAME(a.id) + '] drop constraint [' + OBJECT_NAME(a.constid) + ']' + CHAR(13) +

'alter table [' + OBJECT_NAME(a.id) + '] drop column ['+ b.name + ']' + CHAR(13) + 'go'

from sysconstraints a

inner join syscolumns b on a.id = b.id

where OBJECT_NAME(a.id) like '%msrepl_tran%'

NOTE: Use CTRL+T and F5 to generate the script above. Inspect the output to ensure that no unintended table gets affected.

Converting Restore Filelistonly command into Restore Database command

Very often I need to restore database backups for in-house reproes of issues that I am currently troubleshooting. This can be quite cumbersome if the original database had a bunch of database files. This prodded me along the direction of writing a T-SQL script to help me generate the RESTORE DATABASE command from a RESTORE FILELISTONLY output from a backup file.

-- Create the stored procedure to create the headeronly output 
set nocount on go
create proc #sp_restoreheaderonly 
@backuplocation varchar(max) 
restore filelistonly from disk = @backuplocation 
declare @backuplocation varchar(max),@filelocation varchar(255),@sql varchar(max),@dbname varchar(255) 
set @backuplocation = 'C:\BackupFile.BAK' --Replace with backup file location 
set @filelocation = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\' -- Replace with destination data folder location 
set @dbname = 'RestoredDB' -- Replace with destination database name 

create table #tblBackupFiles
(LogicalName varchar(255),
PhysicalName varchar(255),
Type char(1),
FileGroupName varchar(50),
Size bigint,
MaxSize bigint,
FileId int,
CreateLSN numeric(30,2),
DropLSN numeric(30,2),
UniqueId uniqueidentifier,
ReadOnlyLSN numeric(30,2),
ReadWriteLSN numeric(30,2),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(30,2),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly int,
IsPresent int,
TDEThumbprint varchar(10))

-- Execute above created SP to get the RESTORE FILELISTONLY output into a table

insert into #tblBackupFiles
exec #sp_restoreheaderonly @backuplocation

-- Build the T-SQL RESTORE statement
set @sql = 'RESTORE DATABASE ' + @dbname + ' FROM DISK = ''' + @backuplocation +  ''' WITH STATS = 1, '

select @sql = @sql + char(13) + ' MOVE ''' + LogicalName + ''' TO ''' + @filelocation + LogicalName + '.' + RIGHT(PhysicalName,CHARINDEX('\',PhysicalName)) + ''','
from #tblBackupFiles
where IsPresent = 1

set @sql = SUBSTRING(@sql,1,LEN(@sql)-1)

-- Get the RESTORE DATABASE command

print @sql

-- Cleanup temp objects
drop procedure #sp_restoreheaderonly;
drop table #tblBackupFiles

I created the above script which currently works on SQL Server 2008 and above. The above T-SQL script will look into the Restore Filelistonly output from a backup file and generate the Restore Database command using the Logical and Physical filenames. Another limitation of the above script is that it assumes that there is only 1 database backup in the backup file.

How to find out if a table exists on the instance

 Once in a while I find that need to find out if a particular table name exists in any of the databases of my SQL Server instance. This is when sp_MSforeachdb comes into play.

EXEC sp_MSforeachdb '
if exists(select * from ?.sys.objects where type = ''U'' and name like ''%log%%'' and is_ms_shipped = 0)
    print ''Database Name: '' + ''?''
    select * from ?.sys.objects where type = ''U'' and name like ''%log%%'' and is_ms_shipped = 0

The above piece of T-SQL code finds out if there exists a user table which has a string “log” in it’s name on all the databases of the instance. You can keep modifying this query by adding more filters in the WHERE clause. Current filters only looks for tables which are not shipped by Microsoft (Eg. user tables created by replication, database tuning advisor, log shipping etc.). Basically any user table created by you. If you want to use this on a SQL Server 2000 instance, then you would need to use sysobjects instead of sys.objects.

Cursor coding horrors

Most coders have stereotypes of one kind or another. Given an opportunity, the developer will give into their stereotype. The coding guidelines is what keeps them from using it. I personally have a coding stereotype in T-SQL when defining a loop. Cursors are one of the most commonly used coding constructs. However, I tend to stick to my trusted friend a WHILE loop. I have worked on multiple issues where the T-SQL developer got the next FETCH wrong due to a oversight. Let me demonstrate that with an example. I have a T-SQL sample code which prints out all the user sessions connect to the SQL instance. 

declare @session_id int 

declare cur_sysprocesses cursor for 

select session_id from sys.dm_exec_requests 

where session_id > 50 

open cur_sysprocesses 

fetch next from cur_sysprocesses into @session_id 

while @@FETCH_STATUS <> -1 


print @session_id 

if (@session_id > 50) 

fetch next from cur_sysprocesses into @session_id 


close cur_sysprocesses 

deallocate cur_sysprocesses

I have the next FETCH inside the while loop and the loop is governed by the value of @@FETCH_STATUS. Since, I have defined it as NOT EQUAL TO –1, the cursor will end when the next fetch is unsuccessful. This is a perfectly alright. Not all cursors in a production environment are that simple. Some of them have levels of nesting and WHILE loops which have deep levels of nesting along with conditional blocks. So, let me modify the example and show you: 

declare @session_id int 

declare cur_sysprocesses cursor for 

select session_id from sys.dm_exec_requests 

open cur_sysprocesses 

fetch next from cur_sysprocesses into @session_id 

while @@FETCH_STATUS <> -1 


print @session_id 

if (@session_id > 50) 

fetch next from cur_sysprocesses into @session_id 


close cur_sysprocesses 

deallocate cur_sysprocesses

Now the problem with the above cursor is that most system processes have a session id below 50. So, now my cursor will loop through infinitely because the next fetch will never occur since the check in the conditional block will never evaluate to true as the first fetch from the cursor will always result in a system spid lesser than 50. This seems like a really amateur mistake which you think is not possible. But believe me, this happens! Since, I am showing this to you with a common system DMV, you think this is not possible. However, when you are using user defined tables and cursors, this is quite possible. Never define your next fetch from the cursor within the WHILE loop in a conditional block which is dependant on the data being fetched from the table. 

Always have the next FETCH defined in the first level of the while loop. Never ever put this in a conditional block. Always find an alternative way to find out a workaround or a different way to decide on whether to process the data fetched from the cursor. The next FETCH from the cursor should NOT be defined in an IF conditional block. 

It’s a human being that is coding the cursor. There is always a chance of some unforeseen circumstance (un-thought-of scenario) which makes your cursor go into a doomed state (read: infinite loop). 

You can never be dependant on your data to decide the fate of your code path. 

To summarize, the next FETCH while your looping through your cursor should be in the first level of your code unless and until you have no other choice and want to avoid an infinite loop scenario.