Has SQL Server Performance Improved?
- 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.
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
p.s. One of my examples is the very commonly used Set Based version of that. Yeah... I know that 2022 has GENERATE_SERIES() now but people shouldn't have to find all the inline code and change it to continue with good performance. There's also the issue with GENERATE_SERIES() defeating "Minimal Logging" for those that have that need for speed and low resource usage.
Of course, that's not the only thing that has slowed down... and I mean by a lot for the other code in our systems. It's just difficult to isolate the exact parts that have changed and are causing the issues. The automatic query adjusting junk certainly wasn't helping there. Since we've necessarily had to demote the systems to working in the 2016 Compatibility Level, it's bloody well impossible now.
My mantra continues to be confirmed with every new version and, sometimes, CU's (like they did with SP's)...
"Change is inevitable... change for the better is not!"
It's never a surprise but it's always surprising that it's no surprise.
--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) -
I would say that it would be more useful (for Microsoft and the overall community) to provide some more specific detail about what sort of code or query patterns have slowed down for you with SQL Server 2022 compared to SQL Server 2016.
From what I can gather from your previous posts, going back to database compatibility level 130 has helped performance for you more than anything else with your specific workload.
Are you comparing SQL Server 2016 on legacy hardware to SQL Server 2022 on different hardware?
If you have a reproducible workload, testing on named instances (SQL Server 2016 and SQL Server 2022) on the same VM or bare metal machine would be very useful, since it would eliminate many possible variables.
- Glenn Berry wrote:
I would say that it would be more useful (for Microsoft and the overall community) to provide some more specific detail about what sort of code or query patterns have slowed down for you with SQL Server 2022 compared to SQL Server 2016.
From what I can gather from your previous posts, going back to database compatibility level 130 has helped performance for you more than anything else with your specific workload.
Are you comparing SQL Server 2016 on legacy hardware to SQL Server 2022 on different hardware?
If you have a reproducible workload, testing on named instances (SQL Server 2016 and SQL Server 2022) on the same VM or bare metal machine would be very useful, since it would eliminate many possible variables.
No. Same hardware. In fact, same server with just the Compatibility Level change.
Of course, there are some things that I've been testing on my laptop that don't fair so well under those same conditions but if I run them in 2017, which is on the same server, they run much better. Kind of like the test you did.
So, it's a mixed bag. Lowering the Compatibility Level on the 2022 production box to 2016 has helped to stem the bleeding but it's still bleeding.
--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) -
Just so people know, we have a "Morning Jobs Report" that we have extensive history from. It includes the start DATETIME for each job and the duration. It runs religiously at 0800 on our main prod box. We no longer have the 2016 servers to try to do runs against but we can compare history.
The "Morning Jobs Report" on the new box shows a couple of dozen runs that are supposed to complete by 0800 but they don't. The report doesn't show the duration because they didn't finish by 0800. We can pull that data from the jobs info in MSDB. That will help us identify which jobs (i.e. workload) have been affected enen in the 2016 Comparability Level.
We had to wait a bit to do all of this so make sure we had enough data to compare with and make sure we weren't dealing with flukes.
--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) -
Interesting -- I saw similar results as well with your query. However, on an example using one of our actual database queries, SQL Server 2022 was much faster (either in COMPAT 150 or 160 mode).
--200K row table
select [State],COUNT(*) FROM BigTable
GROUP BY [State] ORDER BY 2 DESC
OPTION (MAXDOP 4)
--SQL Server 2022, COMPAT 160: 14 seconds
--SQL Server 2019, COMPAT 150: 38 seconds -
Interesting -- I saw similar results as well with your query. However, on an example using one of our actual database queries, SQL Server 2022 was much faster (either in COMPAT 150 or 160 mode).
--200K row table
select [State],COUNT(*) FROM BigTable
GROUP BY [State] ORDER BY 2 DESC
OPTION (MAXDOP 4)
--SQL Server 2022, COMPAT 160: 14 seconds
--SQL Server 2019, COMPAT 150: 38 seconds
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply