February 3, 2015 at 6:45 pm
Hi,
When I run the SQL Profiler to detect lock escalation, I noticed it detected a lot of lock escalation with the textdata indicating "create procedure" on certain stored procedure.
Any idea what this means?
Thanks.
February 3, 2015 at 7:27 pm
Just did some testing and the SQL Profiler actually logs those create stored procedure when you do an execute on the stored procedure and there are lock escalation (from page to table) in that stored procedure, which is a bit miss leading as it indicates create procedure.
February 3, 2015 at 10:12 pm
This was removed by the editor as SPAM
February 4, 2015 at 7:34 am
Basically, there's a lock in place during execution to prevent updates during execution. That's all.
"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
February 4, 2015 at 7:16 pm
It just that the SQL Profiler show the textdata as a create procedure which is misleading, it should have been display the execute stored procedure command.
February 5, 2015 at 3:38 am
In that case, it's referencing the object, not the call, so the text is showing the object definition.
"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
February 5, 2015 at 5:44 am
I had another look and it actually shows the entire create stored procedure, all the TSQL within it including the comments.
February 5, 2015 at 5:48 am
right, the object definition.
"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
February 5, 2015 at 5:49 am
Yes, it shows the complete object definition.
It has to be the object definition, not the EXEC, so that you can use the statement_start_offset and statement_end_offset to pinpoint the statements (DMVs like sys.dm_exec_requests)
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
February 5, 2015 at 10:13 pm
Thanks all, similar how do you tell what escalation setting has been configured on a table?
I know you can change it to AUTO, DISABLE or TABLE but how do you query the current setting on the table?
February 5, 2015 at 10:28 pm
It's in sys.tables.
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply