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.