question

Leo avatar image
Leo asked

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. Thanks.
sql-server-2008sqlperformanceindexingindexes
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image
DaniSQL answered
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][1] and also Glenn Berry's [Diagnostic Information Queries][2] and [last two queries in this script][3] [1]: http://sqlserverpedia.com/wiki/Find_Missing_Indexes [2]: http://sqlserverperformance.wordpress.com/2011/02/02/february-2011-version-of-sql-server-2005-and-2008-diagnostic-information-queries/ [3]: http://sqlserverperformance.wordpress.com/2011/02/04/five-dmv-queries-that-will-make-you-a-superhero-in-2011/
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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/ .
2 Likes 2 ·
+1 for linking to documentation. ;-)
1 Like 1 ·
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? Thanks
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for the speed and the answer. I shouldn't have tried to put the links:-)
1 Like 1 ·
WilliamD avatar image
WilliamD answered
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.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Amen Brother!!
1 Like 1 ·
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.
1 Like 1 ·
Yup. What that guy said. +1
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.