5

We are on SQL Server 2008 R2 Standard Edition. Some tables are highly fragmented. I want to see if defragmentation will improve performance and is worth the effort and tables/indexes being locked during the process. Therefore I want to restore the full backup on a test environment and simulate a live environment.
What is the best way to follow? How can I capture the events going on in a live environment for some period? What tools are available for this?
Thank you

3
  • It's a helpful thread indeed but is Profiler the only way to go? Aren't there any other tools tailored just for these kind of tasks? Dec 7, 2016 at 11:03
  • Profiler is tailored just for that task
    – Tom V
    Dec 7, 2016 at 11:56
  • 3
    @Stackoverflowuser, the replay trace to capture the activity should be run as a server-side SQL Trace. Running it from Profiler directly can crash a busy active production server. There is some learning curve to replay it with Distributed Replay but it is the right tool for the job.
    – Dan Guzman
    Dec 7, 2016 at 12:44

1 Answer 1

4

You have a few options. There are third party tools that can help simulate a load against your database. There are open simple utilities like SQL Query Stress which can run whatever queries you supply it in whatever frequency you wish.

There is also the combination of running server side traces and replaying those traces. This can be as simple as gathering scripts from a trace (as Dan rightly pointed out above don't use the Profiler GUI to capture events, but capture the trace to a file using server side trace) and then replaying the workload if you more care about individual query performance if you care more about individual query performance and less about concurrency.

If you need to dig into concurrency and see the workload in real time from multiple angles - you would want to look at using the Distributed Replay features. This allows you to point to a trace in any version of SQL Server back to SQL Server 2005 and run it against any version of SQL Server, also back to SQL Server 2005. Jonathan Kehayias has a step by step walk through of how to setup Distributed Replay. This would allow you to configure the replay from multiple clients distributing the load - a single profiler replay would never allow you to scale. (Please note - you'll see the tool is a SQL Server 2012 tool. Don't let that scare you, the tool came out then, but can deal with your SQL Server 2008 R2 environment)

All this said - there is complexity to this either way. Taking a trace for replay has some requirements and steps. Replaying it also has some steps to take to make sure you are starting from the same starting point, etc. This complexity is worth understanding, it can help perform upgrade readiness tests, help understand hardware changes on total application, etc. You could use the RML utilities to analyze the before and after traces also which can help show you how things improved or didn't after.

If you just want to see if rebuilding indexes made any changes, you can do simpler tests also. You could take a large repeatable insert and run only that before and after. You could take a few queries with high scans and a few with high seeks and run that before and after. I would make sure you still rebuild statistics either way, though, since rebuilding your indexes does rebuild the index level statistics and that sometimes gives more of a benefit than rebuilding indexes and people attribute the benefit to the index rebuilds rather than the index statistics that got rebuilt. I tend to fall into the "rebuild" camp more often than not, but hardware changes in IO performance, workload patterns, etc all make that not a hard and fast rule necessarily for me.

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