About the index of the DMV

Transferred from: Http://www.cnblogs.com/CareySon/archive/2012/05/17/2505981.html#commentform

About the index of the DMV

1. View those indexes that are heavily updated and rarely used

SET TRANSACTION Isolation Level READ UNCOMMITTED
SELECT
Db_name () as DatabaseName
, Schema_name (o.schema_id) as SchemaName
, object_name (s.[object_id]) as TableName
, I.name as IndexName
, s.user_updates
, S.system_seeks + S.system_scans + s.system_lookups
As [System usage]
Into #TempUnusedIndexes
From Sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i on s.[object_id] = i.[object_id]
and s.index_id = i.index_id
INNER JOIN sys.objects o on i.object_id = o.object_id
WHERE 1=2
EXEC sp_msforeachdb ' use [?];
INSERT into #TempUnusedIndexes
SELECT TOP 20
Db_name () as DatabaseName
, Schema_name (o.schema_id) as SchemaName
, object_name (s.[object_id]) as TableName
, I.name as IndexName
, s.user_updates
, S.system_seeks + S.system_scans + s.system_lookups
As [System usage]
From Sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i on s.[object_id] = i.[object_id]
and s.index_id = i.index_id
INNER JOIN sys.objects o on i.object_id = o.object_id
WHERE s.database_id = db_id ()
and OBJECTPROPERTY (s.[object_id], "ismsshipped") = 0
and S.user_seeks = 0
and S.user_scans = 0
and s.user_lookups = 0
And I.name is not NULL
ORDER by S.user_updates DESC '
SELECT TOP * from #TempUnusedIndexes ORDER by [user_updates] DESC
DROP TABLE #TempUnusedIndexes

Results:

This type of index should be dropped

Index of the highest maintenance cost

SET TRANSACTION Isolation Level READ UNCOMMITTED
SELECT
Db_name () as DatabaseName
, Schema_name (o.schema_id) as SchemaName
, object_name (s.[object_id]) as TableName
, I.name as IndexName
, (s.user_updates) as [Update usage]
, (S.user_seeks + S.user_scans + s.user_lookups) as [retrieval usage]
, (S.user_updates)-
(S.user_seeks + S.user_scans + s.user_lookups) As [maintenance cost]
, S.system_seeks + S.system_scans + s.system_lookups as [system usage]
, S.last_user_seek
, S.last_user_scan
, S.last_user_lookup
Into #TempMaintenanceCost
From Sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i on s.[object_id] = i.[object_id]
and s.index_id = i.index_id
INNER JOIN sys.objects o on i.object_id = o.object_id
WHERE 1=2
EXEC sp_msforeachdb ' use [?];
INSERT into #TempMaintenanceCost
SELECT TOP 20
Db_name () as DatabaseName
, Schema_name (o.schema_id) as SchemaName
, object_name (s.[object_id]) as TableName
, I.name as IndexName
, (s.user_updates) as [Update usage]
, (S.user_seeks + S.user_scans + s.user_lookups)
As [retrieval usage]
, (S.user_updates)-
(S.user_seeks + User_scans +
s.user_lookups) as [maintenance cost]
, S.system_seeks + S.system_scans + s.system_lookups as [system usage]
, S.last_user_seek
, S.last_user_scan
, S.last_user_lookup
From Sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i on s.[object_id] = i.[object_id]
and s.index_id = i.index_id
INNER JOIN sys.objects o on i.object_id = o.object_id
WHERE s.database_id = db_id ()
And I.name is not NULL
and OBJECTPROPERTY (s.[object_id], "ismsshipped") = 0
and (S.user_seeks + S.user_scans + s.user_lookups) > 0
ORDER by [maintenance cost] DESC '
SELECT Top * from #TempMaintenanceCost ORDER by [maintenance cost] DESC
DROP TABLE #TempMaintenanceCost

Results:

Maintenance cost should be dropped.

Use frequent indexes

--Use frequent indexes
SET TRANSACTION Isolation Level READ UNCOMMITTED
SELECT
Db_name () as DatabaseName
, Schema_name (o.schema_id) as SchemaName
, object_name (s.[object_id]) as TableName
, I.name as IndexName
, (S.user_seeks + S.user_scans + s.user_lookups) as [Usage]
, s.user_updates
, I.fill_factor
Into #TempUsage
From Sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i on s.[object_id] = i.[object_id]
and s.index_id = i.index_id
INNER JOIN sys.objects o on i.object_id = o.object_id
WHERE 1=2
EXEC sp_msforeachdb ' use [?];
INSERT into #TempUsage
SELECT TOP 20
Db_name () as DatabaseName
, Schema_name (o.schema_id) as SchemaName
, object_name (s.[object_id]) as TableName
, I.name as IndexName
, (S.user_seeks + S.user_scans + s.user_lookups) as [Usage]
, s.user_updates
, I.fill_factor
From Sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i on s.[object_id] = i.[object_id]
and s.index_id = i.index_id
INNER JOIN sys.objects o on i.object_id = o.object_id
WHERE s.database_id = db_id ()
And I.name is not NULL
and OBJECTPROPERTY (s.[object_id], "ismsshipped") = 0
ORDER by [Usage] DESC
SELECT TOP * from #TempUsage ORDER by [Usage] DESC
DROP TABLE #TempUsage

Results

This type of index requires extra attention, and do not kill when optimizing

The most fragmented index

SET TRANSACTION Isolation Level READ UNCOMMITTED
SELECT
Db_name () as Datbasename
, Schema_name (o.schema_id) as SchemaName
, object_name (s.[object_id]) as TableName
, I.name as IndexName
, ROUND (s.avg_fragmentation_in_percent,2) as [fragmentation%]
Into #TempFragmentation
From Sys.dm_db_index_physical_stats (db_id (), NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes i on s.[object_id] = i.[object_id]
and s.index_id = i.index_id
INNER JOIN sys.objects o on i.object_id = o.object_id
WHERE 1=2
EXEC sp_msforeachdb ' use [?];
INSERT into #TempFragmentation
SELECT TOP 20
Db_name () as Datbasename
, Schema_name (o.schema_id) as SchemaName
, object_name (s.[object_id]) as TableName
, I.name as IndexName
, ROUND (s.avg_fragmentation_in_percent,2) as [fragmentation%]
From Sys.dm_db_index_physical_stats (db_id (), NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes i on s.[object_id] = i.[object_id]
and s.index_id = i.index_id
INNER JOIN sys.objects o on i.object_id = o.object_id
WHERE s.database_id = db_id ()
And I.name is not NULL
and OBJECTPROPERTY (s.[object_id], "ismsshipped") = 0
ORDER by [Fragmentation%] DESC '
SELECT Top * from #TempFragmentation ORDER by [fragmentation%] DESC
DROP TABLE #TempFragmentation

The results are as follows:

This type of index requires rebuild, or it can severely slow down database performance

Find indexes that have not been used since the last SQL Server restart

SET TRANSACTION Isolation Level READ UNCOMMITTED
SELECT
Db_name () as Datbasename
, Schema_name (o.schema_id) as SchemaName
, object_name (i.object_id) as TableName
, I.name as IndexName
Into #TempNeverUsedIndexes
From sys.indexes I INNER joins sys.objects O on i.object_id = o.object_id
WHERE 1=2
EXEC sp_msforeachdb ' use [?];
INSERT into #TempNeverUsedIndexes
SELECT
Db_name () as Datbasename
, Schema_name (o.schema_id) as SchemaName
, object_name (i.object_id) as TableName
, I.name as IndexName
From sys.indexes I INNER joins sys.objects O on i.object_id = o.object_id
Left OUTER joins sys.dm_db_index_usage_stats S on s.object_id = i.object_id
and i.index_id = s.index_id
and database_id = db_id ()
WHERE objectproperty (o.object_id, "ismsshipped") = 0
And I.name is not NULL
And s.object_id is NULL '
SELECT * from #TempNeverUsedIndexes
ORDER by Datbasename, SchemaName, TableName, IndexName
DROP TABLE #TempNeverUsedIndexes

Results:

Such indexes should be treated with care, not generalize, to see what causes the problem

To view information about index statistics

SET TRANSACTION Isolation Level READ UNCOMMITTED
SELECT
Ss.name as SchemaName
, St.name as TableName
, S.name as IndexName
, Stats_date (S.id,s.indid) as ' Statistics last Updated '
, s.rowcnt as ' Row Count '
, s.rowmodctr as ' number of changes '
, Cast ((CAST (s.rowmodctr as DECIMAL (28,8))/cast (s.rowcnt as
DECIMAL (28,2)) * 100.0)
As DECIMAL (28,2)) as '% Rows Changed '
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
ORDER by SchemaName, TableName, IndexName

The results are as follows:

Because the query plan is based on statistical information, the selection of the index also depends on the statistics, so according to the statistics update the size of the database can be seen in the general situation, 20% of the automatic update for large tables is very slow.

About the index of the DMV