How to Resolve 100% CPU Usage Problem In SQL Server
My MS SQL Server is using about 95% of the CPU-power.
After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how much it is used.
When it's over 80%, every query is extremely slow.
Our website is dealing with alot of big queries, so some of them takes 45-60 seconds. After a restart (CPU usage less than 80%), it takes 11-20 seconds for the same Query.
How can I fix this? I've read online that affinity masks can adjust the CPU usage, but the Affinity settings are disabled. I cannot change them. Is this because I only have 1 processor?
There are plenty of tricks to do with the queries themselves, but our websites and services are quite big, and there is simply too much to change.
Most of them are already pretty well optimized.
Solution:
This is a long shot, but you might want to take a look at your forced parametrization setting. If you are seeing a large number of query plans when performance is bad, your queries are not being cached the way you expect them to and the queries are taking a long time to scan through the cache to see if there is plan to already use. If clearing the cache solves this problem you might want to look into changing the forced parameterization setting. You can clear the cache using:
DBCC FREEPROCCACHE
You can check to see what the forced parametrization setting is if clearing the cache worked by:
SELECT name
, is_parameterization_forced
FROM sys.databases;
This is probably set to 0, the default. If they desire, you can set that to true by doing:
ALTER DATABASE [database_name] SET PARAMETERIZATION FORCED;
This should be done in a dev environment first and see if this negatively impacts the database in other ways. It can be reverted using:
ALTER DATABASE [database_name] SET PARAMETERIZATION SIMPLE;
For example, you notice the following values.
Cause
This issue occurs because of high spin lock contention in a highly concurrent system. In these operating systems, multiple CPU threads are competing for resources and spinning for an extended interval while they run in a loop periodically to determine whether the resource is available instead of immediately yielding. When multiple CPU threads keep spinning (in a while loop) for a resource instead of yielding, this causes high CPU usage that cause performance issues.
Solution
This problem was fixed in the following cumulative updates for SQL Server:
Cumulative Update 2 for SQL Server 2016
Note After you apply SQL Server 2016 Cumulative Update 2 (CU2), the SECURITY_CACHE and CMED_HASH_SET names are changed to LOCK_RW_SECURITY_CACHE and LOCK_RW_CMED_HASH_SET, respectively. After CU2 is applied, the values appear as follows.
My MS SQL Server is using about 95% of the CPU-power.
After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how much it is used.
When it's over 80%, every query is extremely slow.
Our website is dealing with alot of big queries, so some of them takes 45-60 seconds. After a restart (CPU usage less than 80%), it takes 11-20 seconds for the same Query.
How can I fix this? I've read online that affinity masks can adjust the CPU usage, but the Affinity settings are disabled. I cannot change them. Is this because I only have 1 processor?
There are plenty of tricks to do with the queries themselves, but our websites and services are quite big, and there is simply too much to change.
Most of them are already pretty well optimized.
Solution:
This is a long shot, but you might want to take a look at your forced parametrization setting. If you are seeing a large number of query plans when performance is bad, your queries are not being cached the way you expect them to and the queries are taking a long time to scan through the cache to see if there is plan to already use. If clearing the cache solves this problem you might want to look into changing the forced parameterization setting. You can clear the cache using:
DBCC FREEPROCCACHE
You can check to see what the forced parametrization setting is if clearing the cache worked by:
SELECT name
, is_parameterization_forced
FROM sys.databases;
This is probably set to 0, the default. If they desire, you can set that to true by doing:
ALTER DATABASE [database_name] SET PARAMETERIZATION FORCED;
This should be done in a dev environment first and see if this negatively impacts the database in other ways. It can be reverted using:
ALTER DATABASE [database_name] SET PARAMETERIZATION SIMPLE;
 |
| How to Resolve 100% CPU Usage Problem In SQL Server |
High CPU usage causes performance issues in SQL Server 2016
When you use either a new instance of Microsoft SQL Server 2016 or an instance of SQL Server 2016 that's upgraded from an earlier version of SQL Server, you experience performance issues because of high CPU usage. Additionally, if you query the sys.dm_os_spinlock_stats table, you notice a high value for the spins column (compared to other rows in the table) that corresponds to the SECURITY_CACHE (Security Cache) and CMED_HASH_SET (Metadata Cache).For example, you notice the following values.
Cause
This issue occurs because of high spin lock contention in a highly concurrent system. In these operating systems, multiple CPU threads are competing for resources and spinning for an extended interval while they run in a loop periodically to determine whether the resource is available instead of immediately yielding. When multiple CPU threads keep spinning (in a while loop) for a resource instead of yielding, this causes high CPU usage that cause performance issues.
Solution
This problem was fixed in the following cumulative updates for SQL Server:
Cumulative Update 2 for SQL Server 2016
Note After you apply SQL Server 2016 Cumulative Update 2 (CU2), the SECURITY_CACHE and CMED_HASH_SET names are changed to LOCK_RW_SECURITY_CACHE and LOCK_RW_CMED_HASH_SET, respectively. After CU2 is applied, the values appear as follows.
https://mytecbooks.blogspot.com/2018/04/how-to-resolve-100-cpu-usage-problem-in.html