如何在SQL Server 2005中通过SQL QUERY在DataBase中找到丢失和未使用的索引？ [英] How to find the missing and unused Indexes in a DataBase via SQL QUERY in sql server 2005?

问题描述

如何在sql server 2005中通过SQL QUERY在DataBase中找到表的缺失和未使用的索引？

How to find the missing and unused Indexes of a Table in a DataBase via SQL QUERY in sql server 2005?

推荐答案

SQL Server - 查找丢失和未使用的索引 [ ^ ]：

有关缺失索引的数据存储在以下DMV中，这些DMV都排除了有关空间索引的信息：

The data about missing indexes is stored in the following DMV’s which all exclude info about spatial indexes:

sys.dm_db_missing_index_groups

此DMV仅返回2列，其中包含哪些索引在哪个组中的信息。

This DMV returns only 2 columns with information about which indexes are in which group.

sys.dm_db_missing_index_group_stats

此DMV返回有关每个缺失索引组的信息。它返回的信息类似于估计的平均影响或搜索，扫描和编辑/重新编译将从添加缺失索引中受益的数量。

This DMV returns information about each missing indexes group. It returns info like the estimated average impact or how many seeks, scans and compilations/recompilations would benefit from adding the missing indexes.

sys.dm_db_missing_index_details

此DMV返回有关每个缺失索引的详细信息，例如缺少索引的表名和索引对其有益的列的CSV。

This DMV returns detailed information about each missing index like table name that is missing an index and CSV’s of columns that the index would be beneficial on.

sys.dm_db_missing_index_columns

这是一个接受index_handle参数的动态管理函数（DMF）。它返回应该在index_handle标识的建议索引中的列，该索引可以从sys.dm_db_missing_index_details和sys.dm_db_missing_index_groups获得。它不包括空间索引。名为column_usage的列将返回有关此列如何为特定索引带来好处的信息。 EQUALITY和INEQUALITY意味着该列将用于where子句谓词。 INCLUDE表示该列应该是现有非聚集索引上的包含列。

This a Dynamic Management Function (DMF) that accepts an index_handle parameter. It returns columns that should be in the suggested index identified with the index_handle that can be obtained from sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups. It does not include spatial indexes. The column named column_usage returns info on how this column would benefit for a particular index. EQUALITY and INEQUALITY mean that the column would be used in a where clause predicate. INCLUDE means that the column should be an included column on an existing non-clustered index.

或

使用以下脚本 [ ^ ]：

OR
Use the follwing script[^]:

DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO

- Amy

这篇关于如何在SQL Server 2005中通过SQL QUERY在DataBase中找到丢失和未使用的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！