March 24, 2011 at 3:37 am
select serverproperty ('productVersion')
9.00.4035.00
select serverproperty ('productLevel')
SP3
---
A remote site (I do not have direct access to their instance, but understand it is 2005/sp3) has some long running queries triggering the Blocked Process Report (threshold set to 30 seconds). I asked them to confirm the content sys.dm_exec_sql_text.text, and it returned this (in the interests of readability I have excluded all but the first and last part of the SQL - the detail of the content is not significant) :
[font="Courier New"] create procedure sys.sp_sproc_columns ( @procedure_name nvarchar(390) = '%', ...
(s_scrvov.procedure_number between @group_num_lower and @group_num_upper) order by 1, 2, 3, 18 end [/font]
Regardless of why the blocking is happening (the blocker is application code btw), what puzzles me is why there is an attempt to CREATE this catalog procedure (see Books Online (2008) - ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/62c18c21-35c5-4772-be0d-ffdcc19c97ab.htm) , rather than just trying to execute it.
---
For some context, the blocking application sql is of the form
[font="Courier New"]insert into #t1 (account_id, item_id)
select i.account_id, i.item_id
from t2 i
join t3
on i.account_id = t3.account_id
and i.item_id = t3.item_id[/font]
... where #t1 is a temporary table, t2 is a "persistent" table, and t3 is a table created outside of the current transaction, but which will be dropped at the end of the session.
Wait type: LCK_M_S
WaitingResourceType: KEY
WaitingRequestType: LOCK
BlockingRequestType: LOCK
Thank you for any advice on this.
March 24, 2011 at 8:14 am
You might want to contact Microsoft, but I'm pretty sure the "CREATE" part of that proc is nothing to worry about. When I searched on the first few words, I found this link. Scroll down and you'll see this isn't the only system proc that comes across as CREATE PROCEDURE instead of EXECUTE.
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/7ef42395-0475-4820-969e-b7e7ab297974
March 24, 2011 at 12:46 pm
Thanks very much for the reply: yes, I was kind of moving towards that thought - even though it might have been exec'ing the sp not creating, it's just reporting the structure, not actually attempting a create, and that's either because a) that's just a limitation of that dm view, or b) Microsoft are trying to be helpful?
Thanks again
March 25, 2011 at 4:54 am
You're welcome. But I too would love to know the exact explanation, if there is one. So if anyone has anything more specific, feel free to add to this thread!
March 25, 2011 at 5:47 am
Any time you look at the sql_text for a session that's running a stored procedure (any stored procedure, user or system), you will get the create procedure statement in the text column.
The point being you can use the stmt_start_offset and statement_end_offset to do a substring on that and come up with exactly the current statement within the procedure that is executing. If the query_text showed the EXEC <stored procedure name> then you would not be able to do that so easily and the DMV would be a fair bit less useful.
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply