Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

check missing index Expand / Collapse
Posted Monday, May 13, 2013 4:38 PM


Group: General Forum Members
Last Login: 2 days ago @ 4:12 PM
Points: 1,975, Visits: 3,706
Is there some good queries for checking missing indexes on all tables of a database?

Post #1452356
Posted Tuesday, May 14, 2013 2:37 AM



Group: General Forum Members
Last Login: Thursday, October 20, 2016 1:09 AM
Points: 2,989, Visits: 4,907
try below query

,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
'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
Post #1452428
Posted Tuesday, May 14, 2013 3:01 AM



Group: General Forum Members
Last Login: Today @ 10:06 PM
Points: 45,436, Visits: 43,788
Do not automatically create all the indexes that the missing index DMVs suggest, that's a good way to severely over-index and hinder performance.

Evaluate what the missing index DMVs suggest, examine the existing indexes, test the suggestions, implement what's useful.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1452441
Posted Tuesday, May 14, 2013 4:09 AM



Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 17,064, Visits: 31,954
And remember, the missing index information, as is, doesn't relate directly to any query at all. So simply taking that stuff as written, you might be creating an index for a query that was run once and then never again.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1452481
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse