Capturing Deadlock information or simulating deadlock

SQL Server 2005/2008

You can always run a profiler trace to capture the deadlock graph that gives details of the SPID and queries involved. But I would rather turn on the traces for deadlock and capture the info in the Error logs.

To do this run the following commands

DBCC TRACEON (1204, 01)

DBCC TRACEON (1222, -1)

The output from each of them are different so check the output before you decide which one you prefer. I like the output from the second one which was introduced in SQL Server 2005 and gives the info for each process involved.

If you want to test the output then take the following steps to simulate a deadlock and check the output.

1) CREATE TABLE a (i int); 

    CREATE TABLE b (i int);

   

    INSERT a SELECT 1;

    INSERT b SELECT 9;

2) In a new window
    BEGIN TRAN

    UPDATE a SET i = 11  

    WHERE i = 1 

    WAITFOR DELAY '00:00:10' 

    UPDATE b SET i = 99 

    WHERE i = 9 

    COMMIT

3) In another window
    BEGIN TRAN 

    UPDATE b SET i = 99 

    WHERE i = 9 

    WAITFOR DELAY '00:00:10' 

    UPDATE a SET i = 11 

    WHERE i = 1 

    COMMIT

If you want to retest the deadlock then drop the tables and rerun the steps.

SQL Server 2005/2008 : How to create Composite indexes?

Creating new indexes can be a significant change and it is important to note that adding unnecessary indexes is not a good strategy. While indexes lead to better READ performance they will reduce the WRITE performance on the tables.

If you have more than one SELECT query to tune and create indexes for then you have to analyze if there is one index that will satisfy all the queries. If one index won't suffice the queries then find the least number of indexes and their definitions that will improve the performance of the queries.

To illustrate the examples, I am going to use the following table 

CREATE TABLE [dbo].[table1](

            [col1] [int] NULL,

            [col2] [int] NULL,

            [col3] [int] NULL,

            [col4] [int] NULL

) ON [PRIMARY]

Consider the following SELECT statements and lets see what kind of indexes will help.

1) select col1, col2 from table1 where col1=10

2) select col1, col2, col3 from table1 where col1=10 and col3=3

3) select col3, col2 from table1 where col3=12

4) select col1, col2, col3, col4 from table1 where col1=10 and col3=12 and col4=15

Note: Any column that is part of the selected columns but is not part of the conditions in the WHERE clause, can be part of the included columns of the index. That column does not need to be part of the columns on which the index is created. In all the above queries col2 is part of the selected columns but not part of the WHERE clause.

For the first query all you need is an index on col1 that also includes col2. This will result in index seeks.

CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]

( [col1] ASC ) INCLUDE ( [col2])

For the second query you will need an index on col1 and col3. Also include col2 in that index.

CREATE NONCLUSTERED INDEX [index2] ON [dbo].[table1]

( [col1] ASC, [col3] ASC ) INCLUDE ( [col2])

Note that since the first column in index2 is col1, it will lead to index seeks for the first query. Hence, index2 will make index1 redundant.

But for the third query the index ‘index2’ will not help. This query will lead to index scans. The order of the column in a composite index is important. In index2, col3 is not the first column in the order hence it will lead to index scans for any query which has a WHERE clause on just the col3.

 So the following index would be appropriate. This index will be created on col3 and include col2.

CREATE NONCLUSTERED INDEX [index3] ON [dbo].[table1]

( [col3] ASC ) INCLUDE ( [col2])

 

Now consider the 4^th query, to force an index seek you will have to create the following index.

CREATE NONCLUSTERED INDEX [index4] ON [dbo].[table1]

( [col1] ASC, [col3] ASC, [col4] ASC ) INCLUDE ( [col2])

Notice that this index4 will lead to index seeks for query 1 and 2. So there is no need create index1 and index2

If you analyze the indexes closely you will see that the Index4 will be sufficient for the Query 1, 2 and 4.

To force index seeks for all the queries mentioned above Index4 and Index 3 will be sufficient.

Find missing indexes

SQL Server 2005/2008 : Find missing indexes

Missing index report
Starting from SQL Server 2005 there were some DMVs introduced that stored information regarding what indexes would enhance the performance of the queries. Following query will give you a list of indexes that SQL Server recommends. Use this report wisely and do not create all the indexes listed in the report.


SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
 [Table] = [statement],
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
  + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
  + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Get blocking query

If you want to capture the SPID and the query that is causing blocking then refer to my previous post
http://saveadba.blogspot.com/2012/02/find-query-causing-blocking-extended.html


But I have  come across situations where I am troubleshooting for extended blocking. But unfortunately the queries causing the extended blocking finish execution before I can login to the server. So I have written a script which will capture all the information that I need. I save the output to a file via a job and I email the output to myself. This way I have a record of the queries that cause extended blocking.

I have set up the following script in a job and have configured the job to save the output to a file. Following is a sample of the output which tells me which query is causing blocking and which one is being blocked.


Query #1
run by spid : 62
This is the SPID that is causing the blocking
 hostname                            loginame
--------
SQLServerprd01          Appuser


--------
 Running DBCC INPUTBUFFER for SPID 62
EventType Parameters EventInfo
--------- ---------- ---------
Language Event 0  insert into test...........

Use the following script for this.............................................................................................

use master

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
set nocount on
DECLARE @RefCount varchar(100)
DECLARE @sql1 VARCHAR(4000),@sql2 VARCHAR(4000)
DECLARE @querynum varchar(100)
DECLARE @sqlcmd varchar(8000)
DECLARE @request_status varchar(50)
SET @querynum=1


DECLARE RefCursor CURSOR FOR


SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL))


OPEN RefCursor
FETCH NEXT FROM RefCursor INTO @RefCount
WHILE @@FETCH_STATUS = 0
BEGIN
Select 'Query #'+@querynum+'
run by spid : '+@RefCount
SET @request_status=(SELECT
distinct(l.request_status)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND ( l.resource_description = l1.resource_description OR
(l.resource_description IS NULL AND l1.resource_description IS NULL))
and l.request_session_id=@RefCount)
IF (@request_status='WAIT')
begin
     PRINT 'This is the SPID that is being blocked'
    end
else
begin
     PRINT 'This is the SPID that is causing the blocking'
     end
select ''
SET @sqlcmd='Select hostname from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)


SET @sqlcmd='Select loginame from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)
SET @querynum=@querynum+1
SET @sql2='DBCC INPUTBUFFER('+@RefCount+') with no_infomsgs'
SELECT 'Running DBCC INPUTBUFFER for SPID '+@RefCount
EXEC (@sql2)



SELECT '-----------------------------------------------------------------'


FETCH NEXT FROM RefCursor INTO @RefCount
END
CLOSE RefCursor
DEALLOCATE RefCursor
GO

Long running queries due to table spooling

Yesterday a client of mine reported that one of the Stored procedures that they had written was running extremely slow. So after verifying that the statistics were updated and index were not fragmented, I started to look at the access plan that the SP was using. From the access plan it was evident that a table spooling operator was causing most of the delay. I found that the Table Spool physical operator scans the input and places a copy of each row in a hidden spool table (stored in the tempdb database and existing only for the lifetime of the query). If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

So any nested query or an Insert statement like 'INSERT INTO ....SELECT FROM....' would cause table spooling.  I could not find any index that would improve the performance, so I recommended the developer to use a temporary table instead of a subquery and that seemed to work perfectly. The temporary table increased the response of the query tremendously. It is strange that both the results of the subquery and the temporary table are indeed stored in the TEMPDB. So I am not sure what the reason is for the increased performance of the query. The use of temporary table and avoiding the tablespooling is just an observation. I don't know the reason and don't know it will work for everyone but wanted to share this experience.

For more details please refer to this excellent article explaining spooling.
http://www.simple-talk.com/sql/learn-sql-server/operator-of-the-week---spools,-eager-spool/