Replacement for SP_HELPINDEX. This proc enumerates the INCLUDE columns.

Reposting from:

http://www.sqlservercentral.com/scripts/Indexing/71223/

 

use master
go
create procedure dbo.sp_helpindex2

    @objname nvarchar(776)        -- the table to check for indexes
 

as
 

/*
 

Original Script - Microsoft Corporation
Modified by - Girish Sumaria - girish.sumaria@gmail.com
Information - The sp_helpindex only provides the list of columns in the index and not the INCLUDED COLUMNS.
       In order to retrieve complete index information, I have modified the original code so that
        you can now retrieve INCLUDED COLUMNS list as well. Also, the index type can be retrived separately.
Tip from my fellow Database Developer - Prasant Nanda - prasant.nanda@gmail.com
        Ensure that you mark this procedure as a SYSTEM PROCEDURE to leverage its use from all databases.
*/
    -- PRELIM
    set nocount on
    declare @objid int,            -- the object id of the table
            @indid smallint,    -- the index id of an index
            @groupid int,         -- the filegroup id of an index
            @indname sysname,
            @groupname sysname,
            @status int,
            @keys nvarchar(2126),    --Length (16*max_identifierLength)+(15*2)+(16*3)
            @included_keys nvarchar(2126),    --Length (16*max_identifierLength)+(15*2)+(16*3)
            @InclCol nvarchar(225),
            @dbname    sysname,
            @ignore_dup_key    bit,
            @is_unique        bit,
            @is_hypothetical    bit,
            @is_primary_key    bit,
            @is_unique_key     bit,
            @auto_created    bit,
            @no_recompute    bit
    -- Check to see that the object names are local to the current database.
    select @dbname = parsename(@objname,3)
--    print @dbname
    if @dbname is null
        select @dbname = db_name()
    else if @dbname <> db_name()
        begin
            raiserror(15250,-1,-1)
            return (1)
        end
    -- Check to see the the table exists and initialize @objid.
    select @objid = object_id(@objname)
 

--    print @objid
 

    if @objid is NULL
 

    begin
 

        raiserror(15009,-1,-1,@objname,@dbname)
 

        return (1)
 

    end
 

    -- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
 

    declare ms_crs_ind cursor local static for
 

        select i.index_id, i.data_space_id, i.name,
 

            i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint,
 

            s.auto_created, s.no_recompute
 

        from sys.indexes i join sys.stats s
 

            on i.object_id = s.object_id and i.index_id = s.stats_id
 

        where i.object_id = @objid
 

    open ms_crs_ind
 

    fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
 

            @is_primary_key, @is_unique_key, @auto_created, @no_recompute
 

    -- IF NO INDEX, QUIT
 

    if @@fetch_status < 0
 

    begin
 

        deallocate ms_crs_ind
 

        raiserror(15472,-1,-1,@objname) -- Object does not have any indexes.
 

        return (0)
 

    end
 

    -- create temp table
 

    CREATE TABLE #spindtab
 

    (
 

        index_name            sysname    collate database_default NOT NULL,
 

        index_id                int,
 

        ignore_dup_key        bit,
 

        is_unique                bit,
 

        is_hypothetical        bit,
 

        is_primary_key        bit,
 

        is_unique_key            bit,
 

        auto_created            bit,
 

        no_recompute            bit,
 

        groupname            sysname collate database_default NULL,
 

        index_keys            nvarchar(2126)    collate database_default NOT NULL, -- see @keys above for length descr
 

        included_keys            nvarchar(2126)    collate database_default NULL
 

    )
 

    -- Now check out each index, figure out its type and keys and
 

    --    save the info in a temporary table that we'll print out at the end.
 

    while @@fetch_status >= 0
 

    begin
 

        -- First we'll figure out what the keys are.
 

        declare @i int, @thiskey nvarchar(131) -- 128+3
 

        select @keys = index_col(@objname, @indid, 1), @i = 2
 

        if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
 

            select @keys = @keys + '(-)'
 

        select @thiskey = index_col(@objname, @indid, @i)
 

        if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
 

            select @thiskey = @thiskey + '(-)'
 

        while (@thiskey is not null )
 

        begin
 

            select @keys = @keys + ', ' + @thiskey, @i = @i + 1
 

            select @thiskey = index_col(@objname, @indid, @i)
 

            if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
 

                select @thiskey = @thiskey + '(-)'
 

        end
 

/* Code to find Included Columns goes here */
 

    set @InclCol=null
 

    set @included_keys=null
 

    declare ms_crs_inc_cols cursor local static for
 

        SELECT --sys.tables.object_id,
 

            --sys.tables.name as table_name, sys.indexes.name as index_name,sys.indexes.type_desc as Ind_Type,
 

        sys.columns.name as column_name
 

        --,sys.index_columns.index_column_id, sys.indexes.is_unique, sys.indexes.is_primary_key , sys.index_columns.is_included_column
 

        FROM sys.tables, sys.indexes, sys.index_columns, sys.columns
 

        WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id AND sys.tables.object_id = sys.columns.object_id
 

        AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id)
 

        AND sys.indexes.object_id = @objid
 

            and sys.indexes.index_id = @indid
 

            and is_included_column=1
 

        order by index_column_id
 

    open ms_crs_inc_cols
 

    fetch next from ms_crs_inc_cols into @InclCol
 

    while @@fetch_status >= 0
 

    begin
 

        if @included_keys is null
 

            set @included_keys=@InclCol
 

        else
 

            set @included_keys=@included_keys+','+@InclCol
 

        print @included_keys
 

        fetch next from ms_crs_inc_cols into @InclCol
 

    end
 

    close ms_crs_inc_cols
 

    deallocate ms_crs_inc_cols
 

/* Code to find Included Columns ends here */
 

        select @groupname = null
 

        select @groupname = name from sys.data_spaces where data_space_id = @groupid
 

        -- INSERT ROW FOR INDEX
 

        insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical,
 

            @is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys,@included_keys)
 

        -- Next index
 

        fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
 

            @is_primary_key, @is_unique_key, @auto_created, @no_recompute
 

    end
 

    deallocate ms_crs_ind
 

    -- DISPLAY THE RESULTS
 

    select
 

        'index_name' = index_name,
 

        'type' = case when index_id = 1 then 'clustered' else 'nonclustered' end,
 

        'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
 

                case when ignore_dup_key <>0 then 'ignore duplicate keys' else '' end
 

                + case when is_unique <>0 then ', unique' else '' end
 

                + case when is_hypothetical <>0 then ', hypothetical' else '' end
 

                + case when is_primary_key <>0 then ', primary key' else '' end
 

                + case when is_unique_key <>0 then ', unique key' else '' end
 

                + case when auto_created <>0 then ', auto create' else '' end
 

                + case when no_recompute <>0 then ', stats no recompute' else '' end
 

                + ' located on ' + groupname),
 

        'index_keys' = index_keys,
 

        'included_keys' = included_keys
 

    from #spindtab
 

    order by index_name
 

    return (0) -- sp_helpindex
 

go
 

USE Master
 

EXEC sp_MS_marksystemobject sp_helpindex2
 

go

Over the last few weekends we’ve been compressing a JD Edwards database, and I thought I’d post the results. The following is a report based on the project.

Compression Results

Summary:

Over the weekend I completed compressing the Top 10 Tables per our original discussion.

Initially, the database was 273GB of space used inside it. After all the compression has been finished, we are now at 157GB.

This is a reduction of 42.51% in the JDE database size.

The database files are still the same size. I have not shrunk them yet… and wouldn’t advise it unless you needed the space back for some reason or another.

You should now be able to go years without having to consider adding any more disk space or worry about IO problems.

List of Tables/Indexes Compressed

	Schema	Table	Rows	Type
1	PRODDTA	F0101Z2	2728142	PAGE
2	PRODDTA	F0115	3583766	ROW
3	PRODDTA	F03B11	3813755	PAGE
4	PRODDTA	F03B14	5277380	PAGE
5	PRODDTA	F03B21	6766369	PAGE
6	PRODDTA	F0911	19424122	PAGE
7	PRODDTA	F4074	6801498	PAGE
8	PRODDTA	F4111	8210392	PAGE
9	PRODDTA	F42119	8104459	PAGE
10	PRODDTA	F49211	2799436	PAGE
11	PRODDTA	F49299	6878974	PAGE

Data Points:

Baseline:

BACKUP DATABASE successfully processed 33442892 pages in 473.482 seconds (551.811 MB/sec).

After First Wave of Compression:

BACKUP DATABASE successfully processed 26944689 pages in 340.404 seconds (618.398 MB/sec).

After Final Compression:

BACKUP DATABASE successfully processed 19244052 pages in 354.502 seconds (424.099 MB/sec).

Final File Sizes and Utilization :

As you can see, you now have 434GB of empty space in your database now…

FILEID	FILE_SIZE_MB	SPACE_USED_MB	FREE_SPACE_MB	NAME
1	75000.00	23223.69	51776.31	JDE_PROD_Data
2	109000.00	92.33	108907.67	JDE_PROD_Log
3	100000.00	38333.88	61666.13	JDE_PROD_Data2
4	150000.00	44162.38	105837.63	JDE_PROD_Data3
5	150000.00	44173.75	105826.25	JDE_PROD_Data4

Here is a list of the scripts that I ran for each table over the weekend and the amount of time it took to compress each one. To remove the compression on a table, run the same script, but just change the followiong parameter:   DATA_COMPRESSION = PAGE  to DATA_COMPRESSION = NONE

— 13 min 50 sec

ALTER INDEX ALL ON [PRODDTA].F03B14 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 1 min 47 sec

ALTER INDEX ALL ON [PRODDTA].F03B21 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 1 hour 6 min 18 sec

ALTER INDEX ALL ON [PRODDTA].F42119 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 17 min 17 sec

ALTER INDEX ALL ON [PRODDTA].F4111 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 3 hours 27 min 57 sec

ALTER INDEX ALL ON [PRODDTA].F0911 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

—  F49299 2 min 36 sec

ALTER INDEX ALL ON [PRODDTA].F49299 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— F49211 2 min 7 sec

ALTER INDEX ALL ON [PRODDTA].F49211 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— F0101Z2 4 min 56 sec

ALTER INDEX ALL ON [PRODDTA].F0101Z2 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

The other day my brother sends me this blog link and asks what I think about it:

https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database

I answer back to him with a recent story from another one of my customers.

The issue here is that people write posts on analyzing memory structures without ever addressing the potential side effects of them. In this case, scanning the database cache looking for errors, missing indexes, scans, etc…

Just like with anything, there is no free lunch. As much upside as there is to looking through the cache, here is the downside: depending on the size of the cache being analyzed– the DBA risks being the biggest problem on the entire system.

Here is an example of what a DBA did at one of my customers. They scraped a script off some website and just started running it in the middle of the business day looking for parallelism in plan caches:

-- Collect parallel plan information
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES 
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
INSERT INTO master.dbo.PlanCacheForMaxDop
SELECT
 query_plan AS CompleteQueryPlan,
 n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
 n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
 n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
 n.query('.') AS ParallelSubTreeXML,
 ecp.usecounts,
 ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

What he didn’t understand was that each time he ran it, about 600GB of reads was being pushed through the CPU (as seen via Profiler):

 
Month-Day   Hour        Bytes Per Query
----------- ----------- ----------------------------- 
1-6         16          589,001,506,816.00 
1-8         19          622,114,177,024.00 

While in itself, there’s nothing wrong with looking at the caches, just be aware of the classic Heisenberg Principle: You can’t monitor something without disturbing it.

In this case, his monitoring was actually worse than the queries he was looking for.

In the previous post, I covered how to normalize hard coded expressions inside a query. In this post, there is a specific problem related to PeopleSoft; it’s that often the PROCESS_INSTANCE is hard coded and needs to be normalized.

For example, here’s an INSERT statement from a Profiler trace of a PeopleSoft system:

INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)

The problem is, we can’t do any aggregations on this to find the number of unique queries in the file as all of them could be unique simply because the PROCESS_INSTANCE keeps changing… but in reality… it’s all the same query… there really is only ONE unique query.

What we want to do is make a function that strips out the hard coded values and replaces them with “xxx” or whatever you want to put in.

select dbo.norm_psft('INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)')

-- the dbo.norm_psft function will convert

INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)

-- into:

INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT xxx , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = xxx AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)

Here’s the code:

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

-- usage:
-- select dbo.norm_psft('INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)')
-- select dbo.norm_psft('frank mcbath')


-- testing
-- declare @str varchar(8000) 
-- set @str = 'INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)'


declare @i int
declare @c char(1)
declare @pi_offset_len int
set @i = charindex( 'PROCESS_INSTANCE = ', @str)

if (@i > 1)
begin

set @i = charindex( 'PROCESS_INSTANCE = ', @str)
set @i = @i + 20 -- this is the beginning char after the '= '

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

-- figure out how long the process instance integer is

 set @c = substring(@str,@i,1)

 if substring(@str,@i,1) = ' '
 begin
 set @pi_offset_len = @i
 break
 end

 set @i=@i+1

end

-- dig the PID out of the string... only one occurence is needed
declare @pid nvarchar(20)
set @pid = substring(
@str, 
(charindex( 'PROCESS_INSTANCE = ', @str)+19), 
@i-(charindex( 'PROCESS_INSTANCE = ', @str)+19))

-- now replace ALL occurences of the PID in the string and return the fixed string:

end

if (charindex( 'PROCESS_INSTANCE = ', @str) > 0)
 set @str = replace(@str, @pid, 'xxx')
 
return @str

end

I’m putting this script out here so I don’t need to hunt for it later on. It’s useful and common task people ask about often enough.

Original Blog:

http://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx

Note, I fixed some bugs in his original script.

 -- http://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx
/*
Depending on fragmentation index level appropriated action will be taken (no action, rebuild or reorganize)
If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index will be reorganized, finally if index average fragmentation is greater than 30% index will be rebuilt.
-- Ensure a USE  statement has been executed first.
*/

USE <change_me>
go

SET nocount ON;
go


DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);
DECLARE @dbid SMALLINT;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

if OBJECT_ID('tempdb..#work_to_do') is not null
begin

drop table #work_to_do

end

SET @dbid = Db_id();
SELECT [object_id] AS objectid,
 index_id AS indexid,
 partition_number AS partitionnum,
 avg_fragmentation_in_percent AS frag,
 page_count
INTO #work_to_do
FROM sys.Dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
AND index_id > 0 -- Ignore heaps
AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT objectid,
 indexid,
 partitionnum,
 frag
FROM #work_to_do;

OPEN partitions

 FETCH next
 FROM partitions
 INTO @objectid,
 @indexid,
 @partitionnum,
 @frag;

-- Open the cursor.OPEN partitions;
-- Loop through the partitions.
WHILE @@FETCH_STATUS = 0
BEGIN

 
 IF @@FETCH_STATUS < 0 BREAK;
 SELECT @objectname = Quotename(o.NAME),
 @schemaname = Quotename(s.NAME)
 FROM sys.objects AS o
 JOIN sys.schemas AS s
 ON s.schema_id = o.schema_id
 WHERE o.object_id = @objectid;
 
 SELECT @indexname = Quotename(NAME)
 FROM sys.indexes
 WHERE object_id = @objectid
 AND index_id = @indexid;
 
 SELECT @partitioncount = Count (*)
 FROM sys.partitions
 WHERE object_id = @objectid
 AND index_id = @indexid;
 
 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

 IF @frag < 30.0
 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
 IF @frag >= 30.0
 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
 IF @partitioncount > 1
 SET @command = @command + N' PARTITION=' + Cast(@partitionnum AS NVARCHAR(10));
 EXEC (@command);
 PRINT N'Executed: ' + @command;


 FETCH next
 FROM partitions
 INTO @objectid,
 @indexid,
 @partitionnum,
 @frag;

END
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
go 

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

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

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

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