September 10, 2012 at 8:01 am
I may be asking the wrong question, but here goes...
Is there a way to use an estimated execution plan to determine what kind of locks will be held on a table?
I'm trying to resolve a month end blocking issue that I can't reproduce in our non-production environments (not quite identical environments) and I can't run the code in Production without consequences. So I'm trying to cheat by following the plan's logic and seeing if I can't locate the issue that way.
Thoughts? And should I be asking a different question?
September 10, 2012 at 8:23 am
Anyone can answer if they still wish to, but I think I found my issue.
Estimated Plan pointed out some index seeks. I googled locks and indexes and stumbled across the Create Index statement. I don't have it memorized, so completely forgot about the Allow_Row_Locks and Allow_Page_Locks clauses, both of which are defaulted ON for the index in question.
Then I came across this: http://sqlmoments.blogspot.com/2008/02/lock-unlock.html. This totally explains why we're seeing this issue at month end, but not during the daily runs.
I'm guessing (and this is a WAG) that the SSIS package I'm having problems with is putting a lock on the table during the SELECT of the data source and because of that, it is escalting the lock on the table which prevents the data destination (same table) from accepting the records. Hence, a self-blocking issue which is completely nullified if I remove the data flow and change it to a straight INSERT statement (and do my transformations in T-SQL).
EDIT: Original Post here - http://www.sqlservercentral.com/Forums/Topic1355883-147-1.aspx
If you have anything to add, please let me know. I don't want to continue on this assumption if I'm wrong.
September 10, 2012 at 9:49 am
Brandie Tarvin (9/10/2012)
Is there a way to use an estimated execution plan to determine what kind of locks will be held on a table?
No. The optimiser knows nothing about locks. It's the execution engine, when it runs the query, that picks the locks.
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
September 10, 2012 at 10:00 am
Oh, and please note that the blog post you referenced is 90% copy-paste from these two pages:
http://msdn.microsoft.com/en-us/library/ms184286%28v=sql.105%29.aspx
http://support.microsoft.com/kb/323630
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
September 10, 2012 at 10:20 am
GilaMonster (9/10/2012)
Oh, and please note that the blog post you referenced is 90% copy-paste from these two pages:http://msdn.microsoft.com/en-us/library/ms184286%28v=sql.105%29.aspx
Ah. Thank you for pointing that out.
September 10, 2012 at 10:55 am
GilaMonster (9/10/2012)
Brandie Tarvin (9/10/2012)
Is there a way to use an estimated execution plan to determine what kind of locks will be held on a table?No. The optimiser knows nothing about locks. It's the execution engine, when it runs the query, that picks the locks.
I almost forgot to answer this.
I know the optimiser won't tell me what locking might come up. What I was trying to get at (in my poorly worded way) was to see if I could read the plan in such a way as to determine myself what types of locks I might see.
What I determined was that if I looked carefully at the various touch points in the plan (Index Seek, for instance), I could then go searching for what types of locks an Index Seek might put on a table. I was able to look at the index to see if the Allow Locks bits were ON or OFF, and then follow a chain of logic from there. That did indeed help me figure out where my problem lies. At least part of it.
September 10, 2012 at 11:14 am
Brandie Tarvin (9/10/2012)
I know the optimiser won't tell me what locking might come up. What I was trying to get at (in my poorly worded way) was to see if I could read the plan in such a way as to determine myself what types of locks I might see.
No, you can't really.
An index seek might use row locks or it might start with page locks, it might escalate to table, it might start straight with table, depends on what the execution engine chooses at run time. At best you can tell which tables locks will be taken on
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
September 10, 2012 at 11:19 am
Okay. That makes sense.
I guess the next question is, is there any method of prediction on lock types or does it literally just come down to what the engine is dealing with at the time of execution?
And did that question even make sense?
September 10, 2012 at 2:47 pm
Not that I can think of. Locking can get very complex
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 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply