Configuring the number of sort buffers parameter

When creating indexes in parallel, the number of sort buffers must be equal to or less than 90 percent of the number of buffers in the pool area, before the wash marker, as shown in Figure 9-2.

Figure 9-2: Area available for sort buffers

The limit of 90 percent of the pool size is not enforced when you configure the number of sort buffers parameter, but it is enforced when you run the create index command, since the limit is enforced on the pool for the table being sorted. The maximum value that can be set for number of sort buffers is 32,767; this value is enforced by sp_configure.

Computing the allowed sort buffer value for a pool

sp_cacheconfig returns the size of the pool in megabytes and the wash size in kilobytes. For example, this output shows the size of the pools in the default data cache:

Cache: default data cache,   Status: Active,   Type: Default
      Config Size: 0.00 Mb,   Run Size: 38.23 Mb
      Config Replacement: strict LRU,   Run Replacement: strict LRU
      Config Partition:            2,   Run Partition:            2
 IO Size  Wash Size Config Size  Run Size     APF Percent 
 -------- --------- ------------ ------------ ----------- 
     2 Kb   4544 Kb      0.00 Mb     22.23 Mb     10      
    16 Kb   3200 Kb     16.00 Mb     16.00 Mb     10

This procedure takes the size of the 2K pool and its wash size as parameters, converts both values to pages and computes the maximum number of pages that can be used for sort buffers:

create proc bufs @poolsize numeric(6,2), @wash int
as
select "90% of non-wash 2k pool" = 
    ((@poolsize * 512) - (@wash/2)) * .9

The following example executes bufs with values of “22.23 Mb” for the pool size and “4544 Kb” for the wash size:

bufs 22.23, 4544

The bufs procedure returns the following results:

90% of non-wash 2k pool
----------------------- 
              8198.784

This command sets the number of sort buffers to 8198 pages:

sp_configure "number of sort buffers", 8198

If the table on which you want to create the index is bound to a user-defined cache, configure the appropriate number of sort buffers for the specific cache. As an alternative, you can unbind the table from the cache, create the index, and rebind the table:

sp_unbindcache pubtune, titles

create clustered index title_ix 
    on titles (title_id)

sp_bindcache pubtune_cache, pubtune, titles

WARNING! The buffers used by a sort are reserved entirely for the use of the sort until the sort completes. They cannot be used by another other task on the server. Setting the number of sort buffers to 90 percent of the pool size can seriously affect query processing if you are creating indexes while other transactions are active.