understanding blocking | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

understanding blocking

We have several stored procedures that have been taking an inordinate time to run (over 1000 seconds…). After using Profiler and narrowing it down, it appears that there are a collection of sp’s that access the same tables, however only one of them does an insert while the others do selection. Perhaps I don’t have a good enough grasp on the fundamentals of locking, but, doesn’t SQL Server have row level locking? Would doing an insert on a table cause the entire table to be locked? Is there any way around this?
When those procedures are running, have you looked at blocking in sysprocesses? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
D– No, I confess I have not, but can you recommend a specific query to do that–I’ll have another opportunity in about 4 hours to view them again. What am I looking for? M
http://www.sqlservercentral.com/scripts/contributions/826.asp You can also see the entire collection sqlservercentral has for locking/blocking here: http://www.sqlservercentral.com/Scr…m=Lock and Connection Management&categoryid=3 MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Check out the links that Derrick suggested. Also if you don’t mind dirty reads you can place lock hints on your queries that are doing the reading.
[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Just understand what that’s doing to your data integrity when you do it.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.