Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

closed as too localized by Mat, Mark Storey-Smith, dezso, Max Vernon, Paul White Jun 17 '13 at 20:25

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

9  
Your websites and services are "quite big" yet you are using a database "server" with 1 CPU and 4 GB of RAM? My laptops are far more powerful than that, and I would never use them to host "quite big" anything. –  Aaron Bertrand Jun 14 '13 at 12:54
12  
"This system is used by hundreds Search and Rescue teams..." - is this "for real"? If so step away from the dial and call an expert. And stop restarting SQL Server as a fix. Your restart doesn't fix anything, it just makes all the traffic go away. –  Mark Storey-Smith Jun 14 '13 at 12:58
2  
So what? Data never grows? Data never changes? Are you in on a constant sized data set? –  Marian Jun 14 '13 at 13:44
6  
I wouldn't expect a single, 2.3 GHz CPU to really change the performance profile of a SQL Server previously using a 2.1 GHz CPU. –  Aaron Bertrand Jun 14 '13 at 15:00
7  
@Levi and I'll tell you one last time: SOMETHING is causing that CPU activity, it's not just random boogie men. YOU need to investigate further when the CPU spikes again (and we've given you plenty of tools to do that), rather than just come up with wacky random ideas like CPU affinity and "clearing your CPU cache"... –  Aaron Bertrand Jun 14 '13 at 15:18

4 Answers 4

up vote 3 down vote accepted

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;
share|improve this answer
3  
Note that freeing the procedure cache could actually cause a huge spike in CPU - since all the queries will now have to recompile their execution plans. –  Aaron Bertrand Jun 14 '13 at 13:47
    
They are all set to 0. I have no possibility of doing this in dev environment, the server is always live. What could the negative impacts potentially be? –  Levi Johansen Jun 14 '13 at 14:12
    
You run the risk of the first time someone executes a query, they use values in the where clause that are out of the norm for that query. This creates a plan for that query that works well, but for a majority of the queries to come that use values that are in the norm this cached parametrized plan will perform poorly. –  Drew Leffelman Jun 14 '13 at 14:21
    
This actually sped up a few of the queries on the website. But I restarted the server earlier today, so I won't know if it'll still grow slow over time until tomorrow. –  Levi Johansen Jun 14 '13 at 14:39
1  
You have NO DEV ENVIRONMENT? What kind of honki ponki setup is that that has a business critical "has always to run" server but no separate dev system in a time of virtualization? You see me shocked. GET ONE. –  TomTom Jun 14 '13 at 15:23

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:

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

share|improve this answer
    
We are planning on making mirrored SQL server that will be used for the alarm service. But that might take some time: months, or even a year. –  Levi Johansen Jun 14 '13 at 14:05
    
What I fail to understand is why the CPU usage goes all the way up to 95%: it stays there when there is no activity, so it doesn't need to stay that high. It is clearly holding on to alot of stuff that makes every query slow because it need to clear out the oldest stuff for every new thing... right? This would happen no matter how powerful the CPU was, it would just take a longer time to reach that level, right? It would clear out faster probably, but still... –  Levi Johansen Jun 14 '13 at 14:07
2  
How have you proven that there is "no activity"? Have you actually tried to determine what is using CPU? SQL Server doesn't just jam up CPU for fun - it must be doing something (assuming you've properly identified that it is, in fact, SQL Server in the first place). –  Aaron Bertrand Jun 14 '13 at 14:17
    
We know when there is no one on the website, and we know when there are no alarms going. The server responsible for the alarm-service checks for alarms in the DB every other second, but this query is tiny. It is the SQL server, taskmgr says so, and restarting the SQL Service does free it all up. –  Levi Johansen Jun 14 '13 at 14:25
1  
Just because nobody is "on the web site" does not mean there is "no activity." When you have high CPU usage and "no one on the website" what does EXEC sp_who2; show? –  Aaron Bertrand Jun 14 '13 at 14:31

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.

share|improve this answer
    
I was talking to a dba friend this evening, regarding cursors and locks. He was telling me that cursors should be deallocated automatically at the end of an SP but that that doesn't necessarily happen. The first question is, do these SPs use cursors, if so, are the deallocated explicitly? –  Meredith Poor Jun 16 '13 at 2:20
    
The second question is, are there unreleased locks at the end of an SP execution? This can occur from a Begin Trans without a corresponding End Trans or Rollback. He was indicated that you can tell by counting the system PIDs (Process Ids), if these are building up over time then SQL Server has a memory leak. SSMS for 2005 allows DBAs to see the system PIDs, this isn't possible with 2008 or later. Therefore, to see them, it is necessary to install the SSMS for 2005 with appropriate patches. –  Meredith Poor Jun 16 '13 at 2:23

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

share|improve this answer
    
But this would not change the CPU-usage on the SQL-Server, right? It would just make the queries go faster on the website, and there could be problems is somethign is changed in a table while someone else is using memcached results from the same table, right? –  Levi Johansen Jun 14 '13 at 12:45
    
@Levi if you cache the query results somewhere out in the middle tier then the queries don't hit the database (except when you need to refresh the cache). –  Aaron Bertrand Jun 14 '13 at 13:46
1  
Off - totally. If the CPU is also high when noone is on the website, then quite obviously caching web level stuff would not help. Memcached is a great tool, but not a replacement for a borderline competent person to sit down and find out what the server is doing when it supposedly should be doing nothing. –  TomTom Jun 14 '13 at 15:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.