Query optimization stage

Last Post 05 Apr 2012 04:07 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

04 Apr 2012 07:59 AM
I have an error
"The query processor ran out of internal resources and could not produce a query plan."
This is a vendor database, I know that the process is trying to run a dynamic report and this is a big statement, but I do not exactly know how to capture the exact statement.
Is there is a way to capture statements that are currently in optimization stage.
I do not see any corresponding event in the Profiler.
May be I can use some system views?
Thank you
New Member
New Member

04 Apr 2012 09:56 AM
If they are running out of internal resources they have bigger fish to fry. How much memory do they have and is there a MAX Memory setting? But to answer your question you can see the statements sent to SQL Server by using the statement starting events in profiler. If thsi is a stored proc and it is producing dynamic sql then change the proc to print the statement instead of executing it.
New Member
New Member

04 Apr 2012 10:05 AM
The server has 12GB of RAM, and 8.6 max for sql server.
The t-sql starting event starts after query compilation, so I cannot see it in the Profiler, and because this is a vendor application I do not have access to source code.
But I need to know what our users are trying to execute.
New Member
New Member

05 Apr 2012 04:07 AM
I thought the Batch: Starting event was before compilation but again I never tried under those conditions. You might have a try at the various Error events to see if any of them will include the statement. 12GB of ram is not a lot these days for a server runing SQL Server. Heck my laptop has 12GB. Any chance you can upgrade the memory? In any case running a trace will be the best way to see what is being executed when there are no errors . I have never looked into that situation before but since you are on 2008 you might be able to see the submitted query via Extended Events. As for what they have already run you can always use the procedure cache and the query stats DMV's to see plans that have been cached and are still in cache. Here are a couple of queries you can use to fidn some interesting stuff about what is in cache:

SELECT t.[text] AS [Adhoc Batch or Object Call],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
, qs.[execution_count] AS [Counts]
, qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] / qs.[execution_count]) AS [Avg Worker Time]
, qs.[total_physical_reads] AS [Total Physical Reads], (qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads]
, qs.[total_logical_writes] AS [Total Logical Writes], (qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes]
, qs.[total_logical_reads] AS [Total Logical Reads], (qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads]
, qs.[total_clr_time] AS [Total CLR Time], (qs.[total_clr_time] / qs.[execution_count]) AS [Avg CLR Time]
, qs.[total_elapsed_time] AS [Total Elapsed Time], (qs.[total_elapsed_time] / qs.[execution_count]) AS [Avg Elapsed Time]
, qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
-- ORDER BY [Total Worker Time] DESC
-- ORDER BY [Total Physical Reads] DESC
-- ORDER BY [Total Logical Writes] DESC
-- ORDER BY [Total Logical Reads] DESC
-- ORDER BY [Total CLR Time] DESC
-- ORDER BY [Total Elapsed Time] DESC

SELECT t.text AS [Batch Text], qp.*, ps.*
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_sql_text (ps.sql_handle)AS t
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp

SELECT b.[cacheobjtype], b.[objtype], b.[refcounts], b.[usecounts]
, a.[dbid], a.[objectid], b.[size_in_bytes], a.[text]
FROM sys.dm_exec_cached_plans as b
CROSS APPLY sys.dm_exec_sql_text(b.[plan_handle]) AS a
ORDER BY [usecounts] DESC
You are not authorized to post a reply.

Acceptable Use Policy