Wednesday, June 17, 2009

Replaying traces with SQL Profiler

One of the projects I've become involved with at work has been the virtualization of our regional databases. However, when we hooked up a copy of one of the databases in a virtual environment we noticed a huge performance degradation, mainly in regard to CPU usage. The only part of the server that was virtualized was the drive containing the OS. User databases were mainly placed on a SAN so the IO looked to be acceptable, at least based on the small workload we generated. We know that the servers were not comparable; the current, physical server has 16 CPUs, where the virtual server is limited by VMWare to 4. Since performance was much worse than expected, we decided to try and replay a workload from one of our production servers on a virtual server and compare the results.
We thought of a few different ways to do this, but we eventually decided on using the replay ability of SQL Profiler. So we captured a trace from production, set up a copy of the databases on a second physical server configured identically to the production server, then used the different options inside profiler for the replay. What we saw was surprising.

The original trace was run for an hour. Using the multithreading option on the test box, the replay took twice as long, and the CPU usage was at least 10% higher for the length of the replay. When we set the number of threads to 64, the replay took 5 hours, and the CPU usage was maybe 25% of the original trace.

So the next step is to replay a trace on the same server to see what the results are. I'll post more on this in a few days, after a few more test cases. I'll also include some of the actual numbers.

No comments: