CHARPTER 3--INDEX DMVs

1.查找最重要的缺失的索引　

--=======================================================
--查找最重要的缺失的索引
SELECT TOP(20)
DB_NAME() AS DBName,
ROUND(s.avg_total_user_cost*s.avg_user_impact
*(S.user_seeks+S.user_scans),0) AS [TotalCost],
D.[statement] AS TableName,
D.equality_columns,
D.inequality_columns,
D.included_columns
INTO #TB1
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

EXEC sp_MSforeachdb '
USE [?]
INSERT INTO #TB1
SELECT TOP(20)
DB_NAME() AS DBName,
ROUND(s.avg_total_user_cost*s.avg_user_impact
*(S.user_seeks+S.user_scans),0) AS [TotalCost],
D.[statement] AS TableName,
D.equality_columns,
D.inequality_columns,
D.included_columns
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 TotalCost DESC'

PS:

avg_total_user_cost：Average cost of the user queries that could be reduced by the index in the group.
avg_user_impact ：Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

user_seeks :Number of seeks caused by user queries that the recommended index in the group could have been used for.Not the number of times the index has been accessed.
user_scans :Number of scans caused by user queries that the recommended index in the group could have been used for.
参考:http://msdn.microsoft.com/zh-cn/library/ms345421.aspx

 

 2. 查找无用索引

--==============================================================
--查找无用的索引,当索引使用次数和索引更新次数比在一个很小范围时，
--应该考虑该索引是否合理。

--在SQL SERVER DMVs IN ACTION书中判断无用索引的标注是
--S.[user_seeks]=0 AND S.[user_scans]=0 AND s.[user_lookups]=0
--该标准会将更新过于频繁但会偶尔使用的索引排出在外。

--因此还是建议使用索引使用次数和索引更新次数比，该比值可以自行调整
--==============================================================

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
DB_NAME() AS DBName,
SCHEMA_NAME(O.[Schema_ID]) AS SchemaName,
OBJECT_NAME(O.[Object_ID]) AS TableName,
I.[name] AS IndexName,
S.[user_Updates],
(S.system_seeks+S.system_scans+s.system_lookups) AS [SystemUsage],
(S.[user_seeks]+S.[user_scans]+s.[user_lookups]) AS [UserUsage]
INTO #UnusedIndexes
FROM sys.dm_db_index_usage_stats S
INNER JOIN sys.indexes I
ON I.[object_id]=S.[object_id]
AND I.[index_id]=S.[index_id]
INNER JOIN sys.objects O
ON O.[object_id]=I.[object_id]
WHERE 1=2

EXEC sp_MSForeachDB '
use [?];
INSERT INTO #UnusedIndexes
SELECT TOP(20)
DB_NAME() AS DBName,
SCHEMA_NAME(O.[Schema_ID]) AS SchemaName,
OBJECT_NAME(O.[Object_ID]) AS TableName,
I.[name] AS IndexName,
S.[user_Updates],
(S.system_seeks+S.system_scans+s.system_lookups) AS [SystemUsage],
(S.[user_seeks]+S.[user_scans]+s.[user_lookups]) AS [UserUsage]
FROM sys.dm_db_index_usage_stats S
INNER JOIN sys.indexes I
ON I.[object_id]=S.[object_id]
AND I.[index_id]=S.[index_id]
INNER JOIN sys.objects O
ON O.[object_id]=I.[object_id]
WHERE S.[database_id]=DB_ID()
AND OBJECTPROPERTY(s.[object_id],''ISMSShipped'')=0
AND (S.[user_seeks]+S.[user_scans]+s.[user_lookups])*1.0/(S.[user_Updates]+1)<0.3
AND I.name IS NOT NULL
ORDER BY S.[user_Updates] DESC
'

SELECT TOP(100)* FROM #UnusedIndexes
ORDER BY S.[user_Updates] DESC

DROP TABLE #UnusedIndexes

参考:http://msdn.microsoft.com/zh-cn/library/ms188755.aspx

 

 3. 查找碎片较多的索引

--=================================================================
--查看索引碎片最高的索引
--运行在LIMITIED模式来减少对系统的影响
--参考连接：http://technet.microsoft.com/zh-cn/library/ms188917.aspx


--=================================================================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT DB_NAME() AS DBName,
SCHEMA_NAME(O.[Schema_ID]) AS SchemaName,
OBJECT_NAME(O.[Object_id]) AS ObjectName,
I.name AS IndexName,
ROUND(S.avg_fragmentation_in_percent,2) AS [FragmentationPercent]
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null) AS S
INNER JOIN sys.indexes I 
ON I.[Object_ID]=S.[Object_id]
AND I.[Index_id]=S.[Index_id]
INNER JOIN sys.objects O 
ON O.[object_Id]=I.[object_id]
WHERE S.[database_id]=DB_ID()
AND I.[name] IS NOT NULL
AND OBJECTPROPERTY(O.[object_id],'ISMSShipped')=0
AND 1=2
ORDER BY [FragmentationPercent] DESC

EXEC SP_MSFOREACHDB '
USE [?];
INSERT INTO  #TempFragmentation
SELECT TOP(20)
DB_NAME() AS DBName,
SCHEMA_NAME(O.[Schema_ID]) AS SchemaName,
OBJECT_NAME(O.[Object_id]) AS ObjectName,
I.name AS IndexName,
ROUND(S.avg_fragmentation_in_percent,2) AS [FragmentationPercent]

FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null) AS S
INNER JOIN sys.indexes I 
ON I.[Object_ID]=S.[Object_id]
AND I.[Index_id]=S.[Index_id]
INNER JOIN sys.objects O 
ON O.[object_Id]=I.[object_id]
WHERE S.[database_id]=DB_ID()
AND I.[name] IS NOT NULL
AND OBJECTPROPERTY(O.[object_id],''ISMSShipped'')=0
ORDER BY [FragmentationPercent] DESC
'

SELECT TOP(20)*
FROM #TempFragmentation
ORDER BY [FragmentationPercent] DESC

DROP TABLE #TempFragmentation

 

4. 查看索引的统计信息

--==========================================================
--查看统计的状态信息
--==========================================================
SELECT 
SS.[name] AS SchemaName,
ST.[name] AS TableName,
STATS_DATE(S.ID,S.IndID) AS StatisticsLastUpdated,
S.rowcnt AS [RowCount],
s.rowmodctr AS [NumberOfChanges],
ROUND(s.rowmodctr*1.0/s.rowmodctr*100,2) AS [RowChangedPercent]
FROM sys.sysindexes S
INNER JOIN sys.tables ST
ON ST.[object_id]=S.[id]
INNER JOIN sys.schemas SS
ON ss.[Schema_id]=ST.[schema_id]
WHERE S.ID>100
AND S.indid>0
AND s.rowcnt>=500