Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

Thank you

share|improve this question
you are right, but the spread on this problem mainly led me to the mentioned problem. So it was a bad comment from me ... I'll delete it. – bummi Nov 19 '12 at 19:31
I can use extended events to trap the sqltext but I don't know which event to use. Any suggestions? – SQLSpartan Nov 19 '12 at 19:38
@SQLSpartan sqlserver.error_reported should do it I would have thought. – Martin Smith Nov 19 '12 at 19:43
1  
I would suggest reading Jonathan's blog for information on why you might not want to rely on sql_text: sqlskills.com/blogs/jonathan/post/… – Aaron Bertrand Nov 19 '12 at 21:27
What is the max memory setting and available RAM on the server ? You may want to refer this msdn.microsoft.com/en-us/library/ms177682(v=sql.100).aspx and This connect item connect.microsoft.com/SQLServer/feedback/details/521943/… Note: SQL Server 2008 R2 CU3 has a possible FIX: A non-yielding scheduler error or an error 8623 occurs when you run a query that contains a large IN clause in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. – Kin Apr 25 at 21:30

migrated from stackoverflow.com Nov 19 '12 at 20:49

1 Answer

up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.