DMV about Indexes

DMV about Indexes

1. View indexes that are updated but 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
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
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], ''isshipped '') = 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 20 * from # tempunusedindexes order by [user_updates] DESC
Drop table # tempunusedindexes

 

Result:

 

This type of index should be dropped

 

 

Index with 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
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
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], ''isshipped '') = 0
And (S. user_seeks + S. user_scans + S. user_lookups)> 0
Order by [maintenance cost] DESC'
Select top 20 * from # tempmaintenancecost order by [maintenance cost] DESC
Drop table # tempmaintenancecost

 

Result:

 

High maintenance cost should be dropped

 

Frequently Used Indexes

-- Frequent Indexing
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
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
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], ''isshipped '') = 0
Order by [usage] DESC'
Select top 20 * from # tempusage order by [usage] DESC
Drop table # tempusage

 

Result

 

This type of index requires special attention and should not be eliminated during optimization.

 

 

 

Index with the most fragments

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) 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) 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], ''isshipped '') = 0
Order by [fragmentation %] DESC'
Select top 20 * from # tempfragmentation order by [fragmentation %] DESC
Drop table # tempfragmentation

 

The result is as follows:

 

This type of index requires rebuild, otherwise it will seriously drag down the database performance

 

After the last SQL Server restart, you can find indexes that are not used at all.

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 join 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 join sys. Objects o on I. object_id = O. object_id
Left Outer Join 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

 

Result:

 

This type of index should be taken into account and cannot be generalized. It depends on the cause of this problem.

 

View 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 (S. rowmodctr as decimal (28, 8)/cast (S. rowcnt as
decimal (100.0) *)
As decimal () 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 result is as follows:

 

Because the query plan is based on the statistical information, the selection of indexes also depends on the statistical information. Therefore, the general situation of the database can be seen based on the number of statistics updated, automatic update of 20% is very slow for large tables.