8

I'm trying to troubleshoot some intermittent CPU spikes that we're witnessing on one of our production SQL Servers. We're running SQL Server 2008 R2 Standard Edition with 28 GB RAM and 4 CPU cores. When this happens, we are noticing a large number of RESOURCE_SEMAPHORE_QUERY_COMPILER waits, which lasts for about a minute or two and then stops, which then the CPU usage returns to normal.

After researching this, I understand that this is normally caused by compiling lots of non-reusable execution plans, which we are currently working on changes to our application to address.

Can this behavior also be triggered by plan cache evictions due to memory pressure? If so, how would I check for this? I'm trying to see if there are any short-term remedies we can do, like upgrading the server RAM, until we deploy our application fixes. The only other short-term option I can think of is to move some of our busiest databases to different server.

6

I believe you will see this symptom if you have a LOT of large query plans that are fighting for memory in order to compile (this has very little to do with running the query itself). To hit this, I suspect you are using an ORM or some kind of application that generates many unique but relatively complex queries. SQL Server could be under memory pressure because of things like large query operations, but on further thought it is more likely just that your system is configured with far less memory than it needs (either there is never enough memory to satisfy all of the queries you're trying to compile, or there are other processes on the box that are stealing memory from SQL Server).

You can take a look at what SQL Server is configured with using:

EXEC sp_configure 'max server memory';    -- max configured in MB

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN
  (
    'Total Server Memory (KB)',    -- max currently granted
    'Target Server Memory (KB)'    -- how much SQL Server wished it had
  );

You can identify the cached plans that required the most compile memory with the following Jonathan Kehayias query, adapted slightly:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (10) CompileTime_ms, CompileCPU_ms, CompileMemory_KB,
  qs.execution_count,
  qs.total_elapsed_time/1000.0 AS duration_ms,
  qs.total_worker_time/1000.0 as cputime_ms,
  (qs.total_elapsed_time/qs.execution_count)/1000.0 AS avg_duration_ms,
  (qs.total_worker_time/qs.execution_count)/1000.0 AS avg_cputime_ms,
  qs.max_elapsed_time/1000.0 AS max_duration_ms,
  qs.max_worker_time/1000.0 AS max_cputime_ms,
  SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
    (CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
     END - qs.statement_start_offset) / 2 + 1) AS StmtText,
  query_hash, query_plan_hash
FROM
(
  SELECT 
    c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') AS QueryHash,
    c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)') AS QueryPlanHash,
    c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,
    c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,
    c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,
    qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)
) AS tab
JOIN sys.dm_exec_query_stats AS qs ON tab.QueryHash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY CompileMemory_KB DESC
OPTION (RECOMPILE, MAXDOP 1);

You can see how the plan cache is being used with the following:

SELECT objtype, cacheobjtype,
    AVG(size_in_bytes*1.0)/1024.0/1024.0,
    MAX(size_in_bytes)/1024.0/1024.0,
    SUM(size_in_bytes)/1024.0/1024.0,
    COUNT(*)
FROM sys.dm_exec_cached_plans
GROUP BY GROUPING SETS ((),(objtype, cacheobjtype))
ORDER BY objtype, cacheobjtype;

When you are experiencing high semaphore waits, check to see if these query results vary significantly from during "normal" activity:

SELECT resource_semaphore_id, -- 0 = regular, 1 = "small query"
  pool_id,
  available_memory_kb,
  total_memory_kb,
  target_memory_kb
FROM sys.dm_exec_query_resource_semaphores;

SELECT StmtText = SUBSTRING(st.[text], (qs.statement_start_offset / 2) + 1,
        (CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
         END - qs.statement_start_offset) / 2 + 1),
  r.start_time, r.[status], DB_NAME(r.database_id), r.wait_type, 
  r.last_wait_type, r.total_elapsed_time, r.granted_query_memory,
  m.requested_memory_kb, m.granted_memory_kb, m.required_memory_kb,
  m.used_memory_kb
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_query_stats AS qs
ON r.plan_handle = qs.plan_handle
INNER JOIN sys.dm_exec_query_memory_grants AS m
ON r.request_id = m.request_id
AND r.plan_handle = m.plan_handle
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) AS st;

And you may also want to look and see how memory is distributed:

DBCC MEMORYSTATUS;

And there is some good information here about why you might be seeing a high number of compiles/recompiles (which will contribute to that wait):

http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

http://technet.microsoft.com/en-us/library/cc293620.aspx

You can check for high compile/recompile counts using the following counters:

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN 
  (
    'SQL Compilations/sec',
    'SQL Re-Compilations/sec'
  );

And you can check for internal memory pressure leading to evictions - non-zero counters here would indicate that something not good is going on with the plan cache:

SELECT * FROM sys.dm_os_memory_cache_clock_hands 
  WHERE [type] IN (N'CACHESTORE_SQLCP', N'CACHESTORE_OBJCP');

NOTE Most of these metrics don't have a magic "oh my gosh I need to panic or do something!" threshold. What you need to do is to take measurements during normal system activity, and determine where these thresholds are for your hardware, configuration and workload. When you panic do something is when two conditions are true:

  1. the metrics vary significantly from normal values; and,
  2. there is actually a performance problem occurring (like your CPU spikes) - but only if they are actually interfering with anything. Other than seeing the CPUs spike, are you seeing any other symptom? In other words, is the spike the symptom, or is the spike causing other symptoms? Would users of the system ever notice? A lot of people always go after their highest wait consumer, simply because it's the highest. Something is always going to be the highest wait consumer - you have to know that it's varying enough from normal activity that it indicates a problem or some significant change.

Optimize for ad hoc workloads is a great setting for 99% of the workloads out there, but it will not be very helpful in reducing compilation costs - it is aimed at reducing plan cache bloat by preventing a single-use plan from storing the whole plan until it's been executed twice. Even when you only store the stub in the plan cache, you still have to compile the full plan for the execution of the query. Perhaps what @Kahn meant to recommend was setting the database level parameterization to forced, which will potentially provide better plan re-use (but it really depends on how unique all of these high-cost queries are).

Also some good information in this white paper about plan caching and compilation.

  • We currently have the Optimize for ad hoc workloads set, though, as you mentioned, it's not really relevant to this particular issue. We do have code that generates a lot of unique queries, some from an ORM tool, some hand coded. As far as I know, the CPU spikes are not occurring long enough for our users to notice. Setting the database to forced parameterization sounds dangerous to me. – DanM Apr 17 '14 at 14:53
  • One question - when you check for high compilations, what actually constitutes a high number? I thought compilations/sec was only meaningful when compared to the number of batch requests/sec. – DanM Apr 17 '14 at 15:09
  • @DanM like I said above, there is no way for me to know what might be high for your environment, because I have no idea what is normal for your environment. If the number is close to or higher than the number of batch requests/sec, that may be an indicator, but again it depends. For example, if your batches consist of 5000 statements and 10 of them require recompiles (since this can happen at statement level), comp/sec is going to be 10x compared to batch/sec. Is that a problem? – Aaron Bertrand Apr 17 '14 at 15:15
  • @DanM Also you should take any recommendation to change a database or global setting with an implied disclaimer that it is something you should test and not just enable because someone on the Internet said to do so. :-) I try to explain how and why a change may help, but I don't always remember to state the obvious: test it first. – Aaron Bertrand Apr 17 '14 at 15:30
  • I see your point - what constitutes "high" compilations is entirely environment-dependent. – DanM Apr 17 '14 at 15:58
-1

The most typical reason by far I've seen those waits appear, are as a result of fragmented or insufficient indexes, and statistics that either have insufficient sample size, or are obsolete. This results in massive full table scans hogging up all the memory, which in turn produces a symptom we often see as RESOURCE_SEMAPHORE_QUERY_COMPILE.

The easiest way to verify this is to check if the queries run full table scans / index scans, when they should be doing index seeks. If you have a problem query with which you can reproduce the problem - it becomes very easy to diagnose and fix this.

I would check the indexes on the tables affected by those problem queries - ie. check index fragmentation, potential filtered indexes that aren't used, missing indexes you might want to create, etc. Also, update their statistics with FULLSCAN as soon as possible.

A good point to remember is that your problem table may not be the only one in need of this. For example, if you have a query which fetches data from 10 tables, the execution planner may occasionally show that it isn't using the index on table 1, but when you then check the index on table 1, it's actually ok. The query planner may resolve to fetch data on table 1 with a full table scan correctly, because a faulty / insufficient index on table 7 for instance, returned so much data that this would be the faster option. So diagnosing these can sometimes be tricky.

Also, if you have a lot of codebehind queries with just a few changes in variable values for instance, you might want to look at enabling optimization for ad hoc workloads. Basically what it does is store a stub of the compiled plan instead of the whole plan, saving up resources when you never quite get the exact same plans every time.

  • Most of the things you highlight would cause inefficient query plans, not high compile times. IMHO. – Aaron Bertrand Apr 17 '14 at 14:54
  • Nevertheless, I've seen this happen several times and that wait has often been exactly what followed. Obviously I have no clue how common it is or if it applies here, but the methods mentioned above fixed it. – Kahn Apr 22 '14 at 7:30
  • As an after-edit, in our case it's appeared in a rather large DB after critical indexes/statistics were affected that were used by a large number of queries being run all the time. – Kahn Apr 22 '14 at 7:46
  • 1
    Right, and the compile waits came because indexes / stats were changed, which caused all relevant plans to be recompiled, not because they were fragmented or out of date (like your answer states). – Aaron Bertrand Apr 22 '14 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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