using THE LEAST USED SQL feature called Distributed Replay (DReplay)
There are a lot of things that can go wrong during SQL migration. One of them – random TSQL calls which become slower. And they do.
Here is an actual example where we tested two SQL Server configurations prior to SQL Upgrade and Migration. In this case, the tested settings did worse than old SQL Server. So SQL Distributed Replay is very valuable at catching high risk scenarios like these:
Why does SQL performance change after SQL Server migrations – even if new server has faster storage, more RAM, more CPU cores and better EVERYTHING?
Because changes in hardware and SQL version will force SQL Server chose 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 TSQL calls running faster on NEW, 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?
It could be that hardware or Win OS or both were setup with all best practices and proper settings on NEW server.
It could be that SQL Server was setup with all best practices on the NEW server.
It could be that you jumping few SQL Server versions up and simply SQL just got better.
Or it can be any combination of the above.
How much SQL Server performance improves 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 setup hardware, Windows and SQL for best performance – of course, and often old server wasn’t. Now, I know those best practices we do, improve performance, but I can’t take credit for full 30-40%.
Majority of improvement is because of SQL Server just got better. Better statistics, smarter code, better magic dust Microsoft has few buckets still off – which all make execution plans better, and that makes SQL go faster.
But its not all good.
During migrations, we see some T-SQL code becoming slower!
Wait… What???
Yes, some T-SQL calls become noticeably slower.
Random slowness is not something you want to find out after the migration. Finding out that important query used to take half the second and not it takes 30 seconds.
Ummmhhhh….. 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 existing server (I like to call OLD server).
And then re-run it on the NEW server.
Sounds simple, right?
Not exactly.
There are ton of steps involved.
And I am not going bore you with the steps here.
But I will show result you can expect.
The important thing is that you will be able to say things like:
“The NEW server is faster by 27.8% compared to OLD”.
Here are the queries that became faster on the NEW vs. OLD.
Here are the queries that became slower on the NEW vs. OLD.
And here is what I did to make slower queries to run faster. So, we don’t have to deal with performance emergencies after the migration to the new server is over. And we can all go to bed after migration window is over. And not look like a dummy that did not plan things right for the next week in the office. 😊
Need to eliminate SQL Server Migration risks for your server? First step – use someone that has done this many times.
We would love to help! Just hit us up using Contact Us form.
Comments