Replaying traces, Part 4

So far we’ve replayed the same workload on 3 virtual servers, each with 4 CPUs, and 3 physical servers, 2 with 4 CPUs and 1 with 16 CPUs. We also ran one with 8 CPUs but that replay appeared to hang at 99%.

All the physical replays showed the same trends. A 16 CPU server performed better than 4 CPUs, Hyper-threading off performed better than hyper-threading on. The average for IO and transactions/sec followed the same curve; the longer the replay time the lower the average.

But the virtual server replays showed a different trend. All of these completed in less time

than the 4 CPU tests on a physical server. And the averages for IO and transactions/sec were noticeably less. Since the workload was the same in each case we expected the averages to be consistent with the trend set by the physical since the total number of transactions and IO should have bee the same across the board.

I’m guessing that there were more deadlocks on the virtual than the physical. In all cases the CPU usage was the same, averaging higher than 80%. If deadlock victims are rolled back it would explain the decrease in total IO. But I don’t know if a rolled back transaction is still considered a transaction by the Windows counters. This is going to take some research.

We have some more tests scheduled using a 64 bit virtual server and one test using VMWare’s newest host that supports virtual servers running 8 CPUs. 

Technorati Tags: SQL,replays

SQL Server 2008 Training Kit is available

One of the things I like about Microsoft is the wealth of training they make available for their products. There was a training kit for ASP.NET, one for Visual Basic 2008, and now there’s one for SQL Server 2008. You can get yours at http://www.microsoft.com/downloadS/details.aspx?familyid=E9C68E1B-1E0E-4299-B498-6AB3CA72A6D7&displaylang=en

Replaying traces, Part 3

I had a few surprises when I began replaying a production workload on a virtual server. I'm using the same workload I captured from a production server and replayed on a physical server in different scenarios. The first virtual test was on a 32 bit server hosted on a VMWare box with CPU affinity turned on. A limitation of virtual servers (at least for now) is that they are limited to 4 CPUs.

The first surprise was that the replay was faster than the 4 CPU test on a physical server, 3:07 virtual compared to 3:40 physical. The second surprise is that the total number of transactions appeared to have dropped.

I'll be looking into this to see what I'm missing, and I'll post more details soon.

Replaying traces, Part 2

When I left off, I was discussing using SQL Profiler to replay a trace taken on a production server on different test boxes with different configurations. The end result is to see if we can virtualize our production OLTP database servers.

Our next step in testing was to test replaying a trace on the same server it was captured on. We couldn’t do this on a production server so we used our testing server and assigned our developers to hit the database with our application for one hour. Afterwards we restored the databases to the same state they were in and replayed the trace using the same options we use on the other server tests; using multi-threading, not displaying results, and not replaying server SPIDs. The replay of the trace took only 45 minutes. We probably didn’t capture a large enough workload. But we did rule out the overhead of running the replay when comparing to performance of the production server when the workload was captured.

Back to testing. Since we can’t exactly duplicate the replay of the workload to match the production server, we decided that running the replay on the test server would be our new baseline since we could duplicate that for every test. Testing on the physical box showed the following:

	Production	16 CPU (new baseline)	4 CPU, HT ON	4 CPU, HT OFF
Time	1:07	2:37	3:40	2:44
Avg CPU	17%	24%	83%	77%

We expected the 4 CPU tests to take longer and use more CPU. We were surprised that the 4 CPU test with hyper-threading turned off performed better than with hyper-threading on. So we ran one more test, using all physical processors and turning ht off. This gave us 8 physical processors to work with. This test, though, was incomplete. We ran it twice and both time the replay appeared to hang after completing 99%. Both times we needed to kill the replay after 5 hours. The counters we captured weren’t valid.

I’ll post on testing on a virtual server in a future post.

Technorati Tags: SQL,Trace replay

Testing posting with Live Writer and a proxy

I’m seeing if I can get through the firewall.

Using Live Writer

I’ve seen quite a few blogs about Windows Live Writer. Opinions seem to vary as to the usefulness of the tool. So I thought it was time I tried it out for myself.

This is the first post I’ve made with it. I’m just using basic word processing so it shouldn’t be too different than what I use now. That would be OneNote and Word, sometimes just entering right from blogger. But this looks to have most, if not all, the same capabilities. So we’ll see. 

Technorati Tags: Live Writer

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.