Test Environment
The test environment server is a 2-way Xeon 2.4GHz/512K cache, 533MHz system bus, and 2GB memory on a motherboard with the ServerWorks GC-LE chipset. The test database data file resides on 2x15K 18G SCSI U160 drives (OS) striped, all log files on one 10K SCSI drive, and the tempdb data file on an IDE/ATA drive. The database recovery model is set to SIMPLE, and 4GB of space is pre-allocated for both data and log. In addition, the max degree of parallelism was restricted to one CPU for most tests. The results are single query time measurements. There are some variations between measurements since there is no easy method to determine the contents of the buffer cache. An attempt was made to determine a reasonably repeatable measurement.
The following script creates a test table and populates the table with test data. As defined, this table has 8 integer columns of 4 bytes each, a char column of 10 bytes, a decimal column of 5 bytes, and three other columns of 8 bytes each for a total 71 bytes. Along with the metadata overhead, this works out to approximately 81 bytes per row, allowing 99 rows to fit in each 8KB page at 99% fill factor, with 77 bytes free.
CREATE TABLE M3C_00 (
ID int NOT NULL,
ID2 int NOT NULL,
ID3 int NOT NULL,
ID4 int NOT NULL,
ID5 int NOT NULL,
ID6 int NOT NULL,
SeqID int NOT NULL,
DistID int NOT NULL,
Value char(10) NOT NULL,
randDecimal decimal (9,4) NOT NULL,
randMoney money NOT NULL,
randDate datetime NOT NULL,
seqDate datetime NOT NULL )
The script below populates the above table with 10,000,000 rows. The ID column is a sequence from 1 to 10M, but is not explicitly defined as an identity column. The columns ID2 to ID6 are not important to this series of tests, and any values can be used. The SeqID values are sequential and DistID values are distributed. In this case, the table has 10M rows. The first 100,000 rows have SeqID value 1; the next 100K rows have SeqID value 2 and so on. The first row has DistID value 1, the next row with DistID value is row 101, and so on. All rows with a specific SeqID are in adjacent rows. All rows with a specific DistID value are 100 rows apart, which cause each row having a common DistID value to be in a different 8KB data page. This table is not clustered, so while there is no guarantee on the row placement, it does appear that the rows are inserted in sequence.
BEGIN TRANSACTION
DECLARE @I int, @rowCnt int,
@p int, @sc1 int, @dv1 int
SELECT @I = 1, @rowCnt =
10000000, @p =100, @sc1 = 100000
SELECT @dv1 = @rowCnt/100000
WHILE @I <= @RowCnt BEGIN
INSERT M3C_00
(ID, ID2, ID3, ID4, ID5, ID6, SeqID, DistID,
Value, randDecimal, randMoney,
randDate, seqDate)
VALUES ( @I,
@I, @I/2, @I/4, @I/10, @I/20,
(@I-1)/@sc1 + 1, — Sequential values
(@I-1)%(@dv1) + 1, — Distributed values
>CHAR(65 + 26*rand())+CHAR(65 +
26*rand())+CHAR(65 + 26*rand())
+CONVERT(char(6),CONVERT(int,100000*(9.0*rand()+1.0)))+CHAR(65 + 26*rand()),
10000*rand(), 10000*rand(),
DATEADD(hour,100000*rand(),’1990-01-01′),
DATEADD(hour,@I/5,’1990-01-01′) )
SET @I = @I+1
END
COMMIT TRANSACTION
CHECKPOINT
The above script takes 17min 45sec to run, averaging 9,380 row inserts per second. The M3C_00 table ends up at 105,264 pages with 95% fillfactor. Instead of regenerating the table each time a fresh data set is needed, the original data set is preserved in the above table and a copy of the original is generated with the SELECT INTO statement below.
SELECT
ID,ID2,ID3,ID4,ID5,ID6,SeqID,DistID,Value,randDecimal,randMoney,randDate,seqDate
INTO M3C_01
FROM M3C_00
CHECKPOINT
This table has 101,012 pages, averaging 99 rows per page at 99% fillfactor for a table size of just over 789MB. The SELECT INTO takes approximately 28 seconds to run, averaging 357,000 rows inserted per second. The following two indexes are created on SeqID and DistID columns. Each index occupies 18,554 pages or 145MB.
CREATE INDEX IX_M3C_01_Seq
ON M3C_01 (SeqID) WITH SORT_IN_TEMPDB
CHECKPOINT
CREATE INDEX IX_M3C_01_Dist
ON M3C_01 (DistID) WITH SORT_IN_TEMPDB
CHECKPOINT
The first index takes approximately 30 seconds to create with 256M memory and the seconds takes 60 seconds.