SQL Server 2017 High CPUHave a client with Windows SQL Express 2017. Every once in a while the thing goes awol, tops out the CPU and is slow to respond. This happens for a few hours then it settles down and doesn't happen for another four months or so. They are asking me why.
I'll tell you... I have no idea. They claim something is wrong with the server... I think a sql query is zombied and gone awry.
Here are my notes for the future...
Diagnostics
As for some diagnostics, this says it better than I can:
Just:
- -click FILE.
- -go to: C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Policies\DatabaseEngine\1033
- -select all the files.
- -click EVALUATE.
Multiple Instances
There might be multiple sql server versions running. Or instances running. We left the 2014 as a failsafe in case something went wrong with 2017, since we didn't know how it would react.
Upgrade Away from Sql 2014:
I still think there's a serious bug in 2014 that everyone's ignoring. Since sql-2016 and sql-2017 released, there's no reason to fix bug per se. As a fix, simply upgrade, kill off 2014 and move on.
You are probably fine with 2017 and are at a place where we can remove sql-2014.
Remove Any Unused Sql instance:
Or perhaps there's some type of process in the othe sql-instance that is set to run. If you are not using the other Sql instance, it is probably best to remove it so you can narrow down the number of variables.
Ram-memory:
Ram-memory is meant to be used. That's what it is for. So if it is at 100% there's no need to be alarmed. In a traditional physical system, once the ram-memory is used up, the cpu will access the hard drive as virtual-memory/swap-space.
In a virtual system, such as this system, more ram-memory is dynamically added as the system needs it. This is referred to as hot-add ram. And it will keep a 20% buffer.
While this is supported by the operating system of the database server (Windows Server 2012 Standard), what I'm finding out is that SQL-Express (and the SQL-Standard version btw) is unable to hot-add ram. As shown here (Hot add memory):
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-2017
Also, looking at the link above, it shows that SQL-Express has a max buffer pool/buffer-cache of 1410MB, so hot-adding ram wouldn't help.
Looking into the db, this is exactly what it is using now:
1429700 kb physical_memory_in_use
This system is set to start with 4GB of ram-memory. Adding the 1410MB memory from above will put the usage around 5400MB. Adding 20% buffer will assign 6480MB. Here is the recent screenshot of memory assignment looking similar to our calculations:
All of this to say that you can double the startup Ram to 8GB. According to the datapoints, this is overkill and unnecessary but you have the memory so we might as well try it.
If those 3 items don't work then perhaps we can get away with using the Developer version of sql on the system which doesn't have the limitations.
Bad Query
Lastly, if the CPU load is at 100% then something is topping it out. A bad query is going to consume all resources available no matter how much you have. Adding more resources to compensate for a bad query is a bad idea.
NOTES:
https://logicalread.com/windows-server-hyper-v-dynamic-memory-with-sql-server/
https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/