question

rudrapbiswas avatar image
rudrapbiswas asked

Queries using indices

We have many indices which seem to be used a few times (seek > 0 but say less than 1000). Many of these indices are bad and I want to drop them but I am afraid that there is some badly written query that is relying on this badly defined index. How do I trap those queries that are using these indices?
indexes
1 comment
10 |1200

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

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Jonathan Kehayias has a script on his blog, which will find query plans using a specific index. http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/
10 |1200

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

Cyborg avatar image
Cyborg answered
Before dropping those indexes, few points should be noted. sys.dm_db_operational_Stats data get cleared when you had a server restart, disable then enabled the index or when a drop and create happened like wise sys.dm_db_index_usage_Stats also has similar limitations, so if you are good with above, then the next is. There may be few queries that are seldom used, like annual report or monthly reports your indexes may be used by these queries but their stats may be minimal in DMV, so you should getaway with those. The better way to filter them is to find the queries or SP that are runs less frequently (you may sit with your developer to find those) then the indexes used in that query by analyzing its execution plans and document them. There is no easy way to find which indexes queries relationship without analyzing its execution plans, its not that easy to remove less used index, disabling\dropping the indexes may results in poor performance for some queries. One approach is to find the procedure stats and the query stats from sys.dm_exec_procedure_stats and Query_Stats find out the execution_count and last_execution_time column, this gives you an idea about how frequent is the query\procedure called and when was it lastly executed(Again the catch is if sp is altered then its details may get cleared from cache), then analyze those execution plans to find the indexes used and then document them. You can also export these dmv data to some table by using jobs daily for future references and to be safe from restarts and sp modifications. You may need to repeat this steps for few months, so when your list is ready disable the index and monitor and compare the sps\queries performance(using dmv data collected earlier)for few months if you are good then you can drop the index else you may need to recheck.
10 |1200

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

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.