SQL Server Performance Tuning

How To Eliminate SQL Server Migrations Performance Degradation Risks With Distributed Replay (DReplay)

Updated
5 min read
Written by
Mark Varnas

Using the least used SQL feature: Distributed Replay (DReplay)

There are a lot of things that can go wrong during SQL Server migration.

One of them is random T-SQL calls, which become slower.

And they do.

Here is an actual example where we tested two SQL Server configurations before SQL Upgrade and Migration. In this case, the tested settings did worse than the old SQL Server.

So SQL Server Distributed Replay is very valuable at catching high-risk scenarios like the ones shown in the video below.

YouTube video

Why does SQL performance become worse after SQL Server migrations?

Why does it happen even if the NEW server has faster storage, more RAM, more CPU cores, and better everything?

It’s because changes in hardware and SQL version can lead SQL Server to choose different execution plans. And that “different execution plan”, can cause your T-SQL to run insanely slower.

Granted, that doesn’t happen to a lot of T-SQL calls.

But we see about 90% of T-SQL calls running faster on NEW server, and 10% going slower.

I AM LYING.

On large enterprise-level SQL Server, we see 20-40 SQL calls that will become A LOT slower.

Why does SQL performance improve without any code changes after migrating to the latest version of SQL?

  1. It could be that hardware or Win OS or both were set up with all best practices and proper settings on the NEW server.
  2. It could be that SQL Server was set up with all the best practices on the NEW server.
  3. It could be that you’re jumping up a few SQL Server versions and simply, SQL has just gotten better.
  4. Or it can be any combination of the above.

How much does SQL Server performance improve by jumping several versions?

When upgrading from SQL 2008 to SQL 2016-2017, we often see, SQL performance improving by about 30-40%.

Without changing a single line of code. We set up hardware, Windows, and SQL for best performance – of course, and often old server wasn’t.

Now, I know those best practices do, improve performance, but I can’t take credit for the full 30-40%.

The majority of the improvement is because SQL Server just got better.

Better statistics, smarter code, and better “magic dust” are areas where Microsoft still has a few tricks up its sleeve, all of which contribute to improved execution plans and, consequently, faster SQL performance.

But it’s not all good.

During migrations, we see some T-SQL code becoming slower!

Wait… What???

Yes, some T-SQL calls become noticeably slower.

Discovering random slowness is not something you want to experience after migrating. It’s particularly troubling to find out that an important query that used to take half a second now takes 30 seconds.

Ummh, not cool, buddy. OK?! Not on my critical DB systems.

You probably thinking, “I need to know what may run slower”, right?

Absolutely.

Then the question is:

How do you know prior to migration what SQL will run slower?

The answer: Distributed Replay (or DReplay).

Which must be one of the least used SQL features under the sun.

How can Distributed Replay help SQL Server migrations?

DReplay allows to capture YOUR (notice YOUR) production workload from the existing server (I like to call OLD server). And then re-run it on the NEW server.

Sounds simple, right? Not exactly.

There are a ton of steps involved. And I am not going to bore you with the steps here. But I will show the result you can expect.

The important thing is that you will be able to say things like:

  1. The NEW server is faster by 27.8% compared to OLD.
  2. Here are the queries that became faster on the NEW vs. OLD.
  3. Here are the queries that became slower on the NEW vs. OLD.

So this is how I make slower queries run faster.

And we don’t have to deal with performance emergencies after the migration to the NEW server is over. We can all go to bed after the migration window is over. And not look like a dummy who did not plan things right for the next week in the office. 😊

Resources:

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials