4

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.

IndexUsage

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

Improve this question
4
  • You need to baseline a whole workload and measure before/after. With just the numbers above I have no idea what they mean. 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?
    – Aaron Bertrand
    Commented Oct 27, 2014 at 15:36
  • @AaronBertrand So what you're saying is. Disable the indexes, baseline the queries over a prolonged period of time (IE a week). Then reenable them, do the same baseline and compare. Any chance I can get an answer that's more short term? (Limited time) Update: Oh and, thank you for your answer so far :)
    – Reaces
    Commented Oct 27, 2014 at 15:45
  • 1
    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. but relative should be ballpark). If you haven't taken a trace then that's what you need to take the time to do. I can't think of any "fast" ways to prove the impact on your actual workload.
    – Aaron Bertrand
    Commented Oct 27, 2014 at 15:47
  • @AaronBertrand Alright, I'll push up a request for some more time to give the proof. If you want to put your comments in an answer I can approve it so the question doesn't pop up as unanswered?
    – Reaces
    Commented Oct 27, 2014 at 15:49

1 Answer 1

Reset to default
5

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.