CODEWITHSUNDEEP
sql-serversql-server-2008
pesi
pesi

Reputation: 91

Capture SQL Server workload from production to play against test db

After looking into performance slowness on one of our production SQL Server databases. This is an OLTP databases that unfortunately is also having some heavy analytic queries from time to time and after investigation I would like to make some index changes. I also have a physical test server that is identical to the production where I could restore the production db for stress testing purpose.

Can someone recommend a tool or a method on how I could capture the workload on production for a day or two and then replay that on test system with and without my changes and then compare the difference.

Thanks in advance!

Upvotes: 2

Views: 2940

Answers (2)

anon
anon

Reputation:

You can also look at the Distributed Replay Utility, which you can get in SQL Server 2012 RC0 (or wait until RTM). In addition to being able to replay a trace, it does so more accurately than Profiler - e.g. it can try to synchronize your replay against SQL Server from multiple machines,like your actual workload probably happened (much more realistic than Profiler which can only do so from a single machine).

Upvotes: 5

Mithrandir
Mithrandir

Reputation: 25337

You can use the SQL Server Profiler to capture a workload. Have a look here.

Upvotes: 3

Related Questions