5

I have a dedicated windows 2008 r2 server at the moment and I am thinking about migrating to a virtual server because of the costs. Now I know that SQL should be on a dedicated server, but the flexibility and costs are a great advantage for our situation. I would like to compare both servers. I know it is hard because one is a production server with actual load and the other is a test server with one user.

Both run Microsoft SQL Server 2008. dedicated has 12 GB ram, virtual has 12 GB ram dedicated has a dual quad core CPU and virtual has 4 CPU's

both have the exact same database (I restored a backup from production on the virtual test)

I executed a heavy select query with a heavy view and made the Microsoft SQL Server Management Studio include the Client statistics. That displays the Total execution time

Somehow the virtual shows a zero there now (since we added 2 virtual CPU's) but in the bottom right of the management studio it shows that the virtual server took 3 seconds and the dedicated server(production) took 2 seconds for the query to complete.

How can I make a good comparison between the virtual and production?

Edit: I have been told that the paravirtual setting in VMWare makes it better to compare the performance of the two instances.

Improve this question
4
  • 3
    Your comments to both answers are expanding the scope of your question a little wide. We're heading in the direction of describing how to measure the performance of a server, a broad topic which does not fit this sites Q&A format particularly well. A good starter for you would be Brent Ozar's SQL Server Perfmon Counter Best Practices. Following that, if you have any specific queries please feel free to post a new question.
    – Mark Storey-Smith
    Commented Nov 24, 2011 at 21:14
  • 1
    It looks like you've had two expert answers to the question. If you still want to do it the 'easy' way (ie the way that will give you either a false sense of security or put you off needlessly), then you have probably come to the wrong site for advice - but I hope you'll take the advice you've been given and/or consider asking your boss to get a professional DBA on the case (you have a SAN and VMware so you are big enough to want to do things properly, aren't you?) Drop into chat to discuss more if you aren't convinced?
    – Jack Douglas
    Commented Nov 24, 2011 at 22:42
  • Keep in mind that the rules that govern the physical SQL Server also governs the virtual. That means that just allocating cpu, memory, disk and network is not enough. In an virtualized environment the resources are shared and a 4 core virtual cpu (or a 4 single core cpus) have to wait for all 4 cores to be free before executing (a common mistake in virtualized environments). Depending on the architecture on the hardware below, memory can also be a problem if it's a numa architecture where you might be using 2 cores on each numa node, memory speed can also be a bottleneck. The disk might be pla
    – Anders Gregersen
    Commented Dec 1, 2011 at 8:43
  • "Compare Microsoft SQL server performance between virtual and dedicated server" Edit: I have been told that the paravirtual setting in VMWare makes it better to compare the performance of the two instances. Was anybody able to test this?
    – user8416
    Commented Apr 25, 2012 at 16:25

2 Answers 2

Reset to default
11

Testing isolated queries isn't going to help you make this decision. As you have an existing database live, use it to capture a representative workload.

  • Capture a profiler trace from your live, dedicated server
  • Use the RML toolset to create a replay file
  • Execute against the virtual server using ORCA

I posted a brief summary of the RML tools in an earlier question which you may find useful. To compare results across both runs, capture a trace of the first trace being run against the virtual server and run both through ClearTrace.

Improve this answer
3
  • This definitely sounds like a good test. But is there an easier way? How can I compare results of a query run on both servers. because client statistics show a zero on one server execution time and 2000 on the other. while the other took a second less to complete the query
    – JP Hellemons
    Commented Nov 24, 2011 at 11:44
  • 1
    If you want to compare a workload on two servers, there is no easier way. Individual query statistics will tell you little of value.
    – Mark Storey-Smith
    Commented Nov 24, 2011 at 11:49
  • ok, but client statistics tell me nothing, should I use a heavier query then? Dedicated server has the 3gb switch, should I use it also on virtual?
    – JP Hellemons
    Commented Nov 24, 2011 at 12:00
5

The only completely fair way to compare both servers would be:

  • Get a second dedicated server with identical specs to your production server, or stop using the production server for production.
  • Create a test bench
  • Run the test bench on the dedicated and virtual servers for comparison

Now, step 1 might be costly (but hey, if you can rent the server for just a month, it might not be as expensive as you think), and step 2 is always very complicated!

Improve this answer
5
  • Hi Alex, thanks for your reply. But I was actually hoping to get a bit of advice to create a test for an identical sql server database.
    – JP Hellemons
    Commented Nov 24, 2011 at 11:39
  • Ow forgot to mention that I want to get the virtual server as close as it gets to the dedicated one. So that's why we added 2 cpu's and switched from 8gb ram to 12 gb ram. the ram addition did not affect the execution time in the client statistics.
    – JP Hellemons
    Commented Nov 24, 2011 at 11:45
  • 1
    VCPUs are troublesome- some virt platforms show decreased performance with multiple VCPUs (although this might be dated information). Which kind of virtual server are you using? The big problem with performance on most virtual servers is that you are sharing a physical box with other virtual servers and IO and CPU time are shared- some other virtual server might hinder you, depending on how the virtualization has been setup. IO performance between your dedicated and virtual box might be pretty different, too (are both using direct-attached storage? SAN?... etc.).
    – alex
    Commented Nov 24, 2011 at 13:54
  • It is vmware and has san drives. how can i measure i/o performance? (might belong on serverfault sorry!)
    – JP Hellemons
    Commented Nov 24, 2011 at 14:37
  • 1
    IO performance in your case can be nicely determined by using the SQLIO tool. Don't mistake it for SQLIOSIM tool, which is mostly testing the validity of your setup, not its performance. You can see nice info regarding the subject on SQLServerPedia and on MSDN.
    – Marian
    Commented Nov 24, 2011 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.