This is showing up in the logs several times a night. How do I find the query causing the issue? SQL Server 2008 R2 Sp1.
Possible it is not one query causing the issue. If you are using a ton of ad-hoc queries it would be prudent to enable 'optimize for ad-hoc workloads'. This way SQL Server will only create plans the second time a query is executed.
You can check using this SQL (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 go