[Guide] How to compare performance of two SQL Servers

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

1. We just paid $75,000 for a new server and I need to prove to someone higher up that new server at least a bit faster with all this new and fancy hardware I convinced him to purchase.
2. We are finally migrating SQL Server to the AWS cloud and we have no idea how the NEW server will perform.
3. We migrating to new hardware AND we also upgrading to the latest SQL version. Because when will you get a better time?
4. This is not a SQL migration. We staying where we are. We simply deploying a change to SQL Server or VMWare settings or making a code change and we need to know, will SQL be slower or faster. Overall. Not just for five queries we 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 best SQL Server practices known to man.

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

To sum up changes I am introducing are:

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

If you working on really really import SQL Server, then you want to compare 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 exact result you going to end up with:

DReplay – the most important column

I will explain what these screenshot mean in Part2.