INF: Using DBCC BUFCOUNT to Configure HASH BUCKETS (151256)

MORE INFORMATION

HASH BUCKETS are an array of pointers to linked lists of buffers pointing to pages in cache. Each page in cache has a structure used to manage it known as a "buffer." If chains of buffers become too long, then performance can suffer. On OS/2 SQL Server, the value was hard coded to 1024. On SQL Server 4.2 for Windows NT, the value was hard coded to 8192. Now, "HASH BUCKETS" is configurable from 4999 to 265003 and is the actual number of entries.

The undocumented command DBCC BUFCOUNT is used to print up to the ten longest chains and the average chain length. It has the syntax

   DBCC BUFCOUNT(<N_CHAINS>)
				

where N_CHAINS is the number of the longest chains to print, up to ten. DBCC BUFCOUNT should be run only after a SQL Server has been running and in use for some time; for example, when the page cache is full of data. Otherwise the average will be artificially low and meaningless.

When configuring HASH BUCKETS, usually only the average chain size is of interest. The objective is to configure "HASH BUCKETS" so that the average is below 4. However, too low is not necessarily good because that means you are using memory for the HASH BUCKETS that might better be used for page cache, so you want the average to stay above 2. The primary factor affecting what the average chain length will be is the configured MEMORY value, and rough estimates of what the HASH BUCKETS value should be can be made based just on this value. But how much of the MEMORY value is actually used for the page cache is affected by several other parameters such as USER CONNECTIONS, OPEN OBJECTS, and LOCKS, so DBCC BUFCOUNT can be used to determine if the configured value is adequate.

The following example output is from a computer with 1 GB of physical memory, with SQL Server configured to use 800 MB. Note that DBCC TRACEON(3604) is required to see the output.

1> sp_configure memory
2> go
 name               minimum     maximum    config_value  run_value
 ------------------ ----------- ---------- ------------ -----------
 memory                2800     1048576      409600       409600

1> sp_configure "hash buckets"
2> go

 name               minimum       maximum   config_value  run_value
 ------------------ ----------- ----------- ------------ -----------
 hash buckets         4999        265003        75000       75011

1> dbcc traceon(3604)
2> go
1> dbcc bufcount(1)
2> go

**** THE 1 LONGEST BUFFER CHAINS ****

   bucket number = 26399       chain size = 10

  The Smallest Chain Size is: 0
  The Average Chain Size is: 2.277133

DBCC execution completed. If DBCC printed error messages, see your System
Administrator.
1>

				

Below is a table of suggested values for HASH BUCKETS based on the SP_CONFIGURE MEMORY configuration value. Remember these are suggestions, and that DBCC BUFCOUNT should be used to make sure your system is configured correctly.

 MEMORY         HASH BUCKETS
 ======         ============
  25600             8192
  51200            15000
 102400            25000
 204800            40000
 409600            75000
				

Note that the actual runtime value will be the nearest prime number to the configured value.