September 24, 2009 at 11:13 am
I want to better understand what locks might get issued (and on what objects) for inserting a row into a table.
I understand that locks can be issued at various levels - table level, row level, page level - maybe extent level. Does it ever depend on the number of rows that already exist?
I imagine that if there are indexes or persisted views, that some locking happens there as well.
Can someone point me to the resource that can explain it all, or can someone offer an explanation?
Thanks, Bill.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
September 24, 2009 at 12:12 pm
I typically would look for INSIDE SQL SERVER for the best explanation of how this works. It seems to me that there was good information in there on how the locks are escalated or created.
My guess is that it depends on how the insert happens. Is it at the and of the heap/CI? Is a page split needed? Does it impact more than one page (LOB data), etc.
September 24, 2009 at 12:23 pm
Thanks, Steve. I'll have a look.
In addition to your resources, It looks like SQL Profiler (and Windows Perf Mon with Locks Object Counter - if you just want counts, presumably) can be used to monitor and record locking information.
When I can partition some time, I'll do that. I'll probably learn a good bit from setting up scenarios and looking at the locking activity. However, I guess one of the reasons why I was looking for information was to see if someone had already done just that and had collected the results - and had summarized the cases.
sp_lock can display lock information - but when some locks are held and released faster than sp_lock can display them, that limits the effectivenes.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply