Because different customers use software in different ways, not every customer will benefit from exactly the same database-level indexes.
As a best practice, when users complain about database performance in a specific area of the application, or when performance tuning in general, I like to let the customer run with the application for a few days, and then use a query such as the one included below (adapted from Jeff Garbus at Soaring Eagle: https://mssqlperformance.blogspot.com):
–Source: Jeff Garbus from Soaring Eagle – Blog: https://mssqlperformance.blogspot.com/
–DRM – 2020-05-04 – Adapted by Darren to deal with mixed SQL Environments, include table name in index name, deal with table names containing spaces, etc.
–Use this SQL Script to identify indexes that if added could help improve performance.
–IMPORTANT: Do not BLINDLY implement the script recommendations. For example, if the SAME TABLE is mentioned multiple times, then
– it may be better to COMBINE the recommendations of multiple statements into a single one.
– If you need help, Soaring Eagle.
DECLARE @Edition varchar(50)
SET @Edition = CONVERT(varchar(50), SERVERPROPERTY(‘Edition’))
DECLARE @bEnterpriseEdition bit
IF (LEFT(@Edition, 9) = ‘Enterpris’ OR LEFT(@Edition, 9) = ‘Developer’)
SET @bEnterpriseEdition = 1
‘CREATE INDEX IX_’
+ REPLACE(obj.Name, ‘ ‘, ”) +’_’
+ replace(replace(replace (equality_columns, ‘[‘, ”),’]’,”),’, ‘,’_’)
+ ‘ ON ‘
+ sch.name COLLATE SQL_Latin1_General_CP1_CI_AS + ‘.’ + QuoteName(obj.name)
+ ‘ (‘
+ CASE WHEN inequality_columns IS NULL
ELSE ‘,’ + inequality_columns
+ CASE WHEN included_columns IS NOT NULL
THEN ‘ INCLUDE (‘ + ISNULL(included_columns,”) + ‘) ‘
+ CASE WHEN @bEnterpriseEdition = 1
THEN ‘ WITH (online = ON)’
+ ‘ — ‘ + CONVERT (varchar, avg_user_impact) + ‘% anticipated impact’
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig
ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
JOIN sys.objects obj
ON obj.object_id = mid.object_id
JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
database_id = db_id()
AND avg_user_impact > 60
AND equality_columns IS NOT NULL
The output of this script should be reviewed carefully to see if the tables / recommendations make sense for the specific scenario. Remember that there is a cost for implementing index changes that may outweigh the benefits. The most common change that I make to the results from the above is to combine multiple recommendations for the same index into a single index with more elements in the INCLUDE() section.
For Blue Link employees reading this Blog, a version of the above is now available as IndexCreationRecommendationPROC().