5

Besides wasting space on the database, unnecessary indexes on SQL Server can slow down insert and update operations. Developers who lack experience in database principles sometimes tend to create table indexes which don't make sense for the running queries.

Is there a common procedure or tool for SQL Server 2005/2008 for analyzing the database workload and giving hints which indexes are either never used or not necessary on a certain operational database?

Thank you!

splattne
  • 28,348
  • 19
  • 97
  • 147

6 Answers6

5

I'm seeing a cute little T-SQL script here (http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/) to show unused indexes that should run on SQL Server 2005. Another one http://aspadvice.com/blogs/ssmith/archive/2008/03/31/Find-Unused-SQL-2005-Indexes-for-Current-DB.aspx here, too.

It looks like dm_db_index_usage_stats is the key to all of this. Pretty neat! (See http://msdn.microsoft.com/en-us/library/ms188755.aspx I'm gonna have to go look at some production databases I created now to see what these stats look like. (smile)

Edit: Some very nice addt'l background here: http://blogs.msdn.com/craigfr/archive/2008/10/30/what-is-the-difference-between-sys-dm-db-index-usage-stats-and-sys-dm-db-index-operational-stats.aspx

Evan Anderson
  • 141,071
  • 19
  • 191
  • 328
5

A few things to bear in mind about using sys.dm_db_index_usage_stats:

  1. The output only contains indexes that have been used since the last time the database was started. When a database is shutdown, all entries from the in-memory cache of information for that database are removed. Similarly, the cache does not survive an instant restart. There is no way to manually clear the entries for a particular database without restarting the database. The various articles referenced in the first answer (and also on my blog) describe how to capture the output at various times to do time-series analysis.
  2. Be sure to test your entire business cycle. You don't want to remove an index that's used for an end-of-month report or CEO query, even though it may be tempting to.
  3. Make sure you understand the various counts and what they mean before making a decision on whether the index is being profitably used, or simply maintained at expense.

Hope this helps.

PS One more thing for others reading this and wondering if there's an equivalent method for SQL Server 2000 - no, we (as I was on the team at the time) only added the capability for 2005 onwards.

Paul Randal
  • 7,184
  • 1
  • 35
  • 45
4

We've got a wiki article at SQLServerPedia with a script to do this, plus a tutorial video on how to use it:

One to find indexes not being used:

http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use

And another to find indexes you should add:

http://sqlserverpedia.com/wiki/Find_Missing_Indexes

Brent Ozar
  • 4,425
  • 17
  • 21
  • Up voted to counter the neg vote w/o reason. Plus video tutorials are always helpful. – SQLChicken Jun 04 '09 at 15:09
  • Thanks Brent! I'm reading this on my mobile phone, so I can't check out the video at this moment. Does this tutorial cover only how to find missing indexes or also unnecessary ones? – splattne Jun 04 '09 at 15:29
  • Doh! Sorry about that, I posted the wrong link. I added another link for the one you wanted. There's a tutorial on missing indexes and a tutorial on unneeded indexes, and I pasted the wrong one, hahaha. – Brent Ozar Jun 04 '09 at 16:07
2

try left joining onto select * from Sys.dm_db_index_usage_stats. You can see which have never been touched. Make sure they have been around long enough though, running this the day after a new index has been created may not be enough time....

Also be aware indexes may not be used today but as data size increases they are considered a better bet by the optimizer. Typically with small (new) tables the optimizer will always scan but will start to seek when the number of rows has risen above the tipping point

Dave J

Edit: Evan beat me to it, but he is spot on.

Edit 2: corrected advice, forgot the anti-join bit!

Dave Jackson
  • 31
  • 2
1

I did some index-related query and I've shown this in my blog (http://dbalink.wordpress.com/2008/11/09/how-to-finding-index-usage-stats-with-dmv-in-tsql/)

MarlonRibunal
  • 283
  • 1
  • 3
  • 8
0

Bear in mind that SQL Server does not know your business. It does not know what business tactical and strategic decisions your company has made, is making, and will make.

This means that an index missing today may not be relevant tomorrow or it may become more relevant. The same logic applies to indexes which are under utilized or not used at all.

When SQL Server RTM'd, I wrote my own scripts for perusing and reporting-on the DM tables and thought about automating their activity. One glance at a missing index report where SQL Server was asking for all remaining columns of a table to be added as "Include" columns on an index for a table with very dynamic content was the deterrent I needed to not automate the process.

I still use my scripts, but I apply my company's business decisions to what I do -- Something I can do in a smaller company.

As always, know your data, know your business, know your business' direction.

Robert Miller
  • 234
  • 1
  • 2