Post reply

Testing whether to drop an index

  • September 28, 2011 at 6:24 am

    #257989

    Hi all

    I am wanting to test whether my system is faster when I have dropped a particular index. I am thinking in order to test I should run procs that reference that table before and after the drop and compare the results.

    Is there any other things I should be taking into consideration?

    Thanks

  • September 28, 2011 at 7:03 am

    #1387721

    Every other operations on that table.

    This implies a much more complicated setup unless you have some unit tests ready.

  • September 28, 2011 at 7:05 am

    #1387725

    Just check the execution plans on the queries to see what, if anything, changes when the index goes away. It's not a measure of performance, but it's always good to know what's going on.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 28, 2011 at 7:11 am

    #1387736

    Grant Fritchey (9/28/2011)

    Just check the execution plans on the queries to see what, if anything, changes when the index goes away. It's not a measure of performance, but it's always good to know what's going on.

    Agreed, but he sais SYSTEM. So that implies that all the queries touching that tables need to be looked at as a group (thinking trace replay here).

    Then you know the real impact.

  • September 28, 2011 at 8:24 am

    #1387803

    Ninja's_RGR'us (9/28/2011)

    Grant Fritchey (9/28/2011)

    Just check the execution plans on the queries to see what, if anything, changes when the index goes away. It's not a measure of performance, but it's always good to know what's going on.

    Agreed, but he sais SYSTEM. So that implies that all the queries touching that tables need to be looked at as a group (thinking trace replay here).

    Then you know the real impact.

    Absolutely. I meant in addition to what you're saying.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 28, 2011 at 9:30 am

    #1387852

    Ok so my plan would be to trace the workload against that particular table. Then on my test system replay that and view the results, then drop then index and replay it again and compare. Is that the correct thing to do?

    Thanks

  • September 28, 2011 at 9:57 am

    #1387891

    Kwisatz78 (9/28/2011)

    Ok so my plan would be to trace the workload against that particular table. Then on my test system replay that and view the results, then drop then index and replay it again and compare. Is that the correct thing to do?

    Thanks

    Sounds like a good plan, yes.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 28, 2011 at 10:10 am

    #1387909

    Hi,

    Take a look at Jonathon Kehayias' blog post, which might help you out.

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

    Thanks, Andrew

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

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