question

GPO avatar image
GPO asked

How can I find out which SQL is hitting an index?

I'm using SYS.DM_DB_INDEX_USAGE_STATS and SYS.INDEXES to get an idea of index usage. The table in question was not optimally designed and we have to live with that. It has about 120 columns and 10 million rows. It currently has 18 indexes, some of which INCLUDE up to 20 columns (not counting the columns used for SEEKing). A few indexes are rarely used relative to the others. Before I get rid of them though I want to be sure that I'm not going to cause some report that only runs once a week, to go from a minute to say an hour to run. In other words just because an index is relatively rarely used, doesn't mean that it's not doing an important job when it is used. I would be able to work out which dependencies are using the index if I could capture the SQL that is being executed. I've had a quick look at the profiler and I can't seem to see any settings that return index names. The sys.dm_db_missing_index* DMOs don't seem to really be what I want. It's not so much a case of not enough indexes as working out whether we have some that are expendable by minor tweaks to other indexes or mods to the affected SQL.
indexingtrace
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.
1 Like 1 ·
DenisT avatar image
DenisT answered
If you have a good plan cache, you can adopt Jon's idea of [Finding Key Lookups inside the Plan Cache][1]. Ideally, I'd take snapshots of the plan cache (frequency should depend on your workload) to persist to a table for further analysis. As far as the SQL Trace goes, you'll have to capture the execution plan and look inside of them for the index references -- could be very expensive! Also, you could take snapshots on index_usage_stats to analyze the patterns, sometimes it helps to look at this DMV over time. Very good question! [1]: http://www.sqlskills.com/blogs/jonathan/finding-key-lookups-inside-the-plan-cache/
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 answered
Capturing the full execution plans from either trace events or extended events is very expensive. I wouldn't recommend it without some pretty severe filters in place, and even then I'd only recommend using extended events. That said, I'd probably use extended events to capture the query call metrics. Then, where possible, conflate that with information from the cache and the plans there. It's never going to be perfect, but you should be able to at least identify if queries are running slower over time.
4 comments
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 ·
Nope. No extended events unless you're on 2008 or better. Trace events can do it, but filtering on trace events are done after the event is captured. And capturing execution plans is extremely costly, so you'll be putting massive load on the system. I don't recommend it.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
I don't have a precise measure. What I do have is the one time I turned it on the lights dimmed and my phone started ringing. I haven't turned it on a production box since then. The primary issue is that you can't filter trace events. Oh, you can, but the way it works is it captures every event of type x with the columns you specify, then it decides if it needs to keep the event it captured or not and throws away the thing that has been sitting in memory. So really big objects are moved into memory, flushing other stuff out, even though you don't actually need them. It's pretty bad, but no, sorry, no actual measure.
1 Like 1 ·
GPO avatar image GPO commented ·
I should have said the table is on SQL 2005 server. Does that mean extended events are still an option?
0 Likes 0 ·
GPO avatar image GPO commented ·
(Sorry, I'm getting slightly off topic here) I've heard enough people say that "capturing execution plans in a trace is expensive" for me to happily accept it as gospel... but it would be good to be able to quantify just how much extra work it causes, and how that work is manifested (CPU, i/o etc).
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.