Occasional Sql Server 2019 slowness (upgraded from 2014) -- please help...
-
Hi everyone,
We recently upgraded our server to Sql Server 2019 (15.0.2125.1 (X64) on Windows Server 2019 Standard 10.0) from the previous Sql Server 2014. The server has 32GB of RAM with an 8- Core 2.10 GHz Intel Xeon chipset.
We have noticed periods of slowness in processing from time-to-time which we rarely experienced with 2014. When we investigated, we noticed available memory was very low on the box.
Just wondering has anyone experienced this before or does SQL Server 2019 handle memory differently in this regard? Any ideas/suggestions/insights or anything we can change easily that may improve processing?
Thanks in advance, J.
-
Lots of differences between 2014 and 2019. It's hard to say that it's going to be any one thing in particular. Your best bet is to do a bunch of data collection in order to understand where the memory is going, which processes are causing, and why. It could be as simple as some configuration changes, or it could be that changes to the engine internals are causing you to hit a regression of some kind. I honestly have no way to know with a ton more data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you Grant. I know this was a broad one. I will enable the query store (of course I'm assuming that it does not effect things too much) to gain more insight upon next occurrence., Many thanks, J
- jellybean wrote:
Thank you Grant. I know this was a broad one. I will enable the query store (of course I'm assuming that it does not effect things too much) to gain more insight upon next occurrence., Many thanks, J
Love the Query Store!
However, do be cautious. You can tweak what it captures to ensure it's not putting to much of a load on your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks I set it up on "auto" (which is the default setting for 2019) and "tested" it with a reasonably hefty query that I know. It caught it nicely with good info returned. So I will continue to monitor...Thanks for all. J.
-
Why did you leave it with version 15.0.2125.1 ? ( that is GDR plus a couple of security updates pior to CU1 !!! )
Current is
ref: "Microsoft SQL Server 2019 Builds"
There is a vast number of fixes being implemented over time, that may even solve the problems you see.
(maybe even without reverting the cardinality estimator)
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
-
As a suggestion, set the Compatibility level to SQL 2014 to see if the performance returns to pre-upgrade levels. If so, note the significant changes between 2014 and 2019 and your code (specifically cardinality).
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply