Blocking Process (SQl-Server 6.5)
Cecilio
When a process is Blocking is there a performance decrease? I find that one Process runs pretty
quickly when it can grab resources when it needs them (which makes sense), but when the process is
blocking it runs really slow. Can someone let me know a little bit more about Blocking
Processes', and extent locks (How to reduce the number of).
Thanks,
Cecilio A. Thomas
bmc...@my-dejanews.com
In article <6kkve7$c...@dfw-ixnews4.ix.netcom.com>,
Cecilio <ceci...@ix.netcom.com> wrote:
To avoid two processes modifying the same data in two different ways at the
same time, SQL Server will lock objects. If I try to modify a row of data in a
table, I will acquire an exclusive page lock on the page containing that row.
Once my modification is complete, and the transaction is committed, my lock is
released.
If, during the time I've locked that page, another process also requests the
page (for either read or write access) the server will put the process into
LOCK SLEEP. That is, the second proecss is blocked. You're stuck waiting
around, doing nothing, until the lcok for which you're waiting is released.
If I write a SQL statement that will modify a certain percentage of the
pages in a table, or a certain number of rows in the table (depends on
some sp_configure parameters), I might get a table lock instead of a
cartload of page locks. This is good because the server needs to track
just one lock resource; it's bad because nobody else can access the
table for the duration of my transaction.
Extent locks are required when objects grow in size. Every 2k page in the
database is organized into groups of 8. One group of 8 is called an extent. If
I make an insert to a table, and the table needs to allocate a new page to
accomodate my insert, the server looks to see if any pages are allocated to my
table that are currently unused. Pages are allocated to objects by extent, not
by page. (BTW, this means that the minimum size of an object in your DB is
16k, even if it contains a single row of data.)
You'll see a lot of extent locks during a bcp in (the table's growing by leaps
and bounds) or during a create index.
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading