August 8, 2002 at 12:39 pm
I am trying to make sense of the output of sp_lock. I see what BOL says but what is good and what is bad? What should i be looking for?
-K
August 8, 2002 at 4:55 pm
Normally , i see if any process (SPID) is blocking by another process. If so than I kill that process.
I look field BLK in sp_lock stored procedure.
August 9, 2002 at 3:50 am
If you're just looking for blocking I find sp_who convenient. I'll probably get some argument on this, but I think generally when you're using sp_lock you're in the weeds. Typically you're looking for "bad" things, but the definition of bad depends. A table lock on an 8 row table isn't bad, a table lock on a 8mil row table is. Generally you're looking for a lock that is that more aggressive than you need (table instead of extent, extent instead of page, etc). Problem is that locks generally happen extremely fast, and that in most cases SQL takes the higher level lock because its more efficient (table lock rather than 4 million row locks for bad example).
Take this for what its worth - anything short of a full table lock usually isnt worth pursueing until you have a performance issue. I track performance issues by profiling, looking for high cpu/duration queries. Then I look at the query plan, access pattern, etc.
Andy
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply