June 14, 2011 at 11:12 pm
I have noticed strange behaviour with a sproc which occasionally gets hung at one particular batch inside the sproc.
i)I ran the below query to check what is the execution plan for each batch inside the sproc. I couldn't see a entry for a batch where i think it is being hung.
SELECT a.execution_count ,
OBJECT_NAME(objectid) Name,
query_text = SUBSTRING(
b.text,
a.statement_start_offset/2,
( CASE WHEN a.statement_end_offset = -1
THEN len(convert(nvarchar(max), b.text)) * 2
ELSE a.statement_end_offset
END - a.statement_start_offset)/2
) ,
b.dbid ,
dbname = db_name(b.dbid) ,
b.objectid ,
a.creation_time,
a.last_execution_time,
a.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
WHERE OBJECT_NAME(objectid) = 'Mysproc'
and db_name(b.dbid)='MyDBName'
ORDER BY a.last_execution_time DESC
ii) Now i got the plan handle from above query and used in sys.dm_exec_query_plan and i could see the execution plan for entire sproc. How come this is possible? I could also see the entry for the batch of sql which is hung in the execution plan but not in the above sql?
Can someone please help me with this?
June 15, 2011 at 12:13 am
Not sure what your question is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2011 at 5:26 am
If a query is executing, it has an execution plan. The plan comes first. And it gets stored in the plan cache prior to execution as well. If that process then gets stopped by resource contention or whatever it is that's causing your query to hang, there will still be a plan for it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2011 at 6:25 am
Grant Fritchey (6/15/2011)
If a query is executing, it has an execution plan. The plan comes first. And it gets stored in the plan cache prior to execution as well. If that process then gets stopped by resource contention or whatever it is that's causing your query to hang, there will still be a plan for it.
Unless it's a piece of DDL or has a recompile hint, in which case there's either no plan (for DDL) or it's not cached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2011 at 7:04 am
GilaMonster (6/15/2011)
Grant Fritchey (6/15/2011)
If a query is executing, it has an execution plan. The plan comes first. And it gets stored in the plan cache prior to execution as well. If that process then gets stopped by resource contention or whatever it is that's causing your query to hang, there will still be a plan for it.Unless it's a piece of DDL or has a recompile hint, in which case there's either no plan (for DDL) or it's not cached.
Interesting point though on the recompile. I know it doesn't cache the plan, but there's still one generated for the execution. You just can't pull it out of the plan cache to look at it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2011 at 8:29 am
GilaMonster (6/15/2011)
Not sure what your question is.
In the query which i mentioned in (i) is used to find the execution plan for each batch inside a specific proc. It will tell when was the last time each batch was executed. Now there are three select statements inside the proc which i am trying to look and from query (i) i see only the first select however if i take the plan handle number and pass it through sys.dm_exec_query_plan i see the entire execution plan of the sproc (which includes all the selects i am looking for). So my question is in the query (i) i see only a record entry for the first select however when i pass the plan handle number through sys.dm_exec_query_plan i could see the complete plan. Is it that in my query (i) ONLY the one's which are actually executed shown there?
June 15, 2011 at 10:15 am
The entire batch's query text and plan are found in the DMV. You see one statement and the entire plan because you've got a function in the query to cut up the query text and only show the currently executing statement but the entire plan is shown.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2011 at 10:41 am
GilaMonster (6/15/2011)
The entire batch's query text and plan are found in the DMV. You see one statement and the entire plan because you've got a function in the query to cut up the query text and only show the currently executing statement but the entire plan is shown.
Thanks. So it could be possible that query plan( actual plan from sys.dm_exec_query_plan ) is generated but the batch inside the sproc is hung at a particular point while doing a action. Learnt something new.. Now i need to figure why it is occasionally getting hung. Btw are "select * from table 1 into #temptable1" preferred over " create table # temptable1 insert into #temptable 1 select * from table1) or viceversa?
June 15, 2011 at 10:48 am
Just throwing another idea out there.
Fire up profiler, filter on your login / username.
Trace stmt complement in batch or proc depending on how you are executing the code
Fire up the proc and see it execute
You can also include the actual execution in the trace so you'll see what's going on and you'll be able to post them here... cause we'll need that to help out.
June 15, 2011 at 10:59 am
As Grant said, the plan is generated and cached before execution starts. It's the plan for the batch, SQL doesn't compile statement by statement.
Run the statements individually, you'll soon see which one is being forced to wait (not hung, SQL considers hung processes severe errors). Check sys.dm_exec_requests and you'll see what it's waiting for
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2011 at 3:07 pm
GilaMonster (6/15/2011)
As Grant said, the plan is generated and cached before execution starts. It's the plan for the batch, SQL doesn't compile statement by statement.Run the statements individually, you'll soon see which one is being forced to wait (not hung, SQL considers hung processes severe errors). Check sys.dm_exec_requests and you'll see what it's waiting for
Gail looks like i am back to where i was...if i pass the planhandle values into sys.dm_exec_query_plan and look at the execution plan( from sql 2008 client) i see "estimated rows" i dont see anywhere saying "actual rows".....i think it is estimated execution plan?
June 15, 2011 at 3:13 pm
sqldba_icon (6/15/2011)
GilaMonster (6/15/2011)
As Grant said, the plan is generated and cached before execution starts. It's the plan for the batch, SQL doesn't compile statement by statement.Run the statements individually, you'll soon see which one is being forced to wait (not hung, SQL considers hung processes severe errors). Check sys.dm_exec_requests and you'll see what it's waiting for
Gail looks like i am back to where i was...if i pass the planhandle values into sys.dm_exec_query_plan and look at the execution plan( from sql 2008 client) i see "estimated rows" i dont see anywhere saying "actual rows".....i think it is estimated execution plan?
How the heck do you plan to use that info to solve the issue?
Copy / paste the code into SSMS, run manually untill it fails.
As I said, run it along with profiler and trace the stamements (above and beyond the batch). Then tune that statement and be done with it.
June 15, 2011 at 3:19 pm
Ninja's_RGR'us (6/15/2011)
sqldba_icon (6/15/2011)
GilaMonster (6/15/2011)
As Grant said, the plan is generated and cached before execution starts. It's the plan for the batch, SQL doesn't compile statement by statement.Run the statements individually, you'll soon see which one is being forced to wait (not hung, SQL considers hung processes severe errors). Check sys.dm_exec_requests and you'll see what it's waiting for
Gail looks like i am back to where i was...if i pass the planhandle values into sys.dm_exec_query_plan and look at the execution plan( from sql 2008 client) i see "estimated rows" i dont see anywhere saying "actual rows".....i think it is estimated execution plan?
How the heck do you plan to use that info to solve the issue?
Copy / paste the code into SSMS, run manually untill it fails.
As I said, run it along with profiler and trace the stamements (above and beyond the batch). Then tune that statement and be done with it.
I think i answered my own question after reading Gail's blog. With the query in question what would have probably happened is it generated an estimate plan and then estimate plan was good and used as actual execution plan and so i see the estimated plan( in this case which is also actual plan) in sys.dm_exec_query_plan
Ninja the biggest problem is i cant reproduce the issue as it is very sporadic.
June 15, 2011 at 3:24 pm
Then if it's the case I'd make sure to check for fragmentation, make damn sure those stats are updated daily, maybe even more than once.
And finally I'd use option recompile on that query. If it goes from subsecond to minutes (hours) then you'd be better off taking a 10 ms recompile hit every time.
June 15, 2011 at 11:24 pm
Ninja's_RGR'us (6/15/2011)
Then if it's the case I'd make sure to check for fragmentation, make damn sure those stats are updated daily, maybe even more than once.And finally I'd use option recompile on that query. If it goes from subsecond to minutes (hours) then you'd be better off taking a 10 ms recompile hit every time.
Sure, i will try updating the stats..but i am still not sure why in the execution plan from sys.dm_exec_query_plan i see only "estimated rows" and not "actual rows"? I tried this on another server same results. Seems like it is estimated plan and not actual plan always
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply