INF: Using DBCC BUFCOUNT to Configure HASH BUCKETS

• Microsoft SQL Server, versions 6.0 & 6.5

SUMMARY

The DBCC BUFCOUNT command can be used to configure the HASH BUCKETS value where SQL Server is installed on computers with large amounts of memory.

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>)

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

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

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

   bucket number = 26399       chain size = 10

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

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