Sql-server – How to know that the high number of compilation/sec is because of memory pressure not bad queries

performanceperformance-tuningsql serversql-server-2008-r2

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

Best Answer

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
Related Question