Procedure for estimating merge levels and I/O

The following procedure estimates the number of merge runs and the amount of physical I/O required to create an index:

create proc merge_runs @pages int, @bufs int
declare @runs int, @merges int, @maxmerge int

select @runs = ceiling ( @pages / @bufs ) 

/* if all pages fit into sort buffers, no merge runs needed */
if @runs <=1 
        select @merges = 0
    if @runs > @bufs select @maxmerge = @bufs
    else  select @maxmerge = @runs

    if @maxmerge < 2 select @maxmerge = 2

    select @merges = ceiling(log10(@runs) / log10(@maxmerge)) 
select @merges "Merge Levels",
        2 * @pages * @merges + @pages "Total IO"

The parameters for the procedure are:

This example uses the default number of sort buffers for a table with 2,000,000 pages:

merge_runs 2000000, 500, 20

The merge_runs procedure estimates that 2 merge runs and 10,000,000 I/Os would be required to create the index:

 Merge Levels Total IO    
 ------------ ----------- 
            2    10000000  

Increasing the number of sort buffers to 1500 reduces the number of merge runs and the I/O required:

merge_runs 2000000, 1500 
 Merge Levels Total IO    
 ------------ ----------- 
            1     6000000  

The total I/O predicted by this procedure may be different than the I/O usage on your system, depending on the size and configuration of the cache and pools used by the sort.