Query to list Deadlocks thru Extended Event

I have been used to turning on the Trace flags 1222 or 1204 to get information on the deadlocks.Depending upon the type of environment I would either have them turned on at start-up or on demand  in response to errors that were logged in previous night's batch log file and look for this behavior to recur.
On top of this, if I need to dig in a bit further, I would turn on the Profiler trace.

         SQL Server 2008 onwards,there are extended events that runs a default lightweight session that captures deadlock info (and a bunch of others see Jonathan Kehayias article) in a ring buffer and stores it in memory.We could query the Extended Event DMVs to fetch deadlock info from the memory.This is especially awesome since we do not have to turn on the trace flag or profiler.
        There is a very informative article by Jonathan Kehayias on Extended Event for deadlocks.My query has been built on Jonathan's query.

      There are a few changes I had made.I have listed the deadlock timestamp alongside the XML.
Also, I faced an issue with the query running forever in certain 2008\ R2 instances whereas in others it just worked fine.I am not sure if this is a bug. However,I was able to workaround this by moving the inline table's output to a variable (or a temporary table) .

Since I have to be able to capture this info in environments with varying versions and service packs I have added a TRY CATCH block to make sure that the query worked irrespective of whether the issue with the missing tag has been fixed or not.

DECLARE @deadlock_info XML, 
        @last_n_days   INT = 10; 

SELECT @deadlock_info = Cast(target_data AS XML) 
FROM   sys.dm_xe_session_targets st 
       JOIN sys.dm_xe_sessions s 
         ON s.address = st.event_session_address 
WHERE  name = 'system_health' 

BEGIN try 
    SELECT Cast(xeventdata.xevent.value('(data/value)[1]', 
                       'varchar(max)') 
                       AS XML) 
           AS 
           DeadlockGraph, 
           xeventdata.xevent.value('@timestamp', 'datetime2' 
           ) AS 
           Error_Timestamp 
    FROM   (SELECT @deadlock_info AS TargetData) AS 
           deadlock_info 
           CROSS apply targetdata.nodes ( 
                       '//RingBufferTarget/event') AS 
                       XEventData 
                                    ( 
           xevent) 
    WHERE  xeventdata.xevent.value('@name', 'varchar(4000)') 
           = 
                  'xml_deadlock_report' 
           AND xeventdata.xevent.value('@timestamp', 
               'datetime2' 
               ) > ( 
               Getutcdate() - @last_n_days ) 
END try 

BEGIN catch 
    SELECT Cast(Replace(Replace( 
           xeventdata.xevent.value('(data/value)[1]', 
                        'varchar(max)'), 
                        '<victim-list>' 
            , '<deadlock><victim-list>'), '<process-list>', 
                '</victim-list><process-list>') AS XML) AS 
           DeadlockGraph, 
           xeventdata.xevent.value('@timestamp', 'datetime2' 
           ) 
           AS 
           Error_Timestamp 
    FROM   (SELECT @deadlock_info AS TargetData) AS 
           deadlock_info 
           CROSS apply targetdata.nodes ( 
                       '//RingBufferTarget/event') AS 
                       XEventData 
                                    ( 
           xevent) 
    WHERE  xeventdata.xevent.value('@name', 'varchar(4000)') 
           = 
                  'xml_deadlock_report' 
           AND xeventdata.xevent.value('@timestamp', 
               'datetime2' 
               ) > ( 
               Getutcdate() - @last_n_days ) 
END catch 

References
Jonathan Kehayias Article
Bart Duncan's SQL Weblog 

List Index details with Included columns and identify duplicate indexes

Viewing all columns in an index in a single row has not always been easy.In addition to the regular columns, there are also the included columns and at times we want to view this info all in one place.Here is the code to view all this information along with a query to identify duplicate indexes.

SET NOCOUNT ON
  DECLARE Duplicate_index_cur CURSOR FOR
select sch.name +'.'+ tbl.name,idx.name,col.name,inc.index_column_id,inc.is_included_column
 from sys.indexes idx inner join sys.index_columns inc on idx.object_id = inc.object_id
           and  idx.index_id = inc.index_id
  join sys.columns col on idx.object_id = col.object_id and inc.column_id = col.column_id
           join sys.tables tbl on tbl.object_id = idx.object_id
           join sys.schemas sch on sch.schema_id = tbl.schema_id
order by sch.name, tbl.name,idx.index_id,inc.index_column_id desc

  DECLARE @table_name varchar(256),
 @indexname varchar(256),
 @column_name varchar(256),
 @index_order int ,
 @included_column  int,
 @inc_@column_name varchar(256),
 @col_list varchar(4000) = '',
 @inc_col_list        varchar(4000) = '',
 @table          varchar(256) ,
 @index        varchar(256)

Declare @Indextable table ( tablename varchar(256),
indexname varchar(256),
columnname varchar(256),
included_columns varchar(256))

  OPEN Duplicate_index_cur

  FETCH NEXT
  FROM  Duplicate_index_cur
    INTO @table_name,@indexname,@column_name,@index_order,@included_column

/*Set the Initial Parameters*/
SET @table = @table_name
SET @index = @indexname

  WHILE @@FETCH_STATUS = 0
    BEGIN
        /*Reinitialize Variables*/
IF (@table <> @table_name or @index <> @indexname)
BEGIN
Insert into @Indextable
Select @table,@index,@col_list,@inc_col_list
SET @table = @table_name
SET @index = @indexname
SET @col_list = ''
SET @inc_col_list = ''
END

IF(@included_column = 0)
select @col_list =   @col_list +','+ @column_name
ELSE
select @inc_col_list =  @inc_col_list +','+ @column_name

 FETCH NEXT
  FROM  Duplicate_index_cur
    INTO @table_name,@indexname,@column_name,@index_order,@included_column

    END

Insert into @Indextable
Select @table,@index,@col_list,@inc_col_list
  CLOSE Duplicate_index_cur
  DEALLOCATE Duplicate_index_cur

 /*
 Run this query to list all indexes,columns and included columns
  Select tablename,
indexname,
substring(columnname,2,len(columnname)) as columnname,
substring(included_columns,2,len(included_columns)) as Included_Columns
  from  @Indextable
  */
/*List Duplicate Indexes*/
  select org.tablename,
org.indexname,
dup.indexname,
substring(org.columnname,2,len(org.columnname)) as columnname,
substring(org.included_columns,2,len(org.included_columns)) as Included_Columns
from @Indextable org , @Indextable dup
  where org.tablename = dup.tablename
and org.indexname > dup.indexname
and org.columnname   = dup.columnname
and org.Included_Columns = dup.Included_Columns

Rebuild Vs Reorganize Index (and Update Statistics)

Does rebuilding the clustered index rebuild all the other indexes on the table? What happens to the statistics on that column?

I decided to create a table and rebuild it to see what happened to the index.Here is what I did.

• Create Test table,create index and insert records

DROP TABLE [dbo].[Index_table]
GO
CREATE TABLE [dbo].[Index_table](
[COL1] [int] NOT NULL,
[COL2] [int] NOT NULL,
[COL3] [int] NOT NULL
)
GO
CREATE CLUSTERED INDEX   [CIX]     ON [dbo].[Index_table](COL1)
CREATE NONCLUSTERED INDEX  [NCIX1]   ON [dbo].[Index_table](COL2)
CREATE NONCLUSTERED INDEX  [NCIX2]   ON [dbo].[Index_table](COL3)
GO
SET NOCOUNT ON
GO
Insert into Index_table
SELECT ROUND(RAND() * 100,4,0) ,ROUND(RAND() * 100,3,0),ROUND(RAND() * 100,2,0)
GO 50
DROP VIEW [dbo].[Index_table_stats]
GO

• Create a view to query the DMVs after each operation

CREATE View Index_table_stats

as
select OBJECT_NAME(ius.object_id) as 'Table Name',idx.name as 'Index Name',last_system_scan  as 'Rebuild Time',system_scans as 'Rebuild count',idx.type_desc,STATS_DATE(ius.OBJECT_ID, ius.index_id) AS 'Stats Updated'
FROM sys.dm_db_index_usage_stats ius join sys.indexes idx
ON OBJECT_NAME(ius.object_id) = OBJECT_NAME(idx.object_id) and ius.index_id = idx.index_id
WHERE OBJECT_NAME(ius.object_id) = 'Index_table'

• Run the below queries and to check the index properties and rebuild the column

select * from Index_table_stats order by type_desc
ALTER INDEX CIX on dbo.Index_table REBUILD
select * from Index_table_stats order by type_desc

As seen from the result,rebuilding the clustered index just rebuilds that specific index as indicated by the "Rebuild Time" column.Every rebuild also updates the statistics for that column with a fullscan.
I did notice one weird behavior in SQL Server 2008 R2. When the index is rebuilt the very first time,the "Rebuild count" is displayed as 2.I am not sure why the index is rebuilt twice.

Does rebuilding the non clustered index rebuild just that index?What happens to the statistics on that column?

ALTER INDEX NCIX1 on dbo.Index_table REBUILD
ALTER INDEX NCIX2 on dbo.Index_table REBUILD
select * from Index_table_stats order by type_desc

As evident from the result,for every index that is rebuilt,statistics are also updated with a fullscan

Does the Rebuild ALL syntax rebuild all indexes? 

ALTER INDEX ALL on dbo.Index_table REBUILD
select * from Index_table_stats order by type_desc

Rebuild All would rebuild and update statistics with fullscan all the indexes on that table

Does reorganizing a clustered or non clustered index reorganize the rest of the indexes? What happens to the statistics on that column?

It would be easier if you drop and recreate the table and reorg the index as you could see null values,though this step is not mandatory.

ALTER INDEX CIX on dbo.Index_table REORGANIZE
ALTER INDEX NCIX1 on dbo.Index_table REORGANIZE
ALTER INDEX NCIX2 on dbo.Index_table REORGANIZE
ALTER INDEX ALL on dbo.Index_table REORGANIZE
select * from Index_table_stats order by type_desc

REORGANIZE affects just the index on which the operation is performed (BOL).I was not able to find a DMV\column that dispalys the time REORGANIZE was last performed.
Unlike rebuild,reorg does NOT update statistics on any of the indexes as evident from the "Stats Updated" column.

RoboCopy and RichCopy

Here are two microsoft tools which extend the copy functionality.I have used the Robocopy earlier.I tested RichCopy  recently and liked this GUI.

Multiple options to Clear query cache and buffer pool

While tuning queries it becomes important to run the existing query and obtain initial execution time.

Changes are then made to the query or underlying indexes or statistics. The modified query is executed and the execution time is compared with the initial value.

The query gets compiled the very first time it is executed and the query plan is cached. Similarly the results of the first execution are stored in the buffer pool.

As a result the second time the test query is executed, unless the query is recompiled the execution time would be faster and undependable.

With every change the query should be executed with the exact same set of conditions as the initial execution. How can this be done?

This can be achieved by clearing the plan cache and buffer pool using these commands

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Note: Do not run these commands on a production server. These commands will cause  queries to be recompiled and it may adversely impact performance. This changes clear plan cache and buffer pool for the entire instance.

DBCC FREEPROCCACHE

Removes all cached query plans and execution contexts from the plan cache,

Removes a specific plan from the plan cache by specifying a plan handle or SQL handle or

Removes all cache entries associated with a specified resource pool.

Example:

SELECT * FROM Person.Address;

GO

SELECT plan_handle, st.text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'SELECT * FROM Person.Address%';

GO

dbcc freeproccache(0x060005123RGCA270E306E5EF00200000001000000000000000000000000000000000000000000000000000000)

DBCC FREESYSTEMCACHE

Removes all plans from all caches or from a specified Resource Governor pool.

Example: DBCC FREESYSTEMCACHE('SQL Plans')

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

DBCC FLUSHPROCINDB( db_id )

Removes all cached plans from the plan cache for a particular database.

Example:

       Declare @dbid  int = DB_ID()

       DBCC FLUSHPROCINDB(@dbid)

DBCC DROPCLEANBUFFERS

Removes all clean buffers from the buffer pool. Issue a checkpoint to write all dirty pages to disk and then execute the command.

SQL Server Ebook collection

I found Performance Tuning with SQL Server Dynamic Management Views by Tim Ford and Louis Davidson really really helpful.

I have the paperback edition and I was looking for the online version to avoid typing the sample queries.The entire book and many more are available for free download.Happy reading!

SSMS Tool Pack

Checkout the features of SSMS Tool pack. SSMS2012 needs a license.Prior versions are free.