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:
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 
