Month: February 2015

String Flattening and Normalization

Leave a comment

I often end up with a variety of data where strings are hard coded with values, but the queries are really the same under the sheets. This means I have a hard time really determining how many unique queries I really have.

This function will flatten out the hard coded values into ‘xxx’… thus allowing me to compare like strings.

create table tempdb..denorm(strMixed varchar(8000))

insert into tempdb..denorm values ('select * from tblContacts where Id = ''123'' and type = 12')
insert into tempdb..denorm values ('select * from tblContacts where Id = ''234'' and type = 12')
insert into tempdb..denorm values ('select * from tblContacts where Id = ''456'' and type = 12')

select strMixed from tempdb..denorm

select dbo.norm_string(strMixed) from tempdb..denorm

strMixed
--------------------------------------------------------------
select * from tblContacts where Id = '123' and type = 12
select * from tblContacts where Id = '234' and type = 12
select * from tblContacts where Id = '456' and type = 12

(3 row(s) affected)


--------------------------------------------------------------
select * from tblContacts where Id = 'xxx' and type = 12
select * from tblContacts where Id = 'xxx' and type = 12
select * from tblContacts where Id = 'xxx' and type = 12

(3 row(s) affected)

Here’s the code:

create function norm_string(@str varchar(8000))
returns varchar(8000)
as begin

declare @tblOffset table
(
position int identity,
offset int
)



declare @i bigint
declare @c char(1)
declare @str2 varchar(8000)
declare @str3 varchar(8000)

set @i = 1


while @i < (len(@str)+1)
begin

 set @c = substring(@str,@i,1)
 if @c = char(39)
 begin
 insert into @tblOffset(offset) values (@i)
 end

 set @i=@i+1

end

-- select * from @tblOffset

declare @positionprime int
declare @offsetprime int

declare @position int
declare @offset int
declare @offset1 int

declare @start int
declare @end int

declare @position2 int
declare @offset2 int
declare @stuff varchar(10)
declare @primingoffset int


set @stuff = 'xxx'


declare offsets_cursor cursor for 
select position, offset from @tblOffset

OPEN offsets_cursor

FETCH NEXT FROM offsets_cursor INTO @position, @primingoffset

-- first, get the head of the string. read from beginning to the first delimeter.

set @str2=substring(@str,1,@primingoffset)+@stuff


FETCH NEXT FROM offsets_cursor INTO @position, @offset1
FETCH NEXT FROM offsets_cursor INTO @position2, @offset2



WHILE @@FETCH_STATUS = 0
BEGIN


 set @str2 = @str2+substring(@str,@offset1,@offset2-@offset1)+char(39)+@stuff

 FETCH NEXT FROM offsets_cursor INTO @position, @offset1
 FETCH NEXT FROM offsets_cursor INTO @position2, @offset2


end

close offsets_cursor
deallocate offsets_cursor

-- now put the tail on


set @str2 = @str2+substring(@str,@offset1,len(@str)-@offset1+1)

return @str2
end

Compression of a PeopleSoft/SQL Server Database

Leave a comment

Summary: A PeopleSoft 9.1 database on SQL Server compressed a maximum of 70%.

I’ve been doing some rudimentary testing today on a customer’s SQL Server 2008 R2 PeopleSoft database just to see what the compression ratios are. Different data types compress at different rates, but this post will show you what a PeopleSoft 9.1 database compresses at.

One large table:

ALTER INDEX ALL ON PS_CM_MATCHED_COST REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, data_compression = page, STATISTICS_NORECOMPUTE = ON, maxdop = 0);

sp_spaceused PS_CM_MATCHED_COST

-- NO COMPRESSION

name                  rows       data      index_size 
------------------- --------- ----------- ------------ 
PS_CM_MATCHED_COST  52661662  10406784 KB   135888 KB 

-- PAGE 
-- 1 min 29 sec to compress to PAGE
 
name                  rows       data       index_size 
------------------- --------- ------------ ------------ 
PS_CM_MATCHED_COST  52661662    2168888 KB    11496 KB 

-- ROW
name                  rows        data      index_size 
-------------------- --------- ----------  ------------
PS_CM_MATCHED_COST   52661662   5455040 KB    28176 KB

Using PAGE compression, it took 2 hours, 6 minutes and 22 seconds to compress the

Here’s the uncompressed baseline:

FILE_SIZE_MB  SPACE_USED_MB NAME       FILENAME
------------- ------------- ---------- ------------------- 
242595.63     236784.56     FSSYS_Data F:\data\FS91SSD.mdf
   700.50         10.58     FSSYS_Log  L:\FS91SSD_log.LDF

Using PAGE compression, it took 2 hours, 6 minutes and 22 seconds to compress the 236GB of data and indexes. The size went from 236GB used to 70GB for a reduction of 70%.

FILE_SIZE_MB SPACE_USED_MB  NAME       FILENAME
------------ -------------- ---------- ---------------------
242595.63    70592.00       FSSYS_Data F:\data\FS91SSD.mdf
   700.50       78.89       FSSYS_Log  L:\FS91SSD_log.LDF

Compression with ROW method the database goes from 236GB to 142GB:

FILE_SIZE_MB SPACE_USED_MB  NAME       FILENAME
------------- ------------- ---------- ------------------
142500.00     142190.44     FSSYS_Data F:\data\FS91SSD.mdf
  1300.50         21.27     FSSYS_Log  L:\FS91SSD_log.LDF

Here’s the script to compress the entire database:

DECLARE @SQL NVARCHAR(2000) 
declare @psfttblname sysname

declare tblname cursor for select name from sysobjects where name like 'PS_%' and type = 'U'
OPEN tblname

FETCH NEXT FROM tblname 
INTO @psfttblname

WHILE @@FETCH_STATUS = 0
BEGIN
 
-- SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' REBUILD WITH (DATA_COMPRESSION=PAGE, maxdop = 0)' 
 set @SQL = 'ALTER INDEX ALL ON '+ @psfttblname+' REBUILD WITH (SORT_IN_TEMPDB = ON, data_compression = page, STATISTICS_NORECOMPUTE = ON, maxdop = 0);'
 print @SQL
 print 'start: '+cast(getdate() as nvarchar(20))
 EXEC (@SQL) 
 print 'end: '+cast(getdate() as nvarchar(20))
 print ''
FETCH NEXT FROM tblname 
INTO @psfttblname
end
close tblname
deallocate tblname

Space Free and Check Status of Compression Scripts:

select
 a.FILEID,
 [FILE_SIZE_MB] =
 convert(decimal(12,2),round(a.size/128.000,2)),
 [SPACE_USED_MB] =
 convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
 [FREE_SPACE_MB] =
 convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
 NAME = left(a.NAME,15),
 FILENAME = left(a.FILENAME,30)
 from
 dbo.sysfiles a

-- http://stackoverflow.com/questions/16988326/query-all-table-data-and-index-compression

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
 [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

SELECT [t].[name] AS [Table], [i].[name] AS [Index], 
 [p].[partition_number] AS [Partition],
 [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
WHERE [p].[index_id] > 1 --and object_id('PS_CM_MATCHED_COST') = t.object_id

To shrink the database file from 242GB to 80GB took 37 minutes:

-- shrink database

dbcc shrinkfile('FSSYS_Data', 80000)

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
---- ------ ----------- ----------- --------- --------------
6      1    10240000    256000      9014872   9014872

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- 37 min

When RCSI Hurts You

Leave a comment

Read Commit Snapshot Isolation (RCSI) is a feature in SQL Server that can help alleviate blocking in an application.

From an application point of view, there are pro’s and con’s. They are well enumerated in this post here:

http://sqlperformance.com/2014/05/t-sql-queries/read-committed-snapshot-isolation

With the applications I work with a lot (PeopleSoft, Siebel, & JD Edwards), it’s encourages to use this feature:

http://docs.oracle.com/cd/E38689_01/pt853pbr0/eng/pt/tadm/concept_MicrosoftSQLServerFeatureConsiderations-0779f1.html

So with so much going for it, what’s the downside?

SQL Server uses tempdb for row versioning. It keeps the BEFORE copy/version of the data in tempdb. As such, tempdb gets hammered a lot more than a database without RCSI. This is discussed here:

https://www.simple-talk.com/sql/t-sql-programming/row-versioning-concurrency-in-sql-server/

Since tempdb is getting a lot more IO, you need more IOPS from your disk subsystem to support this new workload. There are only two ways to get this… use SSD or buy a lot more physical drives to handle the workload. I think about it like a car engine. Each cylinder does “x” many units of work. The more cylinders in and engine, the more work the engine can handle. Keep in mind your RAID levels, too. RAID 5 puts a 42% penalty on all writes. So NEVER use RAID 5 for tempdb as it’s 80% writing all the time.

Here’s what happened to a customer who is running a 300GB PeopleSoft database and turned on RCSI without re-sizing tempdb… write latency at 15 seconds… yes… seconds!

vReadLatency  vWriteLatency      DB     FileName
------------  -----------------  ------ ----------------
14            15032              tempdb T:\tempdb3.ndf
14            15032              tempdb T:\tempdb4.ndf
13            15030              tempdb T:\tempdb5.ndf
20            15025              tempdb T:\tempdb.mdf
14            15028              tempdb T:\tempdb7.ndf
17            15027              tempdb T:\tempdb8.ndf
13            15024              tempdb T:\tempdb6.ndf
19            12997              tempdb T:\tempdb2.ndf

There are many blog postings on sizing for RCSI. Just Google: “sizing tempdb for rcsi”

The easiest, cleanest, and often the cheapest solution is to just use local attached SSD for tempdb. Since tempdb is rebuilt every time SQL Server is cycled, you don’t need to RAID it, etc…

First choice for SSD would be something connected to the PCI bus. These are always faster. Second choice is something connected to the SATA/SAS bus… you will bottleneck on the bus… but it’s faster than than physical disk and a lot cheaper than SAN disk drives.

Script to see latency (Courtesy Jimmy May):

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
, substring(DB_NAME(vfs.database_id), 1, 10) as DB
--, mf.name AS FileName
--, vfs.*
, substring(mf.physical_name, 1, 15)
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

FrankMcB’s Blocking Script

Leave a comment

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