Proving the usefulness of a specific index

I'm looking for a way to prove that some recently added indexes are positively impacting a production system.

I've collected some dmv statistics to show that the indexes are indeed being used (frequently) but need some way to prove that they are having a positive enough impact on the select queries, that any negative impact from the added update / delete time is offput.

I'm looking for any insight / advice on how to go about proving that these indexes have a positive impact.

You need to baseline a whole workload and measure before/after. With just the numbers above I have no idea what they mean. The questions I would have include:

• There were 124,000 updates but did they take 10 times longer than they used to?

• Did they block users?

• Is there more activity because people can do more because it's faster, or just a natural increase in number of queries?

Take a backup. Restore on a dev server. Replay a trace with and without the indexes, and compare (note that the absolute results may differ based on hardware/network etc., and you won't get true effects of concurrency, but the relative differences should be helpful). You can compare a variety of things:

• time to run the whole workload

• perfmon stuff throughout workload (e.g. it was faster, but did it peg CPU? it was slower, but did it use more CPU too?)

• snapshots of sys.dm_os_wait_stats before and after each run

• snapshots of sys.dm_exec_query_stats/sys.dm_exec_procedure_stats before and after each run

Make sure you run the workload a couple of times first to prime the cache (because your production system will very rarely be operating on a cold cache).

If you haven't taken a trace of a fairly representative workload, then that's what you need to take the time to do. I can't think of any "fast" ways to prove the impact of these indexes on your actual workload.

StackExchange Database Administrators Q#81191, answer score: 5