Will cleaning data cache help to resolve compiler memory pressure?

Question

I currently experience RESOURCE_SEMAPHORE_QUERY_COMPILE waits causing CPU spike 100% that lasts 10-20 mins and disappears only by completely stopping traffic. I figured it could be a memory pressure and wondering if reducing indexes size will help with memory pressure.

Answer 1

Take a look at this reference on MSDN regarding sys.dm_os_wait_stats. Here is a quote from the above reference regarding RESOURCE_SEMAPHORE_QUERY_COMPILE:

Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.

This wait type, as quoted above, is from too many compilations happening at a time. What it sounds like, if you are consistently seeing this wait type (unlike going from a cold plan cache) then you have too high of an ad hoc workload. In other words, you may be experiencing the lack of plan reuse (a bunch of plans that aren't parameterized/prepared, or not using stored procedures and therefore a new ad hoc query comes in since there is no plan that can be reused it'll compile a new one).

There are a few ways to remedy this, if that is indeed the case. The real way to fix this is to ensure that prepared plans and stored procedures are being used. A brute force method is by setting the database to utilize forced parameterization. I would save the latter as a last resort, as there could be some downsides to forcing parameterization.

There are a few ways to keep an eye on how your plan reuse is looking. The easiest (in my opinion) would be to capture a few perfmon counters for the instance:

\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec

It's a pretty good rule of thumb that compilations per sec shouldn't exceed 10% of batch requests per second, and recompilations per second shouldn't exceed 10% of compilations per second. I typically go from there when troubleshooting plan cache reuse. Feel free to monitor these counters, and post the results in your question and I can help interpret.

And to answer your direct questions, no reducing index sizes or clearing the data cache will not alleviate your issues.