Database Tuning Advisor建议创建现有索引 [英] Database Tuning Advisor recommends to create an existing index
本文介绍了Database Tuning Advisor建议创建现有索引的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当我运行SQL Server 2005数据库调优顾问时,它建议创建索引,但它会建议索引已经有索引的列。为什么它建议再次创建相同的索引?
When I run SQL Server 2005 Database Tuning Advisor, it gives a recommendation to create an index, but it will recommends to index a column which already has an index on it. Why does it give a recommendation to create the same index again?
这是我的SQL:
SELECT t.name AS 'affected_table'
, 'Create NonClustered Index IX_' + t.name + '_'
+ CAST(ddmid.index_handle AS VARCHAR(10))
+ ' On ' + ddmid.STATEMENT
+ ' (' + IsNull(ddmid.equality_columns,'')
+ CASE
WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL
THEN ','
ELSE ''
END
+ ISNULL(ddmid.inequality_columns, '')
+ ')'
+ ISNULL(' Include (' + ddmid.included_columns + ');', ';')
AS sql_statement
, ddmigs.user_seeks
, ddmigs.user_scans
, CAST((ddmigs.user_seeks + ddmigs.user_scans)
* ddmigs.avg_user_impact AS INT) AS 'est_impact'
, ddmigs.last_user_seek
FROM
sys.dm_db_missing_index_groups AS ddmig
INNER JOIN sys.dm_db_missing_index_group_stats AS ddmigs
ON ddmigs.group_handle = ddmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS ddmid
ON ddmig.index_handle = ddmid.index_handle
INNER Join sys.tables AS t
ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE
ddmid.database_id = DB_ID()
AND CAST((ddmigs.user_seeks + ddmigs.user_scans)
* ddmigs.avg_user_impact AS INT) > 100
ORDER BY
CAST((ddmigs.user_seeks + ddmigs.user_scans)
* ddmigs.avg_user_impact AS INT) DESC;
推荐答案
也许尝试DESC以不同的方式订购?
Perhaps try "DESC" to order a different way?
这在另一个类似的SO问题中起作用... 为什么SQL Server 2005动态管理视图报告缺少索引?
This worked in another similar SO question... Why does SQL Server 2005 Dynamic Management View report a missing index when it is not?
这篇关于Database Tuning Advisor建议创建现有索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文