Find Missing Indexes in a Database
Here
is the SQL SCRIPT that can be helpful identifying missing indexes in a
database. This query identifies missing indexes based on query cost benefit. I have
used this script and found very useful. Again do not believe blindly on the
output of this script and use your common sense before taking any decision.
Best
option is to analyze the execution plan before creating any indexes as per
output from the above script. You need to make sure that you do not create any
unused indexes in your database. Remember having unused indexes affects the
performance of the database. Also this script displays the missing indexes only
when the query improvement cost is more than 10.
ALWAYS
remember whenever the SQL instance gets restarted, data from dynamic management
views get flushed out.
Here
is the SCRIPT:
-- Identifying
missing indexes based on query cost benefit.
SELECT
migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )*
( migs.user_seeks + migs.user_scans )
AS Query_Cost_Benefit ,
'CREATE INDEX
[missing_index_'
+ CONVERT (VARCHAR, mig.index_group_handle) + '_'+ CONVERT (VARCHAR, mid.index_handle) + '_'
+ LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement
+ ' (' + ISNULL(mid.equality_columns, '')
+ CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns, '') + ')'
+ ISNULL(' INCLUDE ('+ mid.included_columns + ')', '')
AS
create_index_statement ,
migs.* ,
mid.database_id ,
mid.[object_id]
FROM sys.dm_db_missing_index_groups
mig
INNER JOIN sys.dm_db_missing_index_group_stats
migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost
*
( migs.avg_user_impact /
100.0 )* ( migs.user_seeks + migs.user_scans ) > 10
ORDER BY
migs.avg_total_user_cost * migs.avg_user_impact
* ( migs.user_seeks + migs.user_scans ) DESC