New Index Creation Considerations - SQL Server

For a DBA, it is normal to receive index recommendations from the application team or users. And this recommendation is typically not based on sound database knowledge. For example, users would run DTA - Database Tuning Advisor and come up with the recommendations. While I support the utility of the DTA tool and believe it is a great 1st step, the recommendations coming out of it should not be implemented blindly.

In this post, I am trying to provide a brief description on the things to do or consider before we create a new index.

• Review the existing indexes on the table. Check for duplicate or overlapping indexes. To identify existing indexes on a table use the below TSQL:

exec sp_helpindex

• Review the suggested index along with missing index details from the DMV: sys.dm_db_missing_index_details. We can use the below TSQL to get the list of missing indexes identified by SQL Server. Please use the output of the below query just as reference and not as the determining factor.

USE [database-name]
GO
SELECT db_name(d.database_id) [DB_Name],
object_name(d.object_id) Table_Name,
d.equality_columns Equality_Columns,
d.inequality_columns Inequality_Columns,
d.included_columns Included_Columns,
gs.unique_compiles Unique_Compiles,
gs.user_seeks User_Seeks,
gs.user_scans User_Scans,
gs.last_user_seek Last_User_Seek,
gs.avg_total_user_cost Avg_Total_User_Cost,
gs.avg_user_impact Avg_User_Impact
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats gs
ON gs.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON g.index_handle = d.index_handle
WHERE d.database_id = d.database_id
AND d.object_id = d.object_id
AND db_name(d.database_id) = 'database-name'
AND object_name(d.object_id) = 'table-name'
ORDER BY gs.user_seeks DESC
GO

• Get the SQL from the requester for which they are recommending the index. Before we implement the index, execute the SQL and get the time, IO and execution plan details so that we can compare it after we create the index. Use the below SET options to get the Time and IO details. On SSMS, there is a button on top to enable Inclusion of Actual Execution Plan

SET STATISTICS TIME ON

SET STATISTICS IO ON

• Once we create the index, redo the above step and compare the output. We should immediately see the difference.
• Give it some time and depending on how often the index is expected to be used, run the below TSQL to get the metrics of the index usage.

USE [database-name]
GO
SELECT OBJECT_NAME(i.object_id) Table_Name,
i.name Index_Name,
ius.user_seeks User_Seeks,
ius.user_scans User_Scans,
ius.user_lookups User_Lookups,
ius.user_updates User_Updates,
ius.last_user_seek Last_User_Seek,
ius.last_user_scan Last_User_Scan,
ius.last_user_lookup Last_User_Lookup,
ius.last_user_update Last_User_Update
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i
ON ius.[object_id] = i.[object_id]
AND ius.index_id = i.index_id
WHERE ius.database_id = 7
AND OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
AND i.index_id <> 0
AND OBJECT_NAME(i.[object_id]) = 'table-name'
GO