Comparing Performance of Two SQL Servers: Part 1

How does NEW SQL Server compare to an your OLD one?

Why? Why would you want to compare two SQL Servers?

1. You just paid $375,000 for a new server hardware. It would be nice to show to your boss that the new server is faster with all this new hardware you convinced him to purchase.
2. You are migrating SQL Server to the AWS. You have no idea how the NEW server will perform.
3. You are migrating to new hardware and upgrading to the latest SQL version. How will it perform?
4. You are simply making few config changes to SQL Server or VMWare or SAN and you need to be 100% sure how this will affect SQL performance. You need that across all workload. Not just for few queries you can test manually.
5. Test the new SQL Server capacity.

This is especially useful when you know a setting change, will hurt some queries, but it will help the others. This helps to answer that.

Question is simple: I want to compare OLD to NEW.

And when I say compare, I mean, I want it proven with numbers. “It feels faster”- won’t cut it here. We want solid numbers.

You have your OLD SQL Server. Will call this puppy: OLD, ServerA, or current.

You have a NEW SQL Server. Will call it: NEW or ServerB.

NEW SQL box can be anything and live anywhere

1. Newly purchased on-premises server.
2. New server in AWS or Azure or Rackspace or any boutique cloud provider.
3. New hardware with a new version of SQL Server.
4. Same everything, just some code or setting changes.

So OLD vs. NEW – are just concepts. In reality, they can be anything.

NEW encompasses whatever new things we are introducing: hardware, settings, code, etc.

I will use an example of migrating from OLD SQL Server hardware to NEW.

While migrating, will upgrade SQL Server versions too.

Will create this NEW server using all the best SQL Server practices known to man.

We going to go through a lot of testing here. So why would you not have everything set as the best, right?

To sum up, the changes I am introducing

1. New hardware
2. New SQL version
3. New settings

If you are working on a really important SQL Server, then you want to compare the performance of OLD vs. NEW.

How do you do this?

This is where SQL Distributed Replay feature comes in.

• One of the least known features in SQL Server.

• Why? Because to use Distributed Replay (or DReplay) properly takes a ton of setup to get one single answer.

• It is easy? Hell no!

• Is it super useful? Yup!

• Oh, and here is the exact result you going to end up with:

I will explain what these screenshots mean in Part 2.