Has SQL Server Performance Improved?

Question

Has SQL Server Performance Improved?

Steve Jones - SSC Editor

SSC Guru

Points: 734459
More actions
April 1, 2023 at 12:00 am

#4166854

Comments posted to this topic are about the item Has SQL Server Performance Improved?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 1

It's been my expectation that SQL Server will run slower because of all the stuff they're doing to figure out if a plan should be reverted and a bunch of other things.

As the old science adage warns, "If you measure it, you change it".

To be honest, I'm scared to death of our impending migration from SQL Server 2016 to SQL Server 2022 because MS has tried to make things more "Gumby Proof" and that requires measuring the size of "Gumby's feet" much more often than a simple shoe change would require.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 2

Ok... I just did some extensive testing using Brent's code both as is and with some mods to give the test table a bit more bulk.

Using a MAXDOP of 1, like Brent did, the 2019 Compatibility Level ran using about 38% LESS CPU and Duration than 2016. (In other words, 2019 was FASTER)

Using a MAXDOP of 4, like my normal setup, the 2019 Compatibility Level ran using about 46% LESS CPU and Duration than 2016. (In other words, 2019 was FASTER)

My thought was, "What am I doing differently because both of those sets of tests indicate that 2019 isn't a problem with performance?"

... And then I reread the Brent's article and finally saw the "parenthetical fine print"...

(In this demo case, 2019 compat level actually works beautifully, dropping the CPU time down by about 1/3, and I wish the client’s case was that easy. They already tried that before they called me. Bummer.)

Maybe I'm missing something obvious but... IMHO, that strongly suggests that the real problem is actually "just" a yet-to-be-discovered problem in the actual client implementation (including but not limited to Windows or 3rd party software installed) or hardware or ??? and NOT an inherent problem with 2019.

I also did the same testing in the 2022 (160) Compatibility Level with similar results.

The good part of all this is that, much like the little girl on the "6th Sense" said after her gut wrenching expulsion of stomach fluids, "I feel much better now" about the impending migration from 2016 to 2022 that we're going to make in the next few months.

From Steve's Article:

"...you might document some queries (in addition to Brent's) and record the results. That might help you decide when you upgrade."

Well said... That's one of the many reasons why "Baselining" is so very important (and not just for SQL Server version upgrades).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Glenn Berry Newbie Points: 4 More actions · Answer 3

I tried duplicating Brent's results (using his exact code, unchanged) on two different machines that each had named instances of SQL Server 2016 SP3 and SQL Server 2019 CU19. My results (comparing the two versions) were basically identical, certainly not outside the margin of error.

If I changed the database compatibility level to 150 for the SQL Server 2019 instance, the MAXDOP 1 query results were MUCH faster (around 43% faster) on SQL Server 2019 compared to SQL Server 2016. This seems to echo Jeff's findings.

SQL Server 2019 has so many manageability improvements compared to SQL Server 2016 that make it much easier to work with as you do performance tuning on a daily basis. Pretty much all of the customers that I work with are on SQL Server 2016 or SQL Server 2019, so I deal with this every day.

I hate to see people get frightened about upgrading to a newer version of SQL Server by something like this.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 4

Welcome aboard, Glenn!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 5

Glad to see some additional testing. While I do think most people will run at the latest compat levels, I also know far too many people get hung up at previous levels, especially with upgrades. I haven't had the chance to test, mostly because I have to reinstall SQL 2016, which I just haven't made time to do.

I think the latest versions of the query processor are better, though for a specific query it's possible there will be issues.

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 6

By its very nature, some of the interleaved execution that we're getting with all the intelligent query processing is going to cause some queries to slow down as the execution pauses. Also, as Jeff says, measurements and calculations on those measurements add overhead.

HOWEVER

Based on tests I've run, and no, not enormous client machines, overall, yeah, performance in 2019 (and 2022) has improved. Everywhere? No. All the time? No. Regardless of circumstances? Of course not. And if you're running at the old compatibility levels, then you're only getting a very small subset of the increases in performance, so it's actually not that surprising that you can see worse performance on the newer version, than on the older version. After all, you're taking away improvements.

This feels like an edge case. A large one, and a troubling one, but not the norm.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

abair34 Ten Centuries Points: 1076 More actions · Answer 7

Interesting find!

I had instrumented the code below to run nightly benchmarks across a large farm of SQL Servers honestly to keep an eye on our infrastructure teams and any changes that they might make at the OS / VMWare level. But now that we have a few years and of historical data stored I stumbled on this article and looked back and yea I see the regression in performance results when our instances were swapped to new VMs running SQL 2019. I ended up testing on a single VM that hosts both 16 and 19 (or 13 and 15 to veterans) and sure enough the performance regression still exists.

We run fully virtual so I didn't get a chance to test it on bare metal. Initially I thought maybe lightweight query profiling changes or the CEIP x events might be messing with it because yeah SQL isn't made for tight loops. Unfortunately 2019 always seems to run the test about 300ms slower for me.

I think there is no denying with each release the engine has to handle more lines of code and more branch logic for backwards compatibility so I should expect the benchmark query that I run to get a little slower. Look at us now with two cardinality estimators, we play with countless hints, wonder if we should turn 4199 (hotfixes) on or off, losing sleep over letting intelligent query processing do its "magic":) None of that code comes for free.

Benchmark script if you want to try it yourself.

SET NOCOUNT ON;
DECLARE
@result numeric(16,6) = 0,
@start DATETIME,
@end DATETIME;

DECLARE @i int = 1;

SET @start = CURRENT_TIMESTAMP;

WHILE @i <= 3000000
BEGIN
SET @result = @result % 999999 + sqrt(@i);
SET @i = @i + 1;
END;
SET @end = CURRENT_TIMESTAMP;

SELECT DATEDIFF(MS, @start, @end) as execution_time_ms

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 8

My worst nightmare has come true. We upgraded to 2022 and stuff is crawling now. We turned off Query Store and went back to the old cardinality estimator and that didn't help. We changed to the 2016 Compatibility Level and things are mostly back to normal.

Thanks, Microsoft. I love you, too! 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 9

Jeff Moden wrote:

My worst nightmare has come true. We upgraded to 2022 and stuff is crawling now. We turned off Query Store and went back to the old cardinality estimator and that didn't help. We changed to the 2016 Compatibility Level and things are mostly back to normal.
Thanks, Microsoft. I love you, too! 🙁

This comfirms Brents post: Which Version of SQL Server Should You Use?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 10

Be interested to see what you diagnose. Did you upgrade in place? Index rebuilds or stats updates?

For whatever reason, it seems upgrades mess up perf when the data/indexes/stats should be the same.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 11

We did no index rebuilds were. The conditions travel with the restores. The same holds true with statistics. We do, however, rebuild stats once each week. It has not made a difference.

I'll also say that separate testing of some well known code also clearly demonstrates that SQL Server 2019 and 2022 have made some changes for the worse and neither example relies on indexes or stats. I owe Bob Ward an email about that code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 12

Capture some stuff on your side, esp. backups. I know Bob would want to repro if he can.

And if you want to write a rant on how this went and what you did to deal with it, be an interesting article.

Glenn Berry Newbie Points: 4 More actions · Answer 13

I don't know that Bob Ward is the best person to ping about this. Conor or Derek Wilson might be better resources.

I got similar results for the little loop benchmark across three named instances on the same machine. That is not reassuring, but you might argue that it is not a database workload at all.

SET NOCOUNT ON;

DECLARE @result numeric(16,6) = 0;

DECLARE @start DATETIME;

DECLARE @end DATETIME;

DECLARE @i int = 1;

SET @start = CURRENT_TIMESTAMP;

WHILE @i <= 3000000

BEGIN

SET @result = @result % 999999 + sqrt(@i);

SET @i = @i + 1;

END;

SET @end = CURRENT_TIMESTAMP;

SELECT DATEDIFF(MS, @start, @end) as execution_time_ms;

-- AMD Ryzen 9 5950X bare metal results

-- SQL Server 2022 - 2480ms, 2474ms, 2460ms

-- SQL Server 2019 - 1503ms, 1504ms, 1503ms

-- SQL Server 2016 - 1397ms, 1400ms, 1407ms

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 14

Glenn Berry wrote:

I don't know that Bob Ward is the best person to ping about this. Conor or Derek Wilson might be better resources.

Agreed but Bob Ward was they one that contacted me because of a suggestion from Grant Fritchey. I assume that he'll forward the email to the other two.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)