Index Seek

Hi Guys,

Do you have any script to scan the Tables and find out how many user seeks, user scan, user lookup on Each Individual Indexes?

That will be quite useful for me to find out which INDEX is unused or missing. Then I can drop the index which are unused.


more ▼

asked Feb 15, 2011 at 08:37 AM in Default

avatar image

1.6k 55 59 62

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

3 answers: sort voted first

Check out these DMVs if you want to build the query yourself: sys.dm_db_index_usage_stats, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details, sys.dm_db_index_physical_stats along with sys.indexes.`

But you can also use this scripts already out ther to check for missing indexes. Check out missing indexes script by Brent Ozar and also Glenn Berry's Diagnostic Information Queries and last two queries in this script

more ▼

answered Feb 15, 2011 at 09:19 AM

avatar image

4.9k 33 39 43

  • for linking to documentation. ;-)

Feb 15, 2011 at 09:46 AM ThomasRushton ♦♦

I have run SQL Server 2008 Superhero Queries from Glenn Berry and I am confused with the result set for Query 4 (Finding Bad Indexes) and 5 (Finding Missing Index).

Can you explain me what those 2 results means?


Feb 16, 2011 at 01:47 AM Leo

@Leo, Basically the idea of query no 4 is to identify bad non clustered indexes by comparing the number of writes with reads. If the write is much higher and the reads are very small or zero the index might be hurting your write/update performance and you might be better of dropping it. Query no 5 will help you identify missing indexed and you can see their impact if added. (Becareful though on adding all suggested indexes)

Glenn explained both these queries in great detail in this post http://sqlserverperformance.wordpress.com/2011/01/06/sql-server-index-tuning-for-mere-mortals/ .

Feb 21, 2011 at 08:03 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left

Check the DMVs for all your index statistics needs.

 select db_name(database_id), object_name(object_id, database_id), * 
 FROM sys.dm_db_index_usage_stats

should give you what you need in terms of current indexes.

Similarly, there's a DMV sys.dm_db_missing_index_details that may be of interest for those indexes that don't already exist.

more ▼

answered Feb 15, 2011 at 09:18 AM

avatar image

ThomasRushton ♦♦
42.2k 20 57 53

  • for the speed and the answer. I shouldn't have tried to put the links:-)

Feb 15, 2011 at 09:21 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left

To add to the great answers so far - never forget that the statistics can be misleading.

As soon as you make changes to a system the index usage stats can start to warp. If you create a new index, drop an existing index, add statistics (manually or automatically), import lots of data, delete lots of data or any other massive change to the system, the indexes may start to be used differently.

It is also important to remember that the usage stats are reset when a database has been taken offline and brought back online, or when the server instance has been rebooted. Also the stats are kept around indefinitely as long as an index is there - this means that a system that has been running for months/years could have massively different usage patterns throughout that time and the stats won't tell you that.

Take the stats as a help to identify possible candidates for being dropped, never take them as set-in-stone recommendations.

more ▼

answered Feb 15, 2011 at 10:50 AM

avatar image

26.2k 18 37 48

Amen Brother!!

Feb 15, 2011 at 11:04 AM DaniSQL


Feb 15, 2011 at 02:27 PM ThomasRushton ♦♦

Also, be careful about dropping unique indexes as the Query Optimizer can use their existence to eliminate unnecessary operations. For example, if you do SELECT DISTINCT ColumnA FROM TableB and there is a unique index on ColumnA, the Optimizer will ignore the DISTINCT bit to make a more efficient plan. This won't show up in the DMVs because no scans or seeks are needed - the index's metadata is enough.

Feb 21, 2011 at 08:47 AM David Wimbush

Yup. What that guy said. +1

Feb 21, 2011 at 11:42 PM 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: Feb 15, 2011 at 08:37 AM

Seen: 2400 times

Last Updated: Feb 15, 2011 at 08:37 AM

Copyright 2018 Redgate Software. Privacy Policy