Technical Article

Get Missing Index info

,

Script.

DECLARE @IndexinfoFor VARCHAR(100)

SET@IndexinfoFor = 'dbo.AnyTableName'

SELECTIndex_id, user_seeks, user_scans, user_lookups, user_updates, last_user_seek,
last_user_scan, last_user_lookup, last_user_update, system_seeks, system_scans,
system_lookups, system_updates, last_system_seek, last_system_scan, 
last_system_lookup, last_system_update
FROMsys.dm_db_index_usage_stats
WHEREobject_id = object_id(@IndexinfoFor)

SELECTmic.*, mid.Equality_columns, mid.Inequality_columns, mid.Included_columns, mid.Statement,
migs.Unique_Compiles, migs.User_Seeks, migs.User_Scans, migs.Last_User_Seek,
migs.Avg_Total_User_Cost, migs.Avg_User_Impact, migs.System_Scans, 
migs.System_Seeks, migs.Last_System_Scan, migs.Last_System_Seek, 
migs.Avg_total_system_Cost, migs.Avg_system_Impact 
FROMsys.dm_db_index_usage_stats ius
LEFT OUTER JOIN sys.dm_db_missing_index_details mid
ONius.object_id = mid.object_id
CROSS APPLY sys.dm_db_missing_index_columns(mid.index_handle) mic
LEFT OUTER JOIN sys.dm_db_missing_index_groups mig
ONmid.index_handle = mig.index_handle
LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs
ONmig.index_group_handle = migs.group_handle
WHEREius.object_id = object_id(@IndexinfoFor)

Read 1,637 times
(5 in last 30 days)

Rate

3.5 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (4)

You rated this post out of 5. Change rating