Seldom Used Indexes

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: 7 Comments
Published on: March 20, 2012

On occasion you may ask yourself if there are any under used indexes in your database.  If not you, then possibly a manager or client.  Usually this comes up when evaluating for missing indexes or better indexes.

SQL Server provides a means to find the information we seek.  We can query the sys.dm_db_index_usage_stats DMV  to garner much of the information we want.  You can read about this DMV here.

The columns we want to take a look at are the seeks, scans and lookups columns.  In conjunction with that, we want to compare those columns to the updates related columns.

Here is an example query.

[codesyntax lang=”tsql”]

[/codesyntax]

In this query, I am looking at two main components for comparison.  I want to determine where there are more updates on an index than reads.  I also want to see only those indexes that have no reads.

Using a query like this can help you to narrow your search for under-performing indexes.  Indexes returned by this query tend to be more costly to maintain than the benefit they may be providing.  Starting with the indexes returned by this query, you can test and confirm the findings.

page 1 of 1

Calendar
March 2012
M T W T F S S
« Feb   Apr »
 1234
567891011
12131415161718
19202122232425
262728293031  

Welcome , today is Friday, July 19, 2019