SQL Server Indexes Monitoring

SQL Server Indexes Monitoring

                                                SQL Server Database monitoring is some times little bit boring task for you.But it will be interesting if you doing real investigation on it and Do some R&D or try to think as out of box.Any way today I'm going to post some T-SQL queries will be helpful for Database Administrators or Developers who is doing Index monitoring on their databases.

As a task you should monitor the usage of the standard indexes and consider dropping indexes that are rarely used or not used.You can use the following query to identify indexes that have not been used since the last time SQL Server was started,

USE <your database name>
GO
SELECT    object_name(i.[object_id]) AS [object_name],
        i.name AS [index_name]
FROM sys.indexes i INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE    (o.[type] = 'U') AND
        (NOT EXISTS (SELECT 1 FROM sys.dm_db_index_usage_stats s WHERE s.[index_id] = i.[index_id] AND s.[object_id] = i.[object_id] AND s.[database_id] = db_id()))
ORDER BY object_name(i.[object_id]) ASC;

It's not good to have duplicate indexes in each table. if you have duplicate indexes then you should have to drop them and need to have processes to apply new indexes to given table(Ex: Validate Duplicate index). Following T-SQL queries will help you to find duplicate indexes on give database,

USE <your database name>
GO

;WITH indexcols AS
  (SELECT [object_id] AS id,
    [index_id]        AS indid,
    [name],
    (SELECT
      CASE [keyno]
        WHEN 0
        THEN NULL
        ELSE [colid]
      END                 AS [data()]
    FROM sys.sysindexkeys AS k
    WHERE k.id  = i.[object_id]
    AND k.indid = i.index_id
    ORDER BY keyno,
      colid FOR XML PATH('')
    ) AS cols,
    (SELECT
      CASE keyno
        WHEN 0
        THEN colid
        ELSE NULL
      END                 AS [data()]
    FROM sys.sysindexkeys AS k
    WHERE k.[id]  = i.[object_id]
    AND k.[indid] = i.[index_id]
    ORDER BY colid FOR XML PATH('')
    ) AS inc
  FROM sys.indexes AS i
  )
SELECT    OBJECT_SCHEMA_NAME(ic1.id) + '.' + OBJECT_NAME(ic1.id)  AS [Table],
        ic1.[name]                                              AS [Index Name],
        ic2.[name]                                              AS [Duplicate Index Name]
FROM indexcols ic1 INNER JOIN indexcols ic2 ON ic1.id = ic2.id
    AND (ic1.indid < ic2.indid)
    AND (ic1.cols  = ic2.cols)
    AND (ic1.inc   = ic2.inc); 

 If you want to check index information regular basis then you can use the following query to monitor the index usage on a regular basis.

USE <your database name>
GO

SELECT    object_name(i.[object_id]) AS [object_name],
        i.[name] AS [index_name],
        s.[index_id],
        [user_seeks] + [user_scans] + [user_lookups] AS user_reads,
        [system_seeks] + [system_scans] + [system_lookups] AS system_reads,
        [user_updates],
        [system_updates]
FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i
    ON s.[index_id] = i.[index_id] AND s.[object_id] = i.[object_id]
WHERE    (s.[database_id] = db_id()) AND
        (i.[type] <> 0)
ORDER BY [user_reads] DESC;

If you need to find missing index details in given SQL Server instance then following SQL statement will help you to find it, 

USE <your database name>
GO
SELECT mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
migs.last_user_seek,
'CREATE NONCLUSTERED INDEX [IX_'+OBJECT_NAME(mid.[object_id])+'_'+REPLACE(REPLACE(REPLACE(REPLACE(mid.equality_columns,'[',''),']',''),',',''),' ','_') +'] ON '+mid.[statement]+' ('+mid.equality_columns+')'+CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE ('+mid.included_columns+') ' ELSE '' END+';' ssql
FROM    sys.dm_db_missing_index_group_stats migs INNER JOIN
        sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle INNER JOIN
        sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY user_seeks DESC;

Hope above SQL statement will help you to find some helpful information about indexes.

Cheers...