SQL Server 2005 DMV for indexes

In SQL 2005 there are # of system views to view information related to indexes, i am wondering if there is any system dmv that stores the usage of index, information like how often a index is used and when a index is last used or if there are any indexes in a database that are never used before?

more ▼

asked Mar 27, 2010 at 11:34 PM in Default

avatar image

86 5 5 9

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

There are loads and I think the way to use them is pretty much beyond the scope of an answer here. I have listed some of the most useful in most cases below. I would suggest you search for blog articles or BOL for explanations of how they can be combined to provide information on how your indexes are being used and how you might adjust them to improve your server and database performance.

Existing index data is stored in:

information regarding indexes that might be created to give a potential performance improvement: dm_db_missing_index_columns

more ▼

answered Mar 28, 2010 at 05:52 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

Yup. But you should also learn about when indexes that don't seem to be used actually could be.

Mar 28, 2010 at 06:23 AM Rob Farley
(comments are locked)
10|1200 characters needed characters left

Here's an excellent example of how to use the missing indexes DMVs, and one for unused indexes too.

There are lots more available on SSC too.

more ▼

answered Mar 28, 2010 at 09:50 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

thanks for finding some links, I have some on my office PC, not here at home...

Mar 28, 2010 at 11:21 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 27, 2010 at 11:34 PM

Seen: 2038 times

Last Updated: Mar 28, 2010 at 04:12 PM

Copyright 2018 Redgate Software. Privacy Policy