x

Index Usage

Hello, Can anybody explain about the index usage. The scenario is lets say, I've 30 indexes on a table (CI and NCI), I want to find out the index which is used maximum and the index used minimum, so that I can drop the unused index on a table. I've tried DMV, but did not know what to look after.

Thanks in advance

more ▼

asked Apr 15, 2011 at 08:14 AM in Default

caravanpunk gravatar image

caravanpunk
21 1 1 1

You did good coming to this site. The information below is stellar. Good luck in your quest for index tuning. Once you master it, the sky is the limit for your career as a DBA.
Apr 15, 2011 at 09:58 AM BradleySQL
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Firstly, go and get this book - http://www.red-gate.com/our-company/about/book-store/dynamic-management-views - its free in pdf.

It takes you through the whole 9 yards of the dmvs not just index related ones.

You will end up using [dm_db_index_usage_stats] [dm_db_index_operational_stats] [dm_db_index_physical_stats] in various combinations. The usage stats shows which indexes are used for what. If seeks and scans are low and updates are high then it might be an index to drop.
more ▼

answered Apr 15, 2011 at 08:38 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

(comments are locked)
10|1200 characters needed characters left
The key is, you don't want to just know which indexes are "used," but how they're used. Looking at sys.dm_db_index_usage_stats will tell you if your indexes are getting scans or seeks. In general (and there are exceptions to this) you want to see seeks. But, you really have to evaluate not simple how many times an index is touched, scan or seek, but which procedures are calling them? You may have a query that's called once a day, but by the CEO, you might want to leave that index. All looking at the DMO will tell you is, if the index is used. You still have to do all the research around how, by what, when. Then, before you drop the indexes, you need to test your code to validate it's not negatively affected when you drop them. It's a huge job.
more ▼

answered Apr 15, 2011 at 10:44 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

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

You can start by checking out Brent Ozars [Index Tuning for SQL Server video][1] and scripts.

[1]: http://www.brentozar.com/sql-server-training-videos/index-tuning-for-sql-server/
more ▼

answered Apr 15, 2011 at 08:48 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

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

In addition to the other answers, I would like to recommend you should also to take a look at the singleton_lookup in the sys.dm_db_index_operational_stats. That will give you an hint if you should alter one or more indexes to avoid costly key lookups, by creating covering indexes or even change the clustered index.

But as you already know, it's a heavy investigation project, so you should go for low hanging fruits to prevent the worst performing queries.

You can try to find the worst performing queries by using sys.dm_exec_stats and get the cached execution plans from sys.dm_exec_query_plan where you actually can find the used indexes.
more ▼

answered Apr 17, 2011 at 11:14 PM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(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:

x64

asked: Apr 15, 2011 at 08:14 AM

Seen: 15283 times

Last Updated: Apr 15, 2011 at 08:14 AM