Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
1，查询待建立的索引。sys.dm_db_missing_index_groups,sql如下：
SELECT * FROM (
SELECT TOP 50
ROUND(s.avg_total_user_cost * (s.avg_user_impact/100) *
(s.user_seeks + s.user_scans),0) AS [improvement_measure]
, s.avg_user_impact
, d.statement AS TableName
, d.equality_columns
, d.inequality_columns
, d.included_columns,
'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle)
+ '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'
+ ' ON ' + d.statement
+ ' (' + ISNULL (d.equality_columns,'')
+ CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (d.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY improvement_measure DESC)M
WHERE m.improvement_measure > 10000 --大于10000者需要重建
2，注：1)，avg_user_impact，用户查询可能获得的平均百分比收益，因是数字，故要除以100，以取得可获取的提高性能的倍数。2)，此方法有局限性，指定的建立索引列的并不都是正确的，要手工判定。
3，查询可删除的索引，很少使用。
Select Top 30 database_id, t.object_id, ix.index_id,user_updates, (user_seeks+user_scans) ,t.name,ix.name,sc.name
from sys.dm_db_index_usage_stats ius
JOIN sys.tables t ON ius.object_id = t.object_id
JOIN sys.indexes ix ON t.object_id = ix.object_id AND ix.index_id = ius.index_id
JOIN sys.index_columns ixc ON t.object_id = ixc.object_id AND ix.index_id = ixc.index_id
JOIN sys.columns sc ON t.object_id = sc.object_id AND ixc.column_id = sc.column_id
where user_updates > 10 * (user_seeks+user_scans)
and ius.index_id > 1
AND CHARINDEX('merge',t.name) <1
order by user_updates / (user_seeks+user_scans+1) DESC