x

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

Varun gravatar image

Varun
86 4 5 7

(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:
dm_db_index_operational_stats
dm_db_index_physical_stats
dm_db_index_usage_stats

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

more ▼

answered Mar 28, 2010 at 05:52 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

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
excellent point Rob. I should have mentioned that too, my only possible excuse is that we just lost our daylight savings hour and I've only had one coffee so far today!
Mar 28, 2010 at 06:41 AM Fatherjack ♦♦
+1 Very informative response.
Mar 28, 2010 at 01:00 PM CirqueDeSQLeil
(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

Kev Riley gravatar image

Kev Riley ♦♦
54k 47 49 76

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x84
x22
x10

asked: Mar 27, 2010 at 11:34 PM

Seen: 1827 times

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