Query optimization stage

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

--
04 Apr 2012 08:59 AM
Hi,
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
gunneyk
New Member
New Member

--
04 Apr 2012 10: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.
ntt123
New Member
New Member

--
04 Apr 2012 11:05 AM
Thanks,,
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.
gunneyk
New Member
New Member

--
05 Apr 2012 05: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
ORDER BY [Counts] 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