SQL Indexes and their usage details Using these dynamic management views (DMVs), I wrote the following query, which returns the list of possible missing indexes for all SQL Server user databases. The results are ordered by index advantage that helps you to identify how beneficial each index would be, if we create them on the table. SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer] ,db.[database_id] AS [DatabaseID] ,db.[name] AS [DatabaseName] ,id.[object_id] AS [ObjectID] ,id.[statement] AS [FullyQualifiedObjectName] ,id.[equality_columns] AS [EqualityColumns] ,id.[inequality_columns] AS [InEqualityColumns] ,id.[included_columns] AS [IncludedColumns] ,gs.[unique_compiles] AS [UniqueCompiles] ,gs.[user_seeks] AS [UserSeeks] ,gs.[user_scans] AS [UserScans] ,gs.[last_user_seek] AS [LastUserSeekTime] ,gs.[last_user_scan] AS [LastUserScanTime] ,gs.[avg_total_user_cost] AS [AvgTotalUserCost] ,gs.[avg_user_impact] AS [AvgUserImpact] ,gs.[system_seeks] AS [SystemSeeks] ,gs.[system_scans] AS [SystemScans] ,gs.[last_system_seek] AS [LastSystemSeekTime] ,gs.[last_system_scan] AS [LastSystemScanTime] ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost] ,gs.[avg_system_impact] AS [AvgSystemImpact] ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage] ,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN ',' ELSE '' END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex] ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate] FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK) INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle] INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle] INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id] WHERE id.[database_id] > 4 -- Remove this to see for entire instance ORDER BY [IndexAdvantage] DESC OPTION (RECOMPILE); Obviously these missing indexes are the ones that the SQL Server optimizer identified during query compilation, and these missing index recommendations are specific recommendation targeting a specific query. Consider submitting your workload and the proposed index to theDatabase Tuning Advisor for further evaluation that include partitioning, choice of clustered versus non-clustered index, and so on. Index Usage It’s very important for a DBA to know how the indexes of tables are doing, the below script will let you know the usage of indexes. Based on the column “total_scan” I’ll be able to tell which indexes are mostly used and what is the associated table. This information makes a DBA to keep an eye on those indexes because fragmentations of them may create a performance bottleneck. SELECT o.name [Object_Name], SCHEMA_NAME(o.[schema_id]) [Schema_name], DB_NAME(s.database_id) Database_Name, i.name Index_name, i.Type_Desc, s.user_seeks, s.user_scans, s.user_lookups, (s.user_seeks + s.user_scans) AS total_scan, s.user_updates, s.system_seeks, s.system_scans, s.system_lookups, getdate() Stat_Date FROM sys.objects AS o INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] INNER JOIN sys.dm_db_index_usage_stats AS s ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id AND DB_ID() = s.database_id WHERE o.type = ‘u’ AND i.type IN (1, 2) AND (s.user_seeks > 0 OR s.user_scans > 0 OR s.user_lookups > 0 OR s.system_seeks > 0 OR s.system_scans > 0 OR s.system_lookups > 0) ORDER BY (s.user_seeks + s.user_scans) DESC;

SQL Indexes and their usage details

Using these dynamic management views (DMVs), I wrote the following query, which returns the list of possible missing indexes for all SQL Server user databases. The results are ordered by index advantage that helps you to identify how beneficial each index would be, if we create them on the table.

SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]     ,db.[database_id] AS [DatabaseID]     ,db.[name] AS [DatabaseName]     ,id.[object_id] AS [ObjectID]     ,id.[statement] AS [FullyQualifiedObjectName]     ,id.[equality_columns] AS [EqualityColumns]     ,id.[inequality_columns] AS [InEqualityColumns]     ,id.[included_columns] AS [IncludedColumns]     ,gs.[unique_compiles] AS [UniqueCompiles]     ,gs.[user_seeks] AS [UserSeeks]     ,gs.[user_scans] AS [UserScans]     ,gs.[last_user_seek] AS [LastUserSeekTime]     ,gs.[last_user_scan] AS [LastUserScanTime]     ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]     ,gs.[avg_user_impact] AS [AvgUserImpact]     ,gs.[system_seeks] AS [SystemSeeks]     ,gs.[system_scans] AS [SystemScans]     ,gs.[last_system_seek] AS [LastSystemSeekTime]     ,gs.[last_system_scan] AS [LastSystemScanTime]     ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]     ,gs.[avg_system_impact] AS [AvgSystemImpact]     ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]     ,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE         WHEN id.[equality_columns] IS NOT NULL             AND id.[inequality_columns] IS NOT NULL             THEN '_'         ELSE ''         END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE         WHEN id.[equality_columns] IS NOT NULL             AND id.[inequality_columns] IS NOT NULL             THEN ','         ELSE ''         END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]     ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate] FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK) INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)     ON gs.[group_handle] = ig.[index_group_handle] INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)     ON ig.[index_handle] = id.[index_handle] INNER JOIN [sys].[databases] db WITH (NOLOCK)     ON db.[database_id] = id.[database_id] WHERE id.[database_id] > 4 -- Remove this to see for entire instance ORDER BY [IndexAdvantage] DESC OPTION (RECOMPILE);

Obviously these missing indexes are the ones that the SQL Server optimizer identified during query compilation, and these missing index recommendations are specific recommendation targeting a specific query.  Consider submitting your workload and the proposed index to theDatabase Tuning Advisor for further evaluation that include partitioning, choice of clustered versus non-clustered index, and so on.

Index Usage

It’s very important for a DBA to know how the indexes of tables are doing, the below script will let you know the usage of indexes. Based on the column “total_scan” I’ll be able to tell which indexes are mostly used and what is the associated table. This information makes a DBA to keep an eye on those indexes because fragmentations of them may create a performance bottleneck.

SELECT        o.name [Object_Name],        SCHEMA_NAME(o.[schema_id]) [Schema_name],        DB_NAME(s.database_id) Database_Name,        i.name Index_name,        i.Type_Desc,        s.user_seeks,        s.user_scans,        s.user_lookups,                (s.user_seeks + s.user_scans) AS total_scan,        s.user_updates,        s.system_seeks,        s.system_scans,        s.system_lookups,        getdate() Stat_Date  FROM             sys.objects AS o INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id]     INNER JOIN sys.dm_db_index_usage_stats AS s ON i.[object_id] = s.[object_id]               AND i.index_id = s.index_id     AND DB_ID() = s.database_id  WHERE              o.type = ‘u’    AND i.type IN (1, 2)    AND (s.user_seeks > 0 OR s.user_scans > 0 OR s.user_lookups > 0    OR s.system_seeks > 0 OR s.system_scans > 0 OR s.system_lookups > 0)  ORDER BY (s.user_seeks + s.user_scans) DESC;

The result will look like:

Missing Indexes

The below script will let you know the missing indexes in the selected database, this script is also an important for DBA to know time to time that what are the missing indexes are there because the information of below script will be generated by the actual usage of buffer pool.

SELECT             S.AVG_TOTAL_USER_COST * (S.AVG_USER_IMPACT / 100.0) * (S.USER_SEEKS + S.USER_SCANS) AS EST_IMPROVE,             S.AVG_USER_IMPACT, LEFT (PARSENAME(D.STATEMENT, 1), 32) TABLE_NAME,             ISNULL (D.EQUALITY_COLUMNS,”) + CASE WHEN D.EQUALITY_COLUMNS IS NOT NULL AND D.INEQUALITY_COLUMNS IS NOT NULL THEN ‘,’             ELSE ” END + ISNULL (D.INEQUALITY_COLUMNS, ”) COLUMN_NAMES, ISNULL (D.INCLUDED_COLUMNS , ”) AS INCLUDE_COLUMNS,             S.UNIQUE_COMPILES, S.USER_SEEKS, S.USER_SCANS, S.LAST_USER_SEEK, S.LAST_USER_SCAN, S.AVG_TOTAL_USER_COST,             S.SYSTEM_SEEKS, S.SYSTEM_SCANS, S.LAST_SYSTEM_SEEK, S.LAST_SYSTEM_SCAN, S.AVG_TOTAL_SYSTEM_COST, S.AVG_SYSTEM_IMPACT FROM             SYS.DM_DB_MISSING_INDEX_GROUPS G INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS S ON S.GROUP_HANDLE = G.INDEX_GROUP_HANDLE             INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON G.INDEX_HANDLE = D.INDEX_HANDLE ORDER BY EST_IMPROVE DESC ;

The result will look like:

Index Property table wise

The below script will let you know the count of clustered, non-clustered indexes on a table and index size.

SELECT             sys.tables.name AS [TABLE],             sys.tables.create_date AS CREATE_DATE,             sys.tables.modify_date AS MODIFY_DATE,             CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id) ELSE sys.database_principals.name END AS OWNER,             SUM(ISNULL(CASE INDEXES.TYPE WHEN 0 THEN COUNT_TYPE END, 0)) AS COUNT_HEAP_INDEX,             SUM(ISNULL(CASE INDEXES.TYPE WHEN 1 THEN COUNT_TYPE END, 0)) AS COUNT_CLUSTERED_INDEX,             SUM(ISNULL(CASE INDEXES.TYPE WHEN 2 THEN COUNT_TYPE END, 0)) AS COUNT_NONCLUSTERED_INDEX,             SUM(ISNULL(CASE INDEXES.TYPE WHEN 3 THEN COUNT_TYPE END, 0)) AS COUNT_XML_INDEX,             SUM(ISNULL(CASE INDEXES.TYPE WHEN 4 THEN COUNT_TYPE END, 0)) AS COUNT_SPATIAL_INDEX,             sys.tables.max_column_id_used AS COUNT_COLUMNS,             sys.partitions.rows AS COUNT_ROWS,             SUM(ISNULL(CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGES WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) *             (SELECT low / 1024 AS VALUE FROM master.dbo.spt_values WHERE (number = 1) AND (type = N’E')) AS SIZE_DATA_KB,             SUM(ISNULL(sys.allocation_units.used_pages – CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGES                         WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) * (SELECT low / 1024 AS VALUE FROM             master.dbo.spt_values AS spt_values_2 WHERE (number = 1)             AND (type = N’E') ) AS SIZE_INDEX_KB             FROM                         sys.allocation_units INNER JOIN sys.partitions ON sys.allocation_units.container_id = sys.partitions.partition_id             INNER JOIN                         (                                     SELECT TOP (100) PERCENT object_id, index_id, type AS TYPE, COUNT(*) AS COUNT_TYPE                                     FROM sys.indexes AS indexes_1 GROUP BY object_id, type, index_id ORDER BY object_id                         ) AS INDEXES ON             sys.partitions.object_id = INDEXES.object_id             AND sys.partitions.index_id = INDEXES.index_id             RIGHT OUTER JOIN sys.database_principals             RIGHT OUTER JOIN sys.tables ON sys.database_principals.principal_id = sys.tables.principal_id             ON INDEXES.object_id = sys.tables.object_id             GROUP BY sys.tables.name, sys.tables.create_date, sys.tables.modify_date,             CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id) ELSE sys.database_principals.name END,             sys.tables.max_column_id_used, sys.partitions.rows ORDER BY COUNT_ROWS DESC;

The result will look like: