Index Changes in Production and baseline

  • Hi,

    We are adding 4-5 indexes to one database and dropping 2 unused indexes. I don't have proper testing environment. How to monitor these indexes changes? Do we need to run any baseline but we don't get load all the time same load all the days

  • It really depends on your transaction load, but you have a couple of options. Use Extended Events and capture all the RPC Completed and SQL Batch Completed events for one or two days. That's going to generate a lot of information that you have to be ready to manage. The other option is to capture an hour or two of data at several different times over a couple of days. That will generate a lot less data, but won't be quite as accurate. Then, after you make your structural changes, you do the same thing again and compare the results.

    The real question is, how do you know, if you haven't tested anything, that you're doing the right thing for your servers? Testing blindly on a production environment is absolutely the wrong way to do this. You should be able to set up a test environment even if, all it has, is a copy of the structure and a copy of the statistics so that it simulates your production environment.

    "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

  • I have one environment but I can't able to produce the load. How the profiler replay works for this kind of situation?

    Capturing the transactions 1-2 hrs from production and replay in test env with index and by dropping the index. Is it works in 2008r2

  • ramana3327 (7/2/2015)


    I have one environment but I can't able to produce the load. How the profiler replay works for this kind of situation?

    Capturing the transactions 1-2 hrs from production and replay in test env with index and by dropping the index. Is it works in 2008r2

    You're saying that you have a test environment? Whether your replay the load or not, you can at least test your queries prior to modifying the indexes in production.

    Yes, you can use trace instead of extended events (although I prefer the latter).

    You can connect to 2008R2 from distributed replay, but, the install, the management, all require you have a 2012 set up as well. If you can't even get a test server, setting up all this is going to be too much. Don't try to boil the ocean. Start small. Set up a test server where you can just validate your scripts and your index changes before you get to production. Then, set up a way to capture information so that you can measure before and after you apply the indexes.

    "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

  • Yes. I have development environment I can create Index and run the select statement to find the query improved or not but database load will be quite different (No insert or updates @same time).

  • It would be helpful if there were a way to alter an index such that it would still be maintained by DML operations but ignored by SQL queries. That way you could determine the impact of dropping the index yet still have the option to quickly recover by removing the "ignore" option.

    Disabling a non-clustered index will drop the index leaf and root pages and retain only the meta-data. To re-enable the index, you must rebuild it, so it's essentially the same as dropping the index, and this can be problematic in production.

    Perhaps there is a query hint to ignore specific index(s). If so, then you could capture some of SQL queries you suspect would be negatively impacted by the absensce of the indexes, and then run them in production with the required hints. Unfortunately, I don't see a documented query hint that would do what we need.

    https://msdn.microsoft.com/en-us/library/ms181714(v=sql.105).aspx

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Hey Eric, no query hint I'm aware of that will tell the optimizer to ignore an index.

    Ramana, so your concern then is not whether or not an index is positively or negatively affecting a given SELECT query because you tested it, but, to what degree it will affect INSERT & UPDATE queries? Well, test them too. Measure an insert before and after you create this index. Measure an update before and after you create this index. You can pretty quickly extrapolate the amount of load that you'll see if there are 100 inserts or 1000 or 10000000. It won't be a perfect and exact measure, but it should give you an adequate understanding.

    "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

  • Not only will the *seek, *scan, and *lookup columns in sys.dm_db_index_usage_stats give hints about what indexes are used by queries, but it also contains *update columns indicating the number of writes occurring for inserts, updates, and deletes.

    Also you can use SET STATISTICS IO ON to measure the number of writes performed without and then with the index in place.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell (7/8/2015)


    Not only will the *seek, *scan, and *lookup columns in sys.dm_db_index_usage_stats give hints about what indexes are used by queries, but it also contains *update columns indicating the number of writes occurring for inserts, updates, and deletes.

    Also you can use SET STATISTICS IO ON to measure the number of writes performed without and then with the index in place.

    I'm pretty cautious about using SET STATISTICS IO ON these days. I've seen a few instances of it skewing my results. I prefer to capture the metrics using extended events. They're more accurate.

    "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

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

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