FrankMcB’s Blocking Script

This is my attempt at a “best” blocking script. I’ve hacked and chewed several other people’s together, tried to document where they came from within the code, and then put what I wanted on top of it all.

My Goal:

  • Find the Lead Blocker
  • The Type Of Lock It’s Holding
  • Show What They Are Blocking
  • Determine What Resource It’s Blocking On
  • Create A Script To Show The Row/Page It’s Blocking On
  • Make It Dynamic So Nothing Is Hard Coded To A Database
  • Resolve IDs to Real Names
  • Convert Obscure Codes to Human Readable Information
  • If any of the SQL is a Server Side API Cursor Fetch, Resolve it to the Original SQL Statement.


set nocount on


declare @i bigint
declare @dbname sysname
declare @dbid bigint
declare @sqlstmnt nvarchar(max)

IF OBJECT_ID('tempdb..AllBlockersLocks') IS NOT NULL DROP TABLE tempdb..AllBlockersLocks;


IF OBJECT_ID('tempdb..##AllBlocking') IS NOT NULL DROP TABLE ##AllBlocking;

WITH LeadBlockers (spid, milliseconds, waittype, blocker, blockee, level, dbname,dbid, sqlstmnt)
AS
(
 SELECT spid,waittime 'milliseconds', s.waittype, s.blocked as blocker, s.spid as blockee, 0 as level, dbname = SUBSTRING(DB_NAME(dbid),1,20), dbid, (SELECT text FROM ::fn_get_sql(s.sql_handle)) sql
 FROM master..sysprocesses s
 WHERE blocked = 0 AND EXISTS (SELECT * FROM master..sysprocesses inside WHERE inside.blocked = s.spid)
 UNION ALL
 SELECT s.spid, waittime 'milliseconds',s.waittype, s.blocked as blocker, s.spid as blockee, level + 1, SUBSTRING(DB_NAME(s.dbid),1,20), s.dbid, (SELECT text FROM ::fn_get_sql(s.sql_handle)) sql
 FROM master..sysprocesses s
 INNER JOIN LeadBlockers
 ON s.blocked = LeadBlockers.blockee
)
SELECT *
into ##AllBlocking
FROM LeadBlockers
ORDER BY level




print ''
print '****************************************'
print '*** LEAD BLOCKER(S) LOCKS HERE ***'
print '****************************************'
print ''

print ''
select count(distinct(spid)) 'Number of Lead Blocking Processes' from ##AllBlocking where level = 0
print ''
select distinct(spid) 'Unique Lead Blocking Process SPIDs' from ##AllBlocking where level = 0
print ''

select @i = spid from ##AllBlocking where blocker=0
select @dbid = dbid from ##AllBlocking where blocker = 0

select @i = spid from ##AllBlocking where blocker <> 0
select @dbname = dbname from ##AllBlocking where blocker <> 0

-- select @i = spid from ##AllBlocking where level = 0;
IF OBJECT_ID('tempdb..#TopLevelBlockersSpids') IS NOT NULL DROP TABLE #TopLevelBlockersSpids;
select spid into #TopLevelBlockersSpids from ##AllBlocking where level = 0;


print ''
print 'These Lead SPIDs Hold These Locks'
print '---------------------------------'
print ''


select @sqlstmnt = 
'IF OBJECT_ID(''tempdb..#LeadBlockersListTopLevel'') IS NOT NULL DROP TABLE #LeadBlockersListTopLevel;
select convert (smallint, req_spid) As spid,
 substring(db_name(rsc_dbid), 1, 15) As DatabaseName,
 substring(so.name, 1,15) as TableName,
 rsc_objid as objId,
 rsc_indid As IndId,
 ''ObjType'' =case when rsc_indid = 1 then ''ClustTbl'' when rsc_indid > 1 then ''Index'' else ''HeapTbl'' end,
 ''LockType'' =case 
 when substring (v.name, 1, 4) = ''RID'' then ''RowLock'' 
 when substring (v.name, 1, 4) = ''KEY'' then ''RowLock'' 
 when substring (v.name, 1, 4) = ''PAG'' then ''PageLock'' 
 when substring (v.name, 1, 4) = ''TAB'' then ''TableLock'' 
 else ''UNKNOWN'' end,
 substring (v.name, 1, 4) As Type,
 substring (rsc_text, 1, 32) as Resource,
 substring (u.name, 1, 8) As Mode,

 ''LockMode'' = case
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''X'' then ''Exclusive''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''S'' then ''Share''
 else ''UNKNOWN''
 end,

 substring (x.name, 1, 5) As Status
into #LeadBlockersListTopLevel
 from master.dbo.syslockinfo (nolock),
 master.dbo.spt_values v (nolock),
 master.dbo.spt_values x (nolock),
 master.dbo.spt_values u (nolock),
--> this statement is dynamic for each database involved. drive off the lead blockers top level.
 '+dbname+'..sysobjects so (nolock)

 where master.dbo.syslockinfo.rsc_type = v.number
 and v.type = ''LR''
 and master.dbo.syslockinfo.req_status = x.number
 and x.type = ''LS''
 and master.dbo.syslockinfo.req_mode + 1 = u.number
 and u.type = ''L''
-- and req_spid in (cast(@i as nvarchar(4)))
--> i get the top level locks held by the blockers here:
 and req_spid in (select spid from #TopLevelBlockersSpids)
 and so.id = rsc_objid
 and substring (u.name, 1, 8) = ''X''
 order by spid;' from ##AllBlocking

--print @sqlstmnt

 exec sp_executesql @sqlstmnt


 
IF OBJECT_ID('tempdb..AllBlockersLocks') IS NOT NULL DROP TABLE tempdb..AllBlockersLocks;
CREATE TABLE tempdb..AllBlockersLocks(
 [spid] [smallint] NULL,
 [DatabaseName] [nvarchar](15) NULL,
 [TableName] [nvarchar](15) NULL,
 [objId] [int] NOT NULL,
 [IndId] [smallint] NOT NULL,
 [ObjType] [varchar](8) NOT NULL,
 [LockType] [varchar](12) NOT NULL,
 [Type] [nvarchar](4) NULL,
 [Resource] [nvarchar](32) NULL,
 [ToView] [nvarchar](222) NULL,
 [Mode] [nvarchar](8) NULL,
 [LockMode] [varchar](15) NOT NULL,
 [Status] [nvarchar](5) NULL,
 [Level] [int] NULL,
 [Duration] [bigint] NULL,
 [Blockee] [smallint] NULL,
 [Blocker] [smallint] NULL,
 [BlockSpid] [smallint] NULL,
 [SQLStmnt] [nvarchar](4000) NULL,
 [rsc_text] [nchar](32) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_bin] [binary](16) NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_valblk] [binary](16) NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_dbid] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_indid] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_objid] [int] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_type] [tinyint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_flag] [tinyint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_mode] [tinyint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_status] [tinyint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_refcnt] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_cryrefcnt] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_lifetime] [int] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_spid] [int] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_ecid] [int] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_ownertype] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_transactionID] [bigint] NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_transactionUOW] [uniqueidentifier] NULL




-- set @sqlstmnt = ''

declare @sqlstmnt1 nvarchar(max)
declare @sqlstmnt2 nvarchar(max)

truncate table tempdb..AllBlockersLocks

-- select * from tempdb..AllBlockersLocks where mode = 'X'

select @sqlstmnt1 = 
'insert into tempdb..AllBlockersLocks
select convert (smallint, req_spid) As spid,
 substring(db_name(rsc_dbid), 1, 15) As DatabaseName,
 substring(so.name, 1,15) as TableName,
 rsc_objid as objId,
 rsc_indid As IndId,
 ''ObjType'' =case when rsc_indid = 1 then ''ClustTbl'' when rsc_indid > 1 then ''Index'' else ''HeapTbl'' end,

-- https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx
 ''LockType'' =case 
 when substring (v.name, 1, 4) = ''RID'' then ''RowLock'' 
 when substring (v.name, 1, 4) = ''KEY'' then ''RowLock'' 
 when substring (v.name, 1, 4) = ''PAG'' then ''PageLock'' 
 when substring (v.name, 1, 4) = ''TAB'' then ''TableLock'' 
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''DB'' then ''DBLock''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''FIL'' then ''DBFileLock''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''EXT'' then ''ExtentLock''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''APP'' then ''AppResLck''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''MD'' then ''MetaDataLck''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''HBT'' then ''HeapBTreeLck''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''AU'' then ''AllocUnitLck''
 else ''UNKNOWN'' end,
 substring (v.name, 1, 4) As Type,


-- http://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/

 substring (rsc_text, 1, 32) as Resource, -- PAG 1:288 
 ''ToView'' = case 
 when (substring (v.name, 1, 4) = ''PAG'') and (rsc_indid = 0 or rsc_indid=1) and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then 
 ''DBCC TRACEON(3604) DBCC PAGE( ''+char(39)+substring(db_name(rsc_dbid), 1, 15)+char(39)+ 
 '',''+
 rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),1,CHARINDEX('':'', rtrim(ltrim(rsc_text)))-1)))+
 '',''+
 rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),CHARINDEX('':'', rtrim(ltrim(rsc_text)))+1,10))) +'',3) WITH TABLERESULTS --dbcc ind(tempdb, x, -1)''

 -- RID 1:288:0 in x:y:z format
 when (substring (v.name, 1, 4) = ''RID'') and (rsc_indid = 0 or rsc_indid=1) and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then 
 ''DBCC TRACEON(3604) DBCC PAGE( ''+char(39)+substring(db_name(rsc_dbid), 1, 15)+char(39)+ 
 '',''+
 -- x is the file number
 rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),1,CHARINDEX('':'', rtrim(ltrim(rsc_text)))-1)))+
 '',''+
 substring(
 -- y:z
 rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),CHARINDEX('':'', rtrim(ltrim(rsc_text)))+1,10))),
 1,
 charindex('':'',rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),CHARINDEX('':'', rtrim(ltrim(rsc_text)))+1,10))))-1)
 +
 '',3) WITH TABLERESULTS --dbcc ind(tempdb, x, -1)''+
 '' -- select %%lockres%%,* from '' +substring(so.name, 1,15)+''(nolock) where %%lockres%% = ''+char(39)+
 rtrim(ltrim((cast(rsc_text as nvarchar(20))))
 )+char(39)


 when (substring (v.name, 1, 4) = ''TAB'') and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then ''EXCLUSIVE TABLE LOCK ON ''+substring(so.name, 1,15)

 when (rsc_indid = 1) and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then 
 ''EXCLUSIVE LOCK ON CLUSTIDX: ''+cast(rsc_indid as nvarchar(3))+'' ON OBJID ''+cast(rsc_objid as nvarchar(10))+'' TABLE NAME: ''+substring(so.name, 1,15)+
 '' -- select %%lockres%%, * from '' +substring(so.name, 1,15)+''(nolock) where %%lockres%% = ''+char(39)+
 substring(cast(rsc_text as nvarchar(20)),1,14 
 )+char(39)

 when (rsc_indid > 1) and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then 
 ''EXCLUSIVE LOCK ON NONCLUSTIDX: ''+cast(rsc_indid as nvarchar(3))+'' ON OBJID ''+cast(rsc_objid as nvarchar(10))+'' TABLE NAME: ''+substring(so.name, 1,15)

 else '''' 
 end,

 substring (u.name, 1, 8) As Mode,'


--declare @sqlstmnt2 nvarchar(max)
select @sqlstmnt2 = 
'
-- https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx
 ''LockMode'' = case
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''X'' then ''Exclusive''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''S'' then ''Share''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''IX'' then ''IntentExclusive''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''IS'' then ''IntentShare''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''U'' then ''Update''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''BU'' then ''BulkUpdate''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''SIX'' then ''SharedIntEx''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''IU'' then ''IntentUpd''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''SIU'' then ''ShIntentUpd''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''UIX'' then ''UpdIntentEx''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''Sch-M'' then ''SchemaMod''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''Sch-S'' then ''SchemaStability''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeS-S'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeS-U'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeIn-Null'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeIn-S'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeIn-U'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeIn-X'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeX-S'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeX-U'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeX-X'' then ''Range''
 else ''UNKNOWN''
 end,

 substring (x.name, 1, 5) As Status,
 ab.level as Level,
 ab.milliseconds as Duration,
 ab.blockee as Blockee,
 ab.blocker as Blocker,
 ab.spid as BlockSpid, 
 cast(ab.sqlstmnt as nvarchar(4000)) as SQLStmnt,
 master.dbo.syslockinfo.*
-- into tempdb..AllBlockersLocks
 from master.dbo.syslockinfo (nolock),
 master.dbo.spt_values v (nolock),
 master.dbo.spt_values x (nolock),
 master.dbo.spt_values u (nolock), ##AllBlocking ab (nolock),
--> this statement is dynamic for each database involved. drive off the lead blockers top level.
 '+dbname+'..sysobjects so (nolock)


 where master.dbo.syslockinfo.rsc_type = v.number
 and v.type = ''LR''
 and master.dbo.syslockinfo.req_status = x.number
 and x.type = ''LS''
 and master.dbo.syslockinfo.req_mode + 1 = u.number
 and u.type = ''L''
 and so.id = rsc_objid
 and ab.spid = req_spid' from ##AllBlocking


-- have to concat like this or else the string is bigger than 4000 bytes and you get a syntax error on the dynamic sql
declare @bigsql nvarchar(max)
set @bigsql = cast(@sqlstmnt1 as nvarchar(max))+cast(@sqlstmnt2 as nvarchar(max))

exec sp_executesql @bigsql


 select
 spid, 
 DatabaseName, 
 TableName, 
 objId, 
 IndId, 
 ObjType, 
 LockType, 
 Type, 
 Resource, 
 ToView, 
 LockMode, 
 Status
 from tempdb..AllBlockersLocks 
 where level = 0 and mode = 'X'
 order by level, spid





DECLARE @bspid AS INT;
DECLARE @bblocker AS INT;
DECLARE @bblockee AS INT;
DECLARE @blevel AS INT;
DECLARE @rowcounts AS INT;
DECLARE @nomore AS INT;
DECLARE @blockingchain AS INT;
DECLARE @currentlevel AS INT;
DECLARE @spacing AS NVARCHAR (20);


SET @blockingchain = 1;

print ''
print ''
print 'Here Is The Chain Of Events Being Blocked By The Above SPIDs'
print '------------------------------------------------------------'


-- first, get the top level blockers
DECLARE blocked_cursor CURSOR
 FOR SELECT spid,
 blocker,
 blockee,
 level
 FROM ##AllBlocking
 WHERE level = 0
 ORDER BY spid;

OPEN blocked_cursor;

FETCH NEXT FROM blocked_cursor INTO @bspid, @bblocker, @bblockee, @blevel;

WHILE @@FETCH_STATUS = 0
 BEGIN
 -- do a depth first tree search for each top level blocker spid
 SET @currentlevel = 1;
 SET @nomore = 0;
 SET @rowcounts = 0;
 WHILE (@nomore = 0)
 BEGIN
 -- check to see if there are any rows for this blocking chain
 SELECT @rowcounts = count(*)
 FROM ##AllBlocking
 WHERE blocker = @bspid
 AND level = @currentlevel;
 -- if there are, do a depth search of the tree to show the chain
 IF (@rowcounts > 0)
 BEGIN
 IF (@currentlevel = 1)
 BEGIN
 PRINT '';
 PRINT '';
 PRINT '***************************************************************************************************'
 PRINT '******************* MOST IMPORTANT HERE: LEVEL = 1 IS LEAD BLOCKER ********************************';
 PRINT '***************************************************************************************************'
 print ''
 END
 -- indent based on the level
 SET @spacing = replicate(' ', 5 * (@currentlevel - 1));

 IF (@currentlevel > 1)
 BEGIN
 PRINT '+++++++++++++++++++++++++++++++';
 END

 PRINT @spacing + 'blocking chain: ' + CAST (@blockingchain AS NVARCHAR (4)) + ' level: ' + CAST (@currentlevel AS NVARCHAR (4));
 

 -- show me what is blocking here
 SELECT @spacing + 'Spid: ' + CAST (blocker AS NVARCHAR (4)) + ' is blocking Spid ' + CAST (spid AS NVARCHAR (4))+char(10),
-- @spacing + 'Spid: ' + CAST (spid AS NVARCHAR (4)) + ' wants a '+LockMode+' '+LockType+' lock on object '+ObjType+' '+TableName +' and is being blocked by Spid '+ (select top 1 CAST (spid AS NVARCHAR (4))+' which has a '+ LockMode+' '+LockType+' lock on the same object.' from tempdb..AllBlockersLocks where spid = @bspid and Mode = 'X')
 @spacing + 'Spid: ' + CAST (isnull(spid,' ') AS NVARCHAR (4)) + ' wants a '+isnull(LockMode, ' ') +' '+isnull(LockType, ' ')+' lock on object '+isnull(ObjType, ' ')+' '+
 isnull(TableName, ' ') +' and is being blocked by Spid '--
 + case 
 when @currentlevel > 1 then (select top 1 CAST (isnull(spid,' ') AS NVARCHAR (4)) +' which has a ' + isnull(LockMode, ' ') +isnull(LockType, ' ') +' lock on the same object.' from tempdb..AllBlockersLocks where spid = @bspid order by Mode)
 when @currentlevel = 1 then (select top 1 CAST (isnull(spid,' ') AS NVARCHAR (4)) +' which has a ' + isnull(LockMode, ' ') +isnull(LockType, ' ') +' lock on the same object.' from tempdb..AllBlockersLocks where spid = @bspid and Mode = 'X' order by Mode)
 end
 FROM tempdb..AllBlockersLocks
 WHERE blocker = @bspid
 AND level = @currentlevel;

 if (@currentlevel = 1)
 begin
 IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
 select @spacing+'Spid '+CAST (spid AS NVARCHAR (4))+
 ' has a '+LockMode+' '+LockType+' on ['+ObjType+'] ['+ TableName + '] on Resource ['+rtrim(ltrim(Resource))+'] : '+ ToView 'Statement To See Row(s) *CAUSING* The Blocking' into #t from tempdb..AllBlockersLocks where spid = @bspid and Mode = 'X';
 select * from #t
 select top 1 @spacing + cast(SQLStmnt as nvarchar(max)) 'SQL Statement Thats Blocking' from tempdb..AllBlockersLocks where spid = @bspid and Mode = 'X';
 end


 -- select cast(SQLStmnt as nvarchar(max)) 'SQL Statement Thats Blocking' from tempdb..AllBlockersLocks


-- now go another level deeper in the tree for this chain
 SELECT @bspid = blockee
 FROM ##AllBlocking
 WHERE blocker = @bspid
 AND level = @currentlevel;

 SET @currentlevel = @currentlevel + 1;
 END
 IF (@rowcounts = 0)
 BEGIN
 -- if there are no more levels for the chain, the bail out of the loop and get the next lead blocker
 SET @nomore = 1;
 END
 END
 SET @blockingchain = @blockingchain + 1;
 -- get next lead blocker here
 FETCH NEXT FROM blocked_cursor INTO @bspid, @bblocker, @bblockee, @blevel;
 END

CLOSE blocked_cursor;

DEALLOCATE blocked_cursor;




DECLARE @fetchcount AS BIGINT;

SELECT @fetchcount = COUNT(*)
FROM tempdb..AllBlockersLocks
WHERE upper(CAST (SQLStmnt AS NVARCHAR (MAX))) LIKE '%FETCH%';

IF @fetchcount > 0
 BEGIN
 PRINT '';
 PRINT '*******************************';
 PRINT '*** FETCH TEXT HERE ***';
 PRINT '*******************************';
 PRINT '';
 DECLARE fetch_cursor CURSOR
 FOR SELECT spid
 FROM tempdb..AllBlockersLocks
 WHERE upper(CAST (SQLStmnt AS NVARCHAR (MAX))) LIKE '%FETCH%';
 DECLARE @spid AS BIGINT;
 OPEN fetch_cursor;
 FETCH NEXT FROM fetch_cursor INTO @spid;
 WHILE @@FETCH_STATUS = 0
 BEGIN
 -- now loop through and look for FETCH and show that if it's there
 -- http://www.sqlskills.com/blogs/joe/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch/'
 SELECT @spid 'spid', 
 c.session_id,
 c.properties,
 c.creation_time,
 c.is_open,
 t.text
 FROM sys.dm_exec_cursors (@spid) AS c CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) AS t;
 FETCH NEXT FROM fetch_cursor INTO @spid;
 END
 CLOSE fetch_cursor;
 DEALLOCATE fetch_cursor;
 END





/*

Sample Output:
 
****************************************
*** LEAD BLOCKER(S) LOCKS HERE ***
****************************************
 
 
Number of Lead Blocking Processes
---------------------------------
1

 
Unique Lead Blocking Process SPIDs
----------------------------------
57

 
 
These Lead SPIDs Hold These Locks
---------------------------------
 
spid DatabaseName TableName objId IndId ObjType LockType Type Resource ToView LockMode Status
------ --------------- --------------- ----------- ------ -------- ------------ ---- -------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------- ------
57 SilverSystem x 1571536682 1 ClustTbl RowLock KEY (052c8c7d9727) EXCLUSIVE LOCK ON CLUSTIDX: 1 ON OBJID 1571536682 TABLE NAME: x -- select %%lockres%%, * from x(nolock) where %%lockres%% = '(052c8c7d9727)' Exclusive GRANT
57 SilverSystem x 1571536682 1 ClustTbl RowLock KEY (9d6bf8154a2a) EXCLUSIVE LOCK ON CLUSTIDX: 1 ON OBJID 1571536682 TABLE NAME: x -- select %%lockres%%, * from x(nolock) where %%lockres%% = '(9d6bf8154a2a)' Exclusive GRANT

 
 
Here Is The Chain Of Events Being Blocked By The Above SPIDs
------------------------------------------------------------
 
 
***************************************************************************************************
******************* MOST IMPORTANT HERE: LEVEL = 1 IS LEAD BLOCKER ********************************
***************************************************************************************************
 
blocking chain: 1 level: 1
 
----------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Spid: 57 is blocking Spid 62
 Spid: 62 wants a IntentShare PageLock lock on object ClustTbl x and is being blocked by Spid 57 which has a ExclusiveRowLock lock on the same object.
Spid: 57 is blocking Spid 62
 Spid: 62 wants a IntentShare TableLock lock on object HeapTbl x and is being blocked by Spid 57 which has a ExclusiveRowLock lock on the same object.
Spid: 57 is blocking Spid 62
 Spid: 62 wants a Share RowLock lock on object ClustTbl x and is being blocked by Spid 57 which has a ExclusiveRowLock lock on the same object.

Statement To See Row(s) *CAUSING* The Blocking
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Spid 57 has a Exclusive RowLock on [ClustTbl] [x] on Resource [(052c8c7d9727)] : EXCLUSIVE LOCK ON CLUSTIDX: 1 ON OBJID 1571536682 TABLE NAME: x -- select %%lockres%%, * from x(nolock) where %%lockres%% = '(052c8c7d9727)'
Spid 57 has a Exclusive RowLock on [ClustTbl] [x] on Resource [(9d6bf8154a2a)] : EXCLUSIVE LOCK ON CLUSTIDX: 1 ON OBJID 1571536682 TABLE NAME: x -- select %%lockres%%, * from x(nolock) where %%lockres%% = '(9d6bf8154a2a)'

SQL Statement Thats Blocking
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin tran 
update x set cola = 3





*/



xxx

Paul Randal’s Wait Stats Script

I’m including this script just so I don’t need to hunt it down in the future… Paul is a very big brain in SQL Server. He worked in product engineering and owned the DBCC command and more.

From: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

 

WITH [Waits] AS
 (SELECT
 [wait_type],
 [wait_time_ms] / 1000.0 AS [WaitS],
 ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
 [signal_wait_time_ms] / 1000.0 AS [SignalS],
 [waiting_tasks_count] AS [WaitCount],
 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
 ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
 FROM sys.dm_os_wait_stats
 WHERE [wait_type] NOT IN (
 N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
 N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
 N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
 N'CHKPT', N'CLR_AUTO_EVENT',
 N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
 N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
 N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
 N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
 N'EXECSYNC', N'FSAGENT',
 N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
 N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
 N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
 N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
 N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
 N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
 N'PWAIT_ALL_COMPONENTS_INITIALIZED',
 N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
 N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
 N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
 N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
 N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
 N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
 N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
 N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
 N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
 N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
 N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
 N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
 N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
 N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
 N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
 N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
 AND [waiting_tasks_count] > 0
 )
SELECT
 MAX ([W1].[wait_type]) AS [WaitType],
 CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
 CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
 CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
 MAX ([W1].[WaitCount]) AS [WaitCount],
 CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
 CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
 CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
 CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
 ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO

 

Cleaning Up That Rat’s Nest of T-SQL

The following sites have a very good pretty printers:

http://www.tsqltidy.com

  • For whatever reason, sometimes this site “hangs” and doesn’t do anything.

http://www.dpriver.com/pp/sqlformat.htm

  • This site actually sells a pretty printer that you can run locally, in addition to the above web based site.

Many of the apps I work with are dynamic query generators with anywhere from 20 to 80 tables in a join. It’s code by a machine, for a machine.

It will turn this incomprehensible string…

SELECT SDSOQS,SDLTTR,SDPRP2,SDADTM,SDQTYT,SDRSDT,SDSHPN,SDCORD,SDUOM4,SDPRP1,SDSRP1,SDSHAN,SDUOM2,SDPDDJ,SDOPDJ,SDKCOO,SDSFXO,SDLOCN,SDDELN,SDDRQJ,SDPSN,SDSO16,SDPOE,SDDRQT,SDAN8,SDFEA,SDPEND,SDSHCCIDLN,SDSOCN,SDSPATTN,SDTHGD,SDRKCO,SDOPTT,SDPMTN,SDCOMM,SDOORN,SDSRP3,SDVR02,SDDOC,SDLNID,SDURAT,SDCRCD,SDASN,SDRSDJ,SDAEXP,SDPRP5,SDPRP3,SDPEFJ,SDUORG,SDOCTO,SDTORG,SDSONE,SDPPDJ,SDDOCO,SDDCTO,SDCNDJ,SDSOBK,SDZON,SDSWMS,SDTHRP,SDUPRC,SDPDTT,SDMOT,SDUOM,SDRCTO,SDCRR,SDPSIG,SDDSC2,SDDGL,SDDCT,SDADDJ,SDRORN,SDDMCT,SDNXTR,SDSO12,SDTRDJ,SDEMCU,SDAAID,SDVR01,SDALLOC,SDLOTN,SDRFRV,SDDSC1,SDITM,SDCARS,SDSRP5,SDLNTY,SDLITM,SDPRIO,SDUNCS,SDRKIT,SDRLLN,SDRLIT,SDOGNO,SDMCU,SDKCO,SDTPC,SDSRP2,SDDVAN,SDKITID,SDANBY,SDNUMB,SDFRTH,SDAITM,SDFRMP,SDSQOR,SDPMTO,SDPRP4,SDPA8,SDSRP4,SDIVD,SDOKCO,SDFRGD,SDFUC,SDFUP,SDSO15 FROM PRODDTA.F4211 WHERE ((((((SDLTTR >= @P0  AND SDLTTR <= @P1 ) AND SDLITM LIKE @P2 ) AND SDNXTR < @P3 ) AND SDSOBK > @P4 ))) UNION SELECT SDSOQS,SDLTTR,SDPRP2,SDADTM,SDQTYT,SDRSDT,SDSHPN,SDCORD,SDUOM4,SDPRP1,SDSRP1,SDSHAN,SDUOM2,SDPDDJ,SDOPDJ,SDKCOO,SDSFXO,SDLOCN,SDDELN,SDDRQJ,SDPSN,SDSO16,SDPOE,SDDRQT,SDAN8,SDFEA,SDPEND,SDSHCCIDLN,SDSOCN,SDSPATTN,SDTHGD,SDRKCO,SDOPTT,SDPMTN,SDCOMM,SDOORN,SDSRP3,SDVR02,SDDOC,SDLNID,SDURAT,SDCRCD,SDASN,SDRSDJ,SDAEXP,SDPRP5,SDPRP3,SDPEFJ,SDUORG,SDOCTO,SDTORG,SDSONE,SDPPDJ,SDDOCO,SDDCTO,SDCNDJ,SDSOBK,SDZON,SDSWMS,SDTHRP,SDUPRC,SDPDTT,SDMOT,SDUOM,SDRCTO,SDCRR,SDPSIG,SDDSC2,SDDGL,SDDCT,SDADDJ,SDRORN,SDDMCT,SDNXTR,SDSO12,SDTRDJ,SDEMCU,SDAAID,SDVR01,SDALLOC,SDLOTN,SDRFRV,SDDSC1,SDITM,SDCARS,SDSRP5,SDLNTY,SDLITM,SDPRIO,SDUNCS,SDRKIT,SDRLLN,SDRLIT,SDOGNO,SDMCU,SDKCO,SDTPC,SDSRP2,SDDVAN,SDKITID,SDANBY,SDNUMB,SDFRTH,SDAITM,SDFRMP,SDSQOR,SDPMTO,SDPRP4,SDPA8,SDSRP4,SDIVD,SDOKCO,SDFRGD,SDFUC,SDFUP,SDSO15 FROM PRODDTA.F42119 WHERE ((((((SDLTTR >= @P5  AND SDLTTR <= @P6 ) AND SDLITM LIKE @P7 ) AND SDNXTR < @P8 ) AND SDSOBK > @P9 ))) ORDER BY 54 ASC  , 55 ASC  , 16 ASC  , 100 ASC  , 40 ASC

Into something you can actually read and work with:

SELECT   SDSOQS,
         SDLTTR,
         SDPRP2,
         SDADTM,
...
         SDFRGD,
         SDFUC,
         SDFUP,
         SDSO15
FROM     PRODDTA.F4211
WHERE    ((((((SDLTTR >= @P0
               AND SDLTTR <= @P1)
              AND SDLITM LIKE @P2)
             AND SDNXTR < @P3)
            AND SDSOBK > @P4)))
UNION
SELECT   SDSOQS,
         SDLTTR,
         SDPRP2,
         SDADTM,
         SDQTYT,
         SDRSDT,
...
         SDDCT,
         SDFUP,
         SDSO15
FROM     PRODDTA.F42119
WHERE    ((((((SDLTTR >= @P5
               AND SDLTTR <= @P6)
              AND SDLITM LIKE @P7)
             AND SDNXTR < @P8)
            AND SDSOBK > @P9)))
ORDER BY 54 ASC, 55 ASC, 16 ASC, 100 ASC, 40 ASC;

Disk Performance: What The Numbers Mean

An enterprise grade SAS drive is spec’d at 3ms to 8ms response times.

Guys at Microsoft will tell you they want 10ms or less for everything. My goal is 10ms for the transaction log (LDF), and 20ms for the data files (MDF/NDF).

What happens when your are beyond 10ms on the t-log?

  • The log buffer fills up,
  • SQL Server sees the disk is being thrashed and takes it’s foot off the pedal because it can’t buffer anymore IO requests to the log,
  • At this point, locks start getting held much longer,
  • Severe blocking and deadlocks start to occur,
  • Applications start to time out and go into retries, etc…
  • The system stops being usable.

Errors like this start showing up in the ERRORLOG:

SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [Drive:\MSSQL\MSSQL.1\MSSQL\Data\xyz.mdf] in database [database].  The OS file handle is 0x00000000.  The offset of the latest long I/O is: 0x00000000000000

For more information on this:

http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/09/troubleshooting-sql-server-i-o-requests-taking-longer-than-15-seconds-i-o-stalls-amp-disk-latency.aspx

The curious thing, CPU goes way down because no work is being done. This makes most sysadmins scratch their heads because they don’t realize where the bottleneck is.

Two ways of getting numbers:

PerfMon: Real Time

  • Avg. Disk Sec/Read
  • Avg. Disk Sec/Write
  • Avg. Disk Sec/Transfer

Reading the PerfMon Output: .010 = 10ms, .050 = 50ms, 1.000 = 1 second, etc…

Here’s and example of 6,132ms (6 seconds) response times on a write operation (really bad).

6second_disk_latency

DMV to see Virtual File Stats: Averages since system was started

SELECT
–virtual file latency
vReadLatency
= CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms/num_of_reads) END
, vWriteLatency
= CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms/num_of_writes) END
, vLatency
= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END
–avg bytes per IOP
, BytesperRead
= CASE WHEN num_of_reads = 0
THEN 0 ELSE (num_of_bytes_read/num_of_reads) END
, BytesperWrite
= CASE WHEN num_of_writes = 0
THEN 0 ELSE (num_of_bytes_written/num_of_writes) END
, BytesperTransfer
= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE ((num_of_bytes_read+num_of_bytes_written)/(num_of_reads + num_of_writes)) END

, LEFT(mf.physical_name,2) as Drive
, DB_NAME(vfs.database_id) as DB
–, mf.name AS FileName
, vfs.*
, mf.physical_name
FROM sys.dm_io_virtual_file_stats(NULL,NULL) as vfs
JOIN sys.master_files as mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
–WHERE mf.type_desc = ‘LOG’ — log files
–WHERE DB_NAME(vfs.database_id) IN (‘tpcc’,’tpcc2′)
ORDER BY vLatency DESC
— ORDER BY vReadLatency DESC
— ORDER BY vWriteLatency DESC

The following is a sample output showing that the Read Latency is 6ms and the Write Latency is 255ms with the Average Latency (R+W) is 145ms.

vReadLatency         vWriteLatency        vLatency             Drive DB         physical_name
-------------------- -------------------- -------------------- ----- ---------- -----------------------------------------------------
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb12.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb11.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb10.ndf
6                    254                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb5.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb4.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb3.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb2.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb13.ndf
6                    255                  145                  F:    tempdb     F:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb14.ndf

(10 row(s) affected)

The following chart from Microsoft (http://technet.microsoft.com/en-us/library/cc966412.aspx) shows what they expect:

msft_disk_numbers

DMV Performance Views: Extracting Value

From Jimmy May @ Microsoft.

http://blogs.msdn.com/b/jimmymay/archive/2008/10/30/drum-roll-please-the-debut-of-the-sql-dmv-all-stars-dream-team.aspx

  • Expensive Queries (CPU, reads, frequency, etc.)
  • Wait Stats
  • Virtual File Stats (including calculations for virtual file latency)
  • Plan Cache
  • Blocking (real-time)

Script Download Here:

DMV_All-Stars_v20150114

–DMV_All-Stars.sql
–Jimmy May 
–A.C.E. Performance Team
–jimmymay@microsoft.com
–aspiringgeek@live.com
–http://blogs.msdn.com/jimmymay
–Table of Contents
–1. expensive queries
–2. wait stats
–3. virtual file stats (& virtual file latency)
–4. plan cache interrogation
–5. real-time blockers
–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–Weasel Clause: This script is provided “AS IS” with no warranties, and confers no rights.
—  Use of included script samples are subject to the terms specified at
—  http://www.microsoft.com/info/cpyright.htm
–<<<<<<<<<<—————————————————————–>>>>>>>>>>–

–1. expensive queries
–text *and* statement
–usage: modify WHERE & ORDER BY clauses to suit circumstances
SELECT –TOP 25
— the following four columns are NULL for ad hoc and prepared batches
DB_Name(qp.dbid) as dbname , qp.dbid , qp.objectid , qp.number
–, qp.query_plan –the query plan can be *very* useful; enable if desired
, qt.text
, SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END
– qs.statement_start_offset)/2) + 1) as statement_text
, qs.creation_time , qs.last_execution_time , qs.execution_count
, qs.total_worker_time    / qs.execution_count as avg_worker_time
, qs.total_physical_reads / qs.execution_count as avg_physical_reads
, qs.total_logical_reads  / qs.execution_count as avg_logical_reads
, qs.total_logical_writes / qs.execution_count as avg_logical_writes
, qs.total_elapsed_time   / qs.execution_count as avg_elapsed_time
, qs.total_clr_time       / qs.execution_count as avg_clr_time
, qs.total_worker_time , qs.last_worker_time , qs.min_worker_time , qs.max_worker_time
, qs.total_physical_reads , qs.last_physical_reads , qs.min_physical_reads , qs.max_physical_reads
, qs.total_logical_reads , qs.last_logical_reads , qs.min_logical_reads , qs.max_logical_reads
, qs.total_logical_writes , qs.last_logical_writes , qs.min_logical_writes , qs.max_logical_writes
, qs.total_elapsed_time , qs.last_elapsed_time , qs.min_elapsed_time , qs.max_elapsed_time
, qs.total_clr_time , qs.last_clr_time , qs.min_clr_time , qs.max_clr_time
–, qs.sql_handle , qs.statement_start_offset , qs.statement_end_offset
, qs.plan_generation_num
, qp.encrypted
, qp.query_plan
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
–WHERE last_execution_time >= ‘20130264’
–ORDER BY qs.execution_count      DESC  –Frequency
ORDER BY qs.total_worker_time    DESC  –CPU
–ORDER BY qs.total_elapsed_time   DESC  –Durn
–ORDER BY qs.total_logical_reads  DESC  –Reads
–ORDER BY qs.total_logical_writes DESC  –Writes
–ORDER BY qs.total_physical_reads DESC  –PhysicalReads
–ORDER BY avg_worker_time         DESC  –AvgCPU
–ORDER BY avg_elapsed_time        DESC  –AvgDurn
–ORDER BY avg_logical_reads       DESC  –AvgReads
–ORDER BY avg_logical_writes      DESC  –AvgWrites
–ORDER BY avg_physical_reads      DESC  –AvgPhysicalReads

–sample WHERE clauses
–WHERE last_execution_time > ‘20070507 15:00′
–WHERE execution_count = 1
—  WHERE SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
—    ((CASE statement_end_offset
—        WHEN -1 THEN DATALENGTH(qt.text)
—        ELSE qs.statement_end_offset END
—            – qs.statement_start_offset)/2) + 1)
—      LIKE ‘%MyText%’

–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–2. wait stats
–DBCC sqlperf(waitstats)
–DBCC sqlperf(‘sys.dm_os_wait_stats’,CLEAR)  –re-initialize waitstats
SELECT * , (wait_time_ms – signal_wait_time_ms) as resource_wait_time_ms
, signal_wait_time_per_wait
= CASE WHEN waiting_tasks_count = 0
THEN 0 ELSE (signal_wait_time_ms/waiting_tasks_count) END
, resource_wait_time_per_wait
= CASE WHEN waiting_tasks_count = 0
THEN 0 ELSE ((wait_time_ms – signal_wait_time_ms)/waiting_tasks_count) END
FROM sys.dm_os_wait_stats
ORDER BY resource_wait_time_ms DESC
–ORDER BY wait_time_ms DESC
–ORDER BY signal_wait_time_ms DESC
–ORDER BY waiting_tasks_count DESC
–ORDER BY max_wait_time_ms DESC

–adapted from Paul Randal
–Wait statistics, or please tell me where it hurts
–http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts
;WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms – signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
( N’BROKER_EVENTHANDLER’,             N’BROKER_RECEIVE_WAITFOR’,    N’BROKER_TASK_STOP’,                N’BROKER_TO_FLUSH’,
N’BROKER_TRANSMITTER’,              N’CHECKPOINT_QUEUE’,          N’CHKPT’,                           N’CLR_AUTO_EVENT’,
N’CLR_MANUAL_EVENT’,                N’CLR_SEMAPHORE’,             N’DBMIRROR_DBM_EVENT’,              N’DBMIRROR_EVENTS_QUEUE’,
N’DBMIRROR_WORKER_QUEUE’,           N’DBMIRRORING_CMD’,           N’DIRTY_PAGE_POLL’,                 N’DISPATCHER_QUEUE_SEMAPHORE’,
N’EXECSYNC’,                        N’FSAGENT’,                   N’FT_IFTS_SCHEDULER_IDLE_WAIT’,     N’FT_IFTSHC_MUTEX’,
N’HADR_CLUSAPI_CALL’,               N’HADR_FILESTREAM_IOMGR_IOCOMPLETION’,
N’HADR_LOGCAPTURE_WAIT’,            N’HADR_NOTIFICATION_DEQUEUE’, N’HADR_TIMER_TASK’,                 N’HADR_WORK_QUEUE’,
N’KSOURCE_WAKEUP’,                  N’LAZYWRITER_SLEEP’,          N’LOGMGR_QUEUE’,                    N’ONDEMAND_TASK_QUEUE’,
N’PWAIT_ALL_COMPONENTS_INITIALIZED’,                              N’QDS_PERSIST_TASK_MAIN_LOOP_SLEEP’,
N’QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP’,                N’REQUEST_FOR_DEADLOCK_SEARCH’,     N’RESOURCE_QUEUE’,
N’SERVER_IDLE_CHECK’,               N’SLEEP_BPOOL_FLUSH’,         N’SLEEP_DBSTARTUP’,                 N’SLEEP_DCOMSTARTUP’,
N’SLEEP_MASTERDBREADY’,             N’SLEEP_MASTERMDREADY’,       N’SLEEP_MASTERUPGRADED’,            N’SLEEP_MSDBSTARTUP’,
N’SLEEP_SYSTEMTASK’,                N’SLEEP_TASK’,                N’SLEEP_TEMPDBSTARTUP’,             N’SNI_HTTP_ACCEPT’,
N’SP_SERVER_DIAGNOSTICS_SLEEP’,     N’SQLTRACE_BUFFER_FLUSH’,     N’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,
N’SQLTRACE_WAIT_ENTRIES’,           N’WAIT_FOR_RESULTS’,          N’WAITFOR’,                         N’WAITFOR_TASKSHUTDOWN’,
N’WAIT_XTP_HOST_WAIT’,              N’WAIT_XTP_OFFLINE_CKPT_NEW_LOG’,
N’WAIT_XTP_CKPT_CLOSE’,             N’XE_DISPATCHER_JOIN’,        N’XE_DISPATCHER_WAIT’,              N’XE_TIMER_EVENT’
)
)
SELECT TOP 10
W1.wait_type AS WaitType,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_Sec,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_Sec,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_Sec,
W1.WaitCount AS WaitCount,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_Sec,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_Sec,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_Sec
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) – W1.Percentage < 99.9; — percentage threshold

GO

–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–3. virtual file stats
SELECT
–virtual file latency
vReadLatency
= CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms/num_of_reads) END
, vWriteLatency
= CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms/num_of_writes) END
, vLatency
= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END
–avg bytes per IOP
, BytesperRead
= CASE WHEN num_of_reads = 0
THEN 0 ELSE (num_of_bytes_read/num_of_reads) END
, BytesperWrite
= CASE WHEN num_of_writes = 0
THEN 0 ELSE (num_of_bytes_written/num_of_writes) END
, BytesperTransfer
= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE ((num_of_bytes_read+num_of_bytes_written)/(num_of_reads + num_of_writes)) END

, LEFT(mf.physical_name,2) as Drive
, DB_NAME(vfs.database_id) as DB
–, mf.name AS FileName
, vfs.*
, mf.physical_name
FROM sys.dm_io_virtual_file_stats(NULL,NULL) as vfs
JOIN sys.master_files as mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
–WHERE mf.type_desc = ‘LOG’ — log files
–WHERE DB_NAME(vfs.database_id) IN (‘tpcc’,’tpcc2′)
ORDER BY vLatency DESC
— ORDER BY vReadLatency DESC
— ORDER BY vWriteLatency DESC
–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–4. plan cache interrogation
— note: sys.dm_exec_cached_plans is diminutive version of syscacheobjects
— no dbid, setopts
— we want reusable code, absence of ad hoc SQL
— we want relatively few rows with low usecounts
–2000
SELECT cacheobjtype , objtype , usecounts , pagesused , dbid , sql
FROM master.dbo.syscacheobjects
WHERE cacheobjtype = ‘Compiled Plan’
ORDER BY usecounts DESC
–ORDER BY sql
–2005
SELECT c.cacheobjtype , c.objtype , c.usecounts , c.size_in_bytes , t.dbid , t.text
FROM sys.dm_exec_cached_plans as c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as t
WHERE c.cacheobjtype = ‘Compiled Plan’
ORDER BY c.usecounts DESC
–ORDER BY t.text

–<<<<<<<<<<—————————————————————–>>>>>>>>>>–
–5. real-time blockers
–Report Blocker and Waiter SQL Statements
–http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/trans/sql05vb044.mspx?mfr=true
— SQLCAT BPT
SELECT
t1.resource_type as lock_type
, db_name(resource_database_id) as DB
, t1.resource_associated_entity_id as blkd_obj
, t1.request_mode as lock_req          — lock requested
, t1.request_session_id as waiter_sid– spid of waiter
, t2.wait_duration_ms as waittime
, (SELECT text FROM sys.dm_exec_requests as r  — get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE r.session_id = t1.request_session_id) as waiter_batch
, (SELECT SUBSTRING(qt.text , r.statement_start_offset/2
, (CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE r.statement_end_offset END – r.statement_start_offset)/2)
FROM sys.dm_exec_requests as r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt
WHERE r.session_id = t1.request_session_id) as waiter_stmt    — this is the statement executing right now
, t2.blocking_session_id as blocker_sid — spid of blocker
, (SELECT text FROM sys.sysprocesses as p       — get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE p.spid = t2.blocking_session_id) as blocker_stmt
FROM sys.dm_tran_locks as t1
JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address

Index Usage: What’s Being Used, What’s NOT Being Used

Always trying to figure out “if” and “how much” my indexes are being used.

This posting has the information:

http://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

Conversely, which indexes are being UPDATED but NEVER used, not mirrored, and is not disabled already (since the system has been up):

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
         inner join sys.objects as o 
            on i.object_id = o.object_id     
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 and
user_updates > 100 and
is_disabled <> 1 and
(USER_SEEKS + USER_SCANS + USER_LOOKUPS) = 0
 AND o.is_ms_shipped = 0

Script Out My Custom Applied Indexes

When I apply my custom indexes or missing indexes, I put _fmcb_ in the name of the index. This way, I can run the below query and extract all of them out for a separate build script.

http://social.technet.microsoft.com/wiki/contents/articles/19598.how-to-generate-index-creation-scripts-for-all-tables-in-a-database-using-t-sql.aspx

SELECT  ' CREATE ' +
 CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +
 I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
 I.name  + ' ON '  +
 Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +
 KeyColumns + ' )  ' +
 ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +
 ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( MAXDOP=0, ' +
 CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +
 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +
 -- default value
 'SORT_IN_TEMPDB = OFF '  + ','  +
 CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +
 CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +
 -- default value
 ' DROP_EXISTING = ON '  + ','  +
 -- default value
 ' ONLINE = OFF '  + ','  +
 CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +
 CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +
 DS.name + ' ] '  [CreateIndexScript]
 FROM sys.indexes I
 JOIN sys.tables T ON T.Object_id = I.Object_id
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid
 JOIN (SELECT * FROM (
 SELECT IC2.object_id , IC2.index_id ,
 STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
 FROM sys.index_columns IC1
 JOIN Sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 0
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY IC1.object_id,C.name,index_id
 ORDER BY MAX(IC1.key_ordinal)
 FOR XML PATH('')), 1, 2, '') KeyColumns
 FROM sys.index_columns IC2
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4
 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id
 LEFT JOIN (SELECT * FROM (
 SELECT IC2.object_id , IC2.index_id ,
 STUFF((SELECT ' , ' + C.name
 FROM sys.index_columns IC1
 JOIN Sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 1
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY IC1.object_id,C.name,index_id
 FOR XML PATH('')), 1, 2, '') IncludedColumns
 FROM sys.index_columns IC2
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY IC2.object_id ,IC2.index_id) tmp1
 WHERE IncludedColumns IS NOT NULL ) tmp2
 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
 WHERE i.name like '%fmcb%'
 --I.is_primary_key = 0 AND I.is_unique_constraint = 0
 --AND I.Object_id = object_id('Person.Address') --Comment for all tables
 -- AND I.name = 'IX_Address_PostalCode' --comment for all indexes