By default, SQL Server 6.x uses 7993 hash buckets and hashes the requested
data page's ID to determine the bucket the page is buffered in, if the page is
already in the data cache. SQL Server reads the page from disk if it can't find
the requested page in the bucket. Just like your socks, SQL Server can search
the bucket quicker if fewer pages are buffered in that bucket. Oddly, you can
degrade system performance by adding lots of memory if the hash bucket chain
length (the number of pairs of socks in the drawer) gets too long. Microsoft
says that you need to keep the average bucket chain length to four pages or
fewer and that you probably won't need to change the default 7993 value unless
your system has more than 192MB of memory.
From our experience, average chain lengths can easily be longer than four
pages with much lower memory configurations. Always monitor the length of your
hash buckets regardless of how much memory you have, and increase the
sp_configure value if your average chain lengths are greater than four. You can
easily check chain sizes with two Database Consistency Checker (DBCC) commands:
DBCC BUFCOUNT and SQLPERF(HASHSTATS).
Both commands report slightly different views of the same hash chain
information. BUFCOUNT reports the length of the 10 longest chains, and
SQLPERF(HASHSTATS) reports additional information on the average and maximum
chain lengths. SQLPERF (HASHSTATS) is available only in SQL 6.5 Service Pack 3
(SP3) and later. You must run DBCC TRACEON(3604) before you run either command
so that SQL Server will send the DBCC output to the client screen rather than
just writing it to the error log. See Table 2 and Table 3 for sample output.
Do you know how long your hash chains are? A properly tuned hash chain can
have a surprisingly big effect on performance, and you can easily make this fix.
Go tune it right now. But don't get too attached to hash buckets, because SQL
Server 7.0 eliminates them.
I have a stored procedure that loops through all the records in a
customer table, performs a calculation, and updates each record. I've got my
Lock Escalation (LE) threshold maximum set to the default (200), but my stored
procedure never escalates the individual page locks to an exclusive table lock
as I would expect. What's happening?
SQL Server 6.x typically locks at the page level, but sometimes it escalates
to a table lock to be more efficient. Three sp_configure parameters control this
escalation process. (As in the case of hash buckets, SQL Server 7.0 will
eliminate these parameters, probably because of changes that the new row-level
locking requires.) Here's a brief description of the LE parameters:
LE threshold maximum. This parameter determines the maximum
number of page locks to hold before escalating to a table lock. If the number of
page locks is greater than the escalation maximum, a table lock will occur
regardless of whether the number of page locks has exceeded the LE threshold
percent. The default is 200.
LE threshold minimum. This parameter determines the minimum
number of page locks a user must have before SQL Server escalates to a table
lock. A table lock will occur only if a user reaches this minimum when the
number of page locks exceeds the LE threshold percent. LE threshold minimum
prevents the server from escalating to a table lock for small tables, which
quickly reach the LE threshold percentage. The default is 20.
LE threshold percent. This parameter specifies the
percentage of page locks needed on a table before a SQL Server requests a table
lock. The default (0) causes a table lock to occur only when the number of page
locks reaches the LE threshold maximum.
Prev. page
1
2
[3]
4
next page 
