3

My server is having a high compilation/sec compared to batches/sec (more than 10%). How do I know that this is because of memory pressure that forces removing cached plans from memory, or because of bad ad-hoc queries that looks new to SQL Server every time it sent to be executed?

Note that:

  • CPU is keeping above 80%.
  • Target memory and total memory PerfMon counters are always equal.
  • PLE has a very good value.
  • buffer cache hit ratio is 99%
  • Memory grand pending is always 0
  • Do you have "optimize for ad-hoc workload" turned on? – Max Vernon Feb 25 '16 at 14:38
2

You can see what types of queries are in your plan cache with the following query (source: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/)

SELECT objtype AS [CacheType],
COUNT_BIG(*) AS [Total Plans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
AVG(usecounts) AS [Avg Use Count],
SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
    ELSE 0
    END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
SUM(CASE WHEN usecounts = 1 THEN 1
    ELSE 0
    END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans 
GROUP BY objtype 
ORDER BY [Total MBs – USE Count 1] DESC

This should show you if most of your saved plans are ad-hoc or for procedures. If most of them are only being used once, then try using the "optimize for ad-hoc workload" option mentioned like so:

sp_configure 'show advanced options',1
GO
reconfigure
GO
sp_configure 'optimize for ad hoc workloads',1
GO
reconfigure
GO

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.