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.
