High CPU usage on SQL server - Slow queries [closed]

Question

Our 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.

---

I cannot keep restarting the SQL-Service, even though it only takes 2 seconds, because we have an alarm service that allows people to call in and record a message, a selected group will then be called and hear the recorded message.

This system is used by hundreds Search and Rescue teams, and if the SQL-Service restarts during an alarm, it will terminate and the person that called it in will not be notified.

---

I have searched all over the place, but found nothing except for stuff about "Affinity Masks", which I cannot change.

There must be a way to clear out the CPU cache, without terminating current queries... right?

---

SQL: Microsoft SQL Server 11.0.2100.60
OS: Windows Server 2012 x64
Processor: 2.30 GHz
RAM: 4.00 GB

Answer 1

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;

Answer 2

Affinity does not "adjust the CPU usage" (e.g. in your case make the CPUs perform less work), it allows you to either turn off a CPU (perhaps to make it available to another instance on the same machine) or set a CPU to aid with I/O only. Even if you had multiple CPUs, you wouldn't be able to use the former to help with your goal, and it is impossible for us to guess on the latter because we don't know what is driving your CPU usage so high. It could be due to extremely poor indexing, excessive compilations, abundance of scalar UDFs, I/O thrashing, who knows? (And the reason I/O could be the cause is that if your database is larger than 3 GB or so, it will constantly have to swap data in and out of buffer pool memory, and this takes its toll on CPU.)

CPU cache, also, is a rabbit hole you don't need to be going down. I highly doubt your CPU is thrashing at 95% because of problems with your CPU cache.

To help narrow down the source of CPU pressure, and assuming you're using stored procedures, you can take a look at this diagnostic query from Glenn Berry (sourced from here) - make sure you run it in the context of the right database:

-- Top Cached SPs By Total Worker time (SQL Server 2012). 
-- Worker time relates to CPU cost  (Query 44) (SP Worker Time)

SELECT TOP (25) 
  p.name AS [SP Name], 
  qs.total_worker_time AS [TotalWorkerTime], 
  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
  qs.execution_count, 
  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) 
    AS [Calls/Second],
  qs.total_elapsed_time, 
  qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
  qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure

If you're not using stored procedures, then this example from John Samson can help isolate ad hoc queries (sourced from here):

SELECT TOP (25)
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
    qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
    (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
    qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

You can also take a look at Adam Machanic's sp_WhoIsActive, a stored procedure that can quickly analyze all of the currently running queries, and allow you to sort it however you want (e.g. in your case @sort_order = '[CPU] DESC').

The first thing I would do, though - particularly if this really is mission critical for search and rescue teams - is buy better hardware. You should have more CPUs and more RAM to service your application. You also absolutely need better high availability (e.g. clustering, mirroring or Availability Groups). There is no reason that a reboot of a physical machine should take your application completely offline - we have better solutions for that problem. And finally, I presume this "server" only has one spinny disk drive. This means that all I/O - from the OS, from SQL Server data files, log files, tempdb, etc. all go through a single controller and share read/write activity on a single drive. Get more disks. Get SSDs if/where you can. Use RAID and try to spread the I/O out as much as possible.

That all said, throwing hardware at the problem is not going to be the only part of the fix. You need to isolate exactly what is causing excessive CPU usage and then attack those problems no matter what hardware you're on.

Also see this StackOverflow question for some other ideas:

https://stackoverflow.com/questions/945063/how-do-i-find-out-what-is-hammering-my-sql-server

Answer 3

The following suggestions are a 'shot in the dark' because I can't see actual code.

First is that an SP might be opening cursors and leaving them open. Read up on Cursors, particularly Close and Deallocate. Someone might be closing, but not deallocating cursors. Behavior might have changed due to the upgrade, 2012 might treat leftover cursors differently from 2008 R2.

Second is that there may be table locks that don't get cleared. Again, I'm at a distance so I can't tell, but it would suggest that someone creates a global temp table after a 'begin transaction', and either no 'end transaction' is executed or the stored procedure fails leaving a locked table occupying space in tempdb.

Are you using WinLink by any chance? Something about this sounds vaguely familiar.

Answer 4

You should have a caching mechanism in place like memcached to improve the performance