SQL 2022 performance
-
Hello,
Can’t understand why simple script which utilize CPU single core on SQL 2022 Express executes 2 times slower than on SQL 2016 Express, same on Standard edition.
Where is the catch?
On my local PC:
SQL 2016: ~12 seconds
SQL 2022: ~24 seconds
Script:
CREATE OR ALTER PROCEDURE #p AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 0, @time DATETIME2 = SYSUTCDATETIME();
WHILE @i < 30000000
BEGIN
SET @i = @i + 1
END;
SELECT cpu_time, DATEDIFF(MILLISECOND, @time, SYSUTCDATETIME()) elapsed_time
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
END;
GO
EXEC #p;
DROP PROC #p
-
First step, review the execution plan. Next step, I'd check server resources and server settings. If the query is slower, is the hardware identical? If not, that could be the problem.
If hardware is the same, are the databases on the same type of disk (SSD for example) between the systems?
Now, if all of that is the same, is the number of rows returned the same? Is there any blocking happening while the query is running?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
SQL2022 is known to not being the fasted of the versions.
Have a look at Brent's blog 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
-
Hi,
please check, if the statistics and index are on the same "level", maybe rebuild them before your test.
And, check the mdop and the threshold for parallelism, and everything else which may impact the duration of your query.
Best regards,
Andreas
-
Hi everyone, thanks for replying.
Both tests are run on the same SQL Server, it can also be laptop, as results are similar. So hardware is not the thing. As per databases, this test is running on temp, so it should be the same, as per numbers which is returned, it must be the same as procedure is just going through iteration. I have read Brents post, I see that main features are not ready, for now I am just trying to understand why the same t-sql is not the same in performance . As this proc is just running on tempdb there is not much of ix and statistics involved (?). Regarding execution plan, there also is not much going on. Please see file.
Parameters
cost threshold for parallelism: 50
max degree of parallelism:7
What do you think?
Regards, Jānis.
-
SQL 2022 will give you batch mode execution plans on indexes that are not columnstore, even if batch mode execution plans are slower.
Did you look at the execution plans yet?
-
So here are execution plans for both servers.
-
Hi,
Did you found what is happening ???
We have exact same issues after migrating to 2022.
So we used the same test and 2022 is on average 2.5 times slower.
BR
- CreateIndexNonclustered wrote:
SQL 2022 will give you batch mode execution plans on indexes that are not columnstore, even if batch mode execution plans are slower.
Did you look at the execution plans yet?
Good lord. That may be the issue we're having across the board. Just to ask the question to hopefully save some time, is there a setting name that you can provide so that I can research it and at least test turning it off if there is one? Or, are we simply doomed to suffer with this insanity?
--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 wrote:CreateIndexNonclustered wrote:
SQL 2022 will give you batch mode execution plans on indexes that are not columnstore, even if batch mode execution plans are slower.
Did you look at the execution plans yet?
Good lord. That may be the issue we're having across the board. Just to ask the question to hopefully save some time, is there a setting name that you can provide so that I can research it and at least test turning it off if there is one? Or, are we simply doomed to suffer with this insanity?
it can be disabled at DB level
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF
- Jeff Moden wrote:CreateIndexNonclustered wrote:
SQL 2022 will give you batch mode execution plans on indexes that are not columnstore, even if batch mode execution plans are slower.
Did you look at the execution plans yet?
Good lord. That may be the issue we're having across the board. Just to ask the question to hopefully save some time, is there a setting name that you can provide so that I can research it and at least test turning it off if there is one? Or, are we simply doomed to suffer with this insanity?
frederico_fonseca wrote:Jeff Moden wrote:CreateIndexNonclustered wrote:SQL 2022 will give you batch mode execution plans on indexes that are not columnstore, even if batch mode execution plans are slower.
Did you look at the execution plans yet?
Good lord. That may be the issue we're having across the board. Just to ask the question to hopefully save some time, is there a setting name that you can provide so that I can research it and at least test turning it off if there is one? Or, are we simply doomed to suffer with this insanity?
it can be disabled at DB level
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF
I actually didn't know it was something that could fixed, it was one of the issues with 2022 I found while planning my upgrade and even tested it practically using a demo of the problem that I found somewhere. We have pretty resoundingly confirmed we wont be adopting SQL 2022 and haven't followed it much since, but sometimes following Brent's post of all the things that are still broken is somewhat amusing sometimes.
-
Thank you both!
We had to upgrade because of "corporate policy". We were at 2016. 2017 doesn't have the horrible performance issues that 2019/2022 has. Even a super simple Tally function is more than 18% slower even when dumping to a "BitBucket" variable to take disk and display out of the picture for measurement.
This reminds me of the "Performance Improvement" in 2014 when they "upgraded" the Cardinality Estimator. We upgraded from 2012 to 2016 and performance tanked. Very fortunately, I knew the "fix" for that one.
Frederico... thanks for the link. I'm setting up some simple tests as we speak.
--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) -
Testing complete. Unfortunately, it made zero difference in performance.
--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) -
Heh... New tag line... SQL Server 2022... It just runs slower... A LOT SLOWER!
--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)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply