August 15, 2011 at 8:37 am
When I do a select on sysprocess table and retrieve the actual SQL that is being executed, I see CREATE PROCEDURE statements. I would have expected just some exec PROC statements to be there - Can someone throw some light on why a CREATE PROCEDURE is fired everytime the procedure is invoked to be executed? The one I see is not a dynamically generated query - it is a simple select from a table.
August 15, 2011 at 8:50 am
What you're seeing is the procedure executing. Sys.dm_exec_sql_text shows the create statement (and the entire contents) when the proc runs. This is so that you can use the stmt_start and stmt_end to see exactly what statement in the procedure is currently executing.
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
August 15, 2011 at 9:15 am
thanks.
But does that mean that with every call the sp is getting created, compiled and then executed?
August 15, 2011 at 9:18 am
No.
All you are seeing is the execution. That's it, nothing more.
The sys.dm_exec_sql_text returns the full create statement of the procedure so that you can do things like substring with the stmt_start and stmt_end columns and see exactly what statement within the proc is currently executing.
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
August 15, 2011 at 9:44 am
Got it! Thanks!
That create statement was just misleading. 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply