Sql-server – Using sys.dm_db_index_usage_stats for unhelpful or unused indexes

indexindex-tuningsql server

I'm trying to discover indexes which may be in place which are only adding cost with no value to our database. I've come across sys.dm_db_index_usage_stats and from MSSQL Tips the following:

sys.dm_db_index_usage_stats

This view gives you information about
overall access methods to your indexes. There are several columns
that are returned from this DMV, but here are some helpful columns
about index usage:

user_seeks – number of index seeks

user_scans- number of index scans

user_lookups – number of index lookups

user_updates – number of insert, update or delete operations

Does that mean if there is an index with very few / no seeks+scans+lookups but many updates, then the index is only costing us (have to update index when table is updated) but not gaining us anything?

Second question – Is the following query a decent way to discover this information?

SELECT DB_NAME([database_id]),
       OBJECT_NAME(dm_db_index_usage_stats.[object_id], [database_id]),
       TotalReads = (user_seeks + user_scans + user_lookups),
       Writes = user_updates,
       indexes.[name],
       *
FROM sys.dm_db_index_usage_stats
LEFT JOIN sys.indexes on dm_db_index_usage_stats.[object_id] = indexes.[object_id] and dm_db_index_usage_stats.[index_id] = indexes.[index_id]
ORDER BY (user_seeks + user_scans + user_lookups) ASC, user_updates DESC;

Best Answer

Just throwing this out there, because I remember the bad old days of having a different script for every index ailment you read about on the internet.

I co-author a free stored procedure called sp_BlitzIndex that will tell you about a whole bunch of stuff going on with your indexes all at once.

Some examples:

Aggressively locked indexes
Duplicates (based on keys)
Borderline duplicates (based on first key column)
Unused (with differentiation based on number of writes
High value missing indexes
HEAPs
And more!

The simplest example run is probably like this. Just change the database name.

EXEC sp_BlitzIndex @DatabaseName = 'StackOverflow', @Mode = 4

There are a ton more ways to run it, just check out the docs at the GitHub link.

Hope this helps!

Related Solutions

Sql-server – SQL Server 2008 R2 DMV Question

use dbx;
select foo
from db1.dbo.table
join db2.dbo.table on condition
where some_function();

This query consumed lots of CPU and requested a large memory grant. In which database? The information you want simply doesn't exist as a concept. As a human with insight knowledge and with hindsight benefit, you probably would be able to explain why 75% of CPU is due to db1 and 15% is due to db2. But ultimately you just can assign queries to a database. The fact that some (ok, most) queries are 100% contained inside a db does not mean that all query resources can be assigned deterministically to a db.

However, for practical means is relatively simple to automate exactly what you did in your post: inspect the plans and identify all physical access operators locations and use this info to assign the query resources to a DB.

with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select x.value(N'@NodeId',N'int') as NodeId
    , x.value(N'@PhysicalOp', N'sysname') as PhysicalOp
    , x.value(N'@LogicalOp', N'sysname') as LogicalOp
    , ox.value(N'@Database',N'sysname') as [Database]
    , ox.value(N'@Schema',N'sysname') as [Schema]
    , ox.value(N'@Table',N'sysname') as [Table]
    , ox.value(N'@Index',N'sysname') as [Index]
    , ox.value(N'@IndexKind',N'sysname') as [IndexKind]
    , x.value(N'@EstimateRows', N'float') as EstimateRows
    , x.value(N'@EstimateIO', N'float') as EstimateIO
    , x.value(N'@EstimateCPU', N'float') as EstimateCPU
    , x.value(N'@AvgRowSize', N'float') as AvgRowSize
    , x.value(N'@TableCardinality', N'float') as TableCardinality
    , x.value(N'@EstimatedTotalSubtreeCost', N'float') as EstimatedTotalSubtreeCost
    , x.value(N'@Parallel', N'tinyint') as DOP
    , x.value(N'@EstimateRebinds', N'float') as EstimateRebinds
    , x.value(N'@EstimateRewinds', N'float') as EstimateRewinds
    , st.*
    , pl.query_plan
from sys.dm_exec_query_stats as st
cross apply sys.dm_exec_query_plan (st.plan_handle) as pl
cross apply pl.query_plan.nodes('//RelOp[./*/Object/@Database]') as op(x)
cross apply op.x.nodes('./*/Object') as ob(ox)

Sql-server – Unused Indexes – Consider Primary Key Constraint Supporting Indexes

Primary Key and Unique constraints are enforced by a supporting index for performance and to enforce uniqueness. Just because the index is not used for searching does not mean it isn't being used to guarantee uniqueness of the key values.

Whether or not a table has a primary key constraint or any unique constraints is not a performance decision - it is a data integrity decision. The primary key gets a primary key constraint and the candidate keys get unique constraints.

Whether or not the primary key index is clustered or nonclustered, on the other hand, is a performance question. Doing a Binoogle search on that will find many hits, including https://stackoverflow.com/questions/2206541/index-view-how-to-choose-the-clustered-index.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 R2 DMV Question

Sql-server – Unused Indexes – Consider Primary Key Constraint Supporting Indexes

Related Question