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 as 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 else begin if @runs > @bufs select @maxmerge = @bufs else select @maxmerge = @runs if @maxmerge < 2 select @maxmerge = 2 select @merges = ceiling(log10(@runs) / log10(@maxmerge)) end select @merges "Merge Levels", 2 * @pages * @merges + @pages "Total IO"
The parameters for the procedure are:
pages – the number of pages in the table, or the number of leaf-level pages in a nonclustered index.
bufs – the number of sort buffers to configure.
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.