How to capture workload from production to replay on test?

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.

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

StackExchange Database Administrators Q#60721, answer score: 5