Query that uses table index

Question

Query that uses table index

Yuri55

SSCrazy Eights

Points: 8449
More actions
March 25, 2007 at 12:02 pm

#171806

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

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.

MohammedU SSCoach Points: 19644 More actions · Answer 2

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

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 3

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

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 4

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

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 5

Thanks, Mark. Problem is that this nonclustered index is composite one with 3 columns and first column (id) is in clustered index (PK). Thanks

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 6

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.