分類: Index

Missing Indexes for All Databases

一直以來都知道Execution Plan可以看Missing Indexes，但是並不知道其實SQL Server一直以來都有將Missing Indexes記錄起來，利用以下的Script可以找出每個DB中所缺失的Indexes，作為建立新的index的依據和考量。

SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],

migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],

mid.equality_columns, mid.inequality_columns, mid.included_columns,

migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

ORDER BY index_advantage DESC OPTION (RECOMPILE);

-- Getting missing index information for all of the databases on the instance is very useful

-- Look at last user seek time, number of user seeks to help determine source and importance

-- Also look at avg_user_impact and avg_total_user_cost to help determine importance

-- SQL Server is overly eager to add included columns, so beware

-- Do not just blindly add indexes that show up from this query!!!

-- Refer Glenn Berry on Pluralslight -- Glenn@SQLskills.com -- GlennAlanBerry

繼續閱讀 “Missing Indexes for All Databases"