I want to perform performance testing on my test server.
How can I capture workload from production and replay on my test server?
Basically I'm looking for detailed steps so that I can replay the workload from production to my test environment.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You can record the production workload with SQL Server Profiler and replay it against the test environment. There might be a bottleneck because the Profiler only replays from one computer. If you need more load from several computers (and have SQL Server 2012) you could use Distributed Replay (as long as you don't run into the same bugs with TVPs in traces as I've run into).
Note: the distributed replay requirement for SQL 2012 is for the replay client, you can use a 2008 trace file and target a 2008 instance if you want. See Distributed replay requirements