Resource_semaphore_query_compile
This wait brings back nightmares from SQL Server 2005 x86 pre SP2. However, I have been troubleshooting this wait type for a customer recently so I thought I would write about it. Loosely, it means that new plans cannot be put into the procedure cache fast enough so they wait. There can be, in my experience, be two causes(post SQL Server 2005 sp2) of this:
- Plan reuse issues
- Large memory grants
Plan Reuse Issues
This is the easy one or hard one depending on your perspective. It is easy to identify but hard to fix. In an ideal world, you rewrite all of your adhoc sql as stored procedures following best practices for plan reuse. Easier said than done for large application that did not begin this way. Here is how you diagnose it.
SELECT TEXT ,cp.*
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan' and usecounts = 1
ORDER BY cp.size_in_bytes DESC
The key columns are the size_in_bytes and usecounts. You want low size_in_bytes and high usecounts. Rows with 1 usecount are not being reused.
Aside from rewriting the application, there are a few setting that you can use to help. In SQL Server 2005+, you can turn on forced parameterization. This will auto-parameterize a lot of queries. It sounds good but it is usually not a silver bullet. SQL Server 2008 introduces the “Optimize for ADHOC Workloads” server setting. This creates small stub plans for plans that will not be reused. This further reduces the footprint of the procedure cache.
Here are some additional resources:
Compilation and recompilation whitepaper
Parameters and Execution Plan Reuse
Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005 (See workaround section)
Large Memory Grants
This is because the more memory used for grants, the less that will be available for query compilation. The symptoms are easy to identify. A high number in the perfmon counter memory manager : memory grant outstanding is a dead giveaway. Once you see that, run this query.
SELECT TEXT, query_plan, requested_memory_kb, granted_memory_kb, used_memory_kb
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
This will give you queries currently waiting on a memory memory grant along with the query text and their xml showplan. Typically, when I see a server in a problem state, this query returns more than 10 rows(sometimes a lot more rows) and the queries are requesting fifty to several hundred megabytes of memory each.
I usually look for a common query that is waiting many times and requesting a large amount of memory. Look at its query plan. You want to find iterators that are large memory consumers like hash joins\aggregates, sorts and merge joins with sorts.
Sometimes the resolution is just recompiling the query or updating stats. If that doesn’t work, you can look for missing indexes. I would keep covering indexes in mind as well as filtered indexes. If the query still does a hash join(and index scan) on the filtered index, it will be cheaper. If there are no good choices for indexes, look for places to tune the query. Specifically, I have found help by moving the filters to the on clause but that might not meet requirements so be careful.
If there are cardinality overestimation problems and the iterator is actually only touching a few rows, you can try LOOP join hint. However, this should be rare or there may be parameter sniffing issues due uneven data distribution. If this is the case, simply recompiling the query should fix it most of the time. If that works, a statement level recompile, plan guides or OPTIMIZE FOR hint.
This is a tough wait state to solve. If it is not bad statistics, then you probably have some application design issues that need to be addressed in the long run. Hopefully this post will help you identify the problem and at least put some short term Band-Aids in place.
This content is published under the Attribution-Share Alike 3.0 Unported license.
Comments
My RSS
-
My LifeStream
My Presence
