Occasional memory issues with SQL Server 2019 (more so than 2014)

SQL Server Administration
1

Folks,

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 or anything we can change easily that may improve processing?

Thanks in advance, J.

2

(I edited your post to fix the formatting)

SQL Server shouldn't change that much from 2014 to 2019. Is CPU high while you see the issue? SQL Server should consume as much RAM as you allow.

You can use sp_whoisactive SQL Server Monitoring Stored Procedure by Adam Machanic to see what queries are running at that time.

1 Like
3

To the contrary, I think that both SQL Server 2019 and 2022 are veritable pigs when it comes to performance.

Also, in 2014, did you have the Legacy Cardinality Estimator enabled? If so, you'll need to enable it separately for every database that needs it.

Things like Query Store and a bunch of other "built in" stuff is also now enabled where it wasn't in 2017 or less.

Running 8 core with only 32GB means that you have much less that 4GB per core because you're not supposed to allocate all ram to SQL Server. You're supposed to save a fair bit for the operating system.

Also, did you make config changes to match your 2014 system? For example, did you change "MAXDOP" and other things where the defaults are usually troublesome?

There's a whole lot that changed in the basement starting at 2019... make sure you configured it correctly. I'd also suggest to buy some more ram and get it up to 128 GB to cover your 8 core.

4

Thanks for the suggestions Jeff. To be honest I'm not a SQL Admin person but a dev with some responsibilities for the server.

As I'm not familiar with the Legacy Cardinality Estimator and the MAXDOP, to be honest, so I will have to research these and see what they are set to on 2019 (the 2014 has been decommissioned so I can't compare) but I can dig out the values for 2019 box.

I would also like to raise the compatibility level of the databases from the current level of: 100 to the recommended 150. However, I would like to estimate the risk. I was told to check the sprocs for ansi warning or no count or something like that (as there is a risk here) that but did not fully understand the point. Would you know what the risk was related to?

Thanks, J.

5

Just getting back to you on the Legacy Cardinality Estimnation (CE) and max degree of parallelism (MAXDOP) on the 2019 box. As the 2014 has been decommisioned so unable to determin that...

2019 CE is currently reading zero for the value
2019 MAXDOP has 4 values: min is zero; max is 32767; config_value is 2; and run_value is 2