Microsoft KB Archive/151256

= INF: Using DBCC BUFCOUNT to Configure HASH BUCKETS =

Article ID: 151256

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q151256



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

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.

Additional query words: SQL65 SQL6 DBCC MEMORY CONFIGURE SP_CONFIGURE

Keywords: kbinfo kbusage KB151256

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.