Post reply

Query that ran to use an Index

  • June 11, 2012 at 11:59 am

    #262264

    I know I can get when an Index was used last from sys.sm_db_index_usage_stats.

    But how can I figure out what query used that index , be it scan, seek or lookup ?

    I am working on an Index Consolidation Project and I want to be able to tell them they -- Hey we have similar types of Indexes here, which are redundant, but if we can drop one or even consolidate both of them into one -- the query which used to use the 1st Index now will use the 2nd index (consolidated one).

    I want to be able to prove that with hard facts.

    Thanks

  • June 11, 2012 at 12:08 pm

    #1499420

    Grizzly Bear (6/11/2012)

    I know I can get when an Index was used last from sys.sm_db_index_usage_stats.

    But how can I figure out what query used that index , be it scan, seek or lookup ?

    I am working on an Index Consolidation Project and I want to be able to tell them they -- Hey we have similar types of Indexes here, which are redundant, but if we can drop one or even consolidate both of them into one -- the query which used to use the 1st Index now will use the 2nd index (consolidated one).

    I want to be able to prove that with hard facts.

    Thanks

    I believe the only way is to show an actual execution plan.

    Jared
    CE - Microsoft

  • June 11, 2012 at 12:13 pm

    #1499422

    i've only done it the wat SQLKnowitAll describes it:

    1. run the query in SSMS, with Show Actual. Execution plan enabled.

    2.review the execution plan.

    3. change the indexes.

    4. run the query again in SSMS, again with Show Actual. Execution plan enabled.

    5. review the new execution plan and note any differences.

    also, be careful dropping indexes; the query you are looking at might not use those other indexes, but other queries might be.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 11, 2012 at 12:33 pm

    #1499432

    Grizzly Bear (6/11/2012)

    I know I can get when an Index was used last from sys.sm_db_index_usage_stats.

    But how can I figure out what query used that index , be it scan, seek or lookup ?

    Like this, providing the query's plans are still in cache.

    http://sqlskills.com/blogs/jonathan/post/Finding-what-queries-in-the-plan-cache-use-a-specific-index.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 11, 2012 at 12:38 pm

    #1499433

    Sorry, I should have been a little bit more explicit in my question

    I do not know which query uses which index to run the execution plan.

    I know there is

    Index1 on Table1 , built on Col1, Col2 and Col3.

    Index2 on Table1 built using Col1, Col2, Col4, Col6.

    According to sys.dm_db_index_usage_stats Index1 has been used 6000 times and Index2 has been used 23000 times.

    My thinking is if I can consolidate index1 and Index2 into Index3 and build it using Col1, Col2, Col3, Col4 and Col6 the queries that use index1 and index2 will now use index3.

    I do not know what queries use Index1 and Index2. All I know is they are being used.

    If I can find it out, I can drop the above index1 and Index2 and run the execution plan (and hopefully it uses Index3) and then I can have my point driven hard across the table -- that we can consolidate Index1 and Index2.

    PS: We have a crazy, Crazy, CRAZY Database System. Seriously, it does not fall under any regular categories of DB systems you have ever seen and I really mean. Our DBs would be a far outlier if stats were collected in terms on number of tables in each DB and number of Indexes on each table.:alien:

    But it is what it is, it is a successful business and nothing will change, unless I can prove it.

  • June 11, 2012 at 12:41 pm

    #1499436

    Thanks Gila.

    Will use this to dig it out.

  • June 11, 2012 at 12:42 pm

    #1499437

    Grizzly Bear (6/11/2012)

    Sorry, I should have been a little bit more explicit in my question

    I do not know which query uses which index to run the execution plan.

    I know there is

    Index1 on Table1 , built on Col1, Col2 and Col3.

    Index2 on Table1 built using Col1, Col2, Col4, Col6.

    According to sys.dm_db_index_usage_stats Index1 has been used 6000 times and Index2 has been used 23000 times.

    My thinking is if I can consolidate index1 and Index2 into Index3 and build it using Col1, Col2, Col3, Col4 and Col6 the queries that use index1 and index2 will now use index3.

    I do not know what queries use Index1 and Index2. All I know is they are being used.

    If I can find it out, I can drop the above index1 and Index2 and run the execution plan (and hopefully it uses Index3) and then I can have my point driven hard across the table -- that we can consolidate Index1 and Index2.

    PS: We have a crazy, Crazy, CRAZY Database System. Seriously, it does not fall under any regular categories of DB systems you have ever seen and I really mean. Our DBs would be a far outlier if stats were collected in terms on number of tables in each DB and number of Indexes on each table.:alien:

    But it is what it is, it is a successful business and nothing will change, unless I can prove it.

    Well, without knowing the query you would never know if index3 will be used for either of the queries... It might, but it might not... Not knowing your knowledge of indexes, I have to ask if you truely understand indexes. i.e. If you have an index on columna, and columnb... Will a qury filtering solely on columnb be able to use this index?

    Jared
    CE - Microsoft

  • June 11, 2012 at 12:51 pm

    #1499440

    SQLKnowItAll (6/11/2012)

    i.e. If you have an index on columna, and columnb... Will a qury filtering solely on columnb be able to use this index?

    Yes, actually it would, but that's not the example that the OP gave. (hint, you said 'able to use', not 'able to seek')

    The merging of the indexes that the OP did show may be perfectly acceptable, or it may have terrible effects on performance (more likely the former unless Col1 and Col2 have very few unique values). The only way to find out would be to test, and the blog post that I linked explains how to find what queries used what indexes (providing the plans are still in cache)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 11, 2012 at 12:55 pm

    #1499443

    GilaMonster (6/11/2012)

    SQLKnowItAll (6/11/2012)

    i.e. If you have an index on columna, and columnb... Will a qury filtering solely on columnb be able to use this index?

    Yes, actually it would, but that's not the example that the OP gave. (hint, you said 'able to use', not 'able to seek')

    The merging of the indexes that the OP did show may be perfectly acceptable, or it may have terrible effects on performance (more likely the former unless Col1 and Col2 have very few unique values). The only way to find out would be to test, and the blog post that I linked explains how to find what queries used what indexes (providing the plans are still in cache)

    Thanks for pointing that out to me. I have to be more precise with my wording if I am going to give proper examples.

    Jared
    CE - Microsoft

  • June 11, 2012 at 1:08 pm

    #1499451

    Thanks again Gila.

    Yes your blog post might make me break thru the boundary wall I was so looking for.

    I did a search myself -- did not find it. Was a little surprised.

    Should learn how to use Google? :hehe:

Viewing 10 posts - 1 through 9 (of 9 total)

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