March 25, 2007 at 12:02 pm
My table has 4 indexes (1 clustered and 3 nonclustered- let say ncIndex1 - ncIndex3).
I am not sure how efficient is ncIndex3 for example.
From sys.dm_db_index_usage_stats I can see that this index is used (statistics is updated). But I failed to find query/SP that uses this index.
I tried using Profiler but did not get usefull info- 1) when I used index Scans event I’ve got Appl name but Textdata is always empty; 2) when I used RPC/batch Starting Event with pointed table name, indexid I did not get anything usefull again as all (at least what I checked) these queries/SPs did not used ncIndex3 (as I can see from Execution plan) but only table instead.
My question is: How can I catch query/SP that uses particular index on particular table?
Thanks
March 25, 2007 at 2:47 pm
What's the index? You can see if you have queries that are even capable of using it if you look at the first col in the index and the columns in the WHERE clause.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 25, 2007 at 3:19 pm
The following article has good info about index usage and procedure too...
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
MohammedU
Microsoft SQL Server MVP
March 25, 2007 at 4:44 pm
Thanks, gents, for your help.
Steve: this nonclustered index ncIndex3 first column is the same as clustered index (id). I found some cases where clustered index was used but can not catch ncIndex3 usage.
Mohammed: this is great article. But for index issues it refers to the same sys.dm_db_index_usage_st view I used to confirm that this ncIndex3 is used (index statistic data is changed). But how to find related queries there is nothing in it. But I'll double check.
Anyhow I still have no idea how to catch these query/SP. Thanks
March 26, 2007 at 9:23 am
Hi Yuri,
I had similar problem in a past, and here is how I solved it. However this method is not 100% guarantee that you will catch that SQL because it is based on idea that that SQL is still in object cache:
select
objtype,
text
from sys.dm_Exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where text like '%field_name%' -- put here your field name , not the index name
March 26, 2007 at 9:41 am
Thanks, Mark. Problem is that this nonclustered index is composite one with 3 columns and first column (id) is in clustered index (PK). Thanks
March 26, 2007 at 10:36 am
Yes, that's another reason why it's not 100% guarantee. However you can put
where text like '%c1%' or text like '%c2%' or text like '%c3%'
hope it will not return huge number os SQL statements, copy each of them into query editor, and manually run execution plan. Again, this is not the best method, but unless it returns large number of records, it works.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply