SQL Server Performance

How to test Index Tuning Changes before implementing in Production?

Discussion in 'T-SQL Performance Tuning for Developers' started by ZLA, Jun 19, 2008.

  1. ZLA New Member

    I am working with SQL Profiler and the Index Tuning Wizard in order to improve the database performance for my client's web site. Before I implement any index recommendations, I would like to measure the performance improvement. My best guess at the moment is to start from a fresh production copy of the database and run some standard set of SQL statements against it before and after any index changes. If I have the profiler running during both tests, I can compare the two runs.
    Are there other, perhaps better, ways to do this? Thanks in advance.
    ZLA, Jun 19, 2008
    #2

  2. Luis Martin Moderator

    You can also do this:
    Set statistics io on
    You query before any new index.
    set statistics io off
    The same after new index. You will find different amount of read ahead before and after.
    Luis Martin, Jun 19, 2008
    #3

  3. ZLA New Member

    Thank you, Luis. That should give me what I need.
    ZLA, Jun 19, 2008
    #4

Share This Page