Benchmark Testing

Introduction

Process to establish a method to compare and benchmark performance of SQL Server.

· hardware independent

· version independent

· Repeatable

· Simple

· Scalable

· Not require any external software or applications

· Have sufficient run time and impact to make comparison

Overview

The test involves a series of sql scripts which produce sequential and random io through data inserts and data updates.

Test 1            ( see script 1 )

Simple Insert

This test creates a table and inserts 1 million rows into the table

The insert includes a 6k text data block and four secondary indexes which have been set in such a manner with the

data generation to produce non sequential key values.

The table has an ascending clustered key on an identity column.

The index choice and data generation ensure that the secondary indexes are badly fragmented this helps to force

random io  – in other words worst case

Index Name	Rows	Scan Density	Pages	Mb
PK_TestTable1	1,000,000	99.5960642154324	76924	601
idx_TestTable1_cGuid	1,000,000	12.5167037861915	4490	35
idx_TestTable1_cSguid	1,000,000	12.5114633826805	7633	60
idx_TestTable1_cDate1	1,000,000	38.4734399174832	5964	47
idx_TestTable1_cBig	1,000,000	49.3245583650849	11391	89

Table showing secondary index fragmentation after data load.

( Note that there is no leaf level fragmentation ( Primary Key ) )

The populated table has an overall size as shown

Data	601 MB
Secondary Indexes	235 MB
Text data	7,800 MB
Total	8,636 MB

Physical Index Statistics ( script 101 )

Index id	Index Type	Index depth	Index level	Pages	Rows	Minrecordsize (bytes)	Maxrecordsize (bytes)	Avgrecordsize (bytes)
1	CLUS	3	0	76924	1000000	595	595	595
1	CLUS	3	1	285	76924	11	11	11
1	CLUS	3	2	1	285	11	11	11
1	CLUS	1	0	1000000	2000000	84	6014	3049
2	NC	3	0	4490	1000000	24	24	24
2	NC	3	1	29	4490	27	30	29.999
2	NC	3	2	1	29	27	30	29.896
3	NC	3	0	7633	1000000	40	40	40
3	NC	3	1	64	7633	43	46	45.999
3	NC	3	2	1	64	43	46	45.953
4	NC	3	0	5964	1000000	44	44	44
4	NC	3	1	69	5964	47	50	49.999
4	NC	3	2	1	69	47	50	49.956
5	NC	3	0	11391	1000000	44	44	44
5	NC	3	1	147	11391	47	50	49.999
5	NC	3	2	1	147	47	50	49.979

Index 1 is the clustered primary key ( the yellow line represents the text data )

Index activity ( script 103 )

TableName	IndexName	Index Type	Seeks	Scans	Lookups	Updates
TestTable1	PK_TestTable1	CLUS	0	0	0	1000000
TestTable1	idx_TestTable1_cGuid	NC	0	0	0	1000000
TestTable1	idx_TestTable1_cSguid	NC	0	0	0	1000000
TestTable1	idx_TestTable1_cDate1	NC	0	0	0	1000000
TestTable1	idx_TestTable1_cBig	NC	0	0	0	1000000

Index Operational stats ( script 104 )

Table Name	Index Name	Index Type	Total writes	Total insert writes	Leaf writes	Non leaf writes	Insert leaf writes	Insert non leaf writes	Leaf page splits	Non leaf page splits	reads
TestTable1	PK_TestTable1	CLUS	0	1076924	0	0	1000000	76924	76924	286	0
TestTable1	idx_TestTable1_cGuid	NC	0	1004334	0	0	1000000	4334	4334	27	0
TestTable1	idx_TestTable1_cSguid	NC	0	1007605	0	0	1000000	7605	7605	66	0
TestTable1	idx_TestTable1_cDate1	NC	0	1005964	0	0	1000000	5964	5964	70	0
TestTable1	idx_TestTable1_cBig	NC	0	1011391	0	0	1000000	11391	11391	148	0

Performance can be calculated by dividing the operation by the elapsed time. My sample times for the single table insert

with secondary indexes produced figures of:-

2,450 Rows/sec

1,270 Mb/min

20 Mb/sec

Test 2

Simple insert of 4 tables ( simultaneous insert )

Create and populate 4 tables with 1 million rows of data each.

Each table is as the description of the table in Test 1

Table and Index data ( script 102 )

TableName	IndexName	IndexDepth	TotalPages	LeafRows	Mb
TestTable1	PK_TestTable1	2	1077210	2000000	8415
TestTable2	PK_TestTable2	2	1077210	2000000	8415
TestTable3	PK_TestTable3	2	1077210	2000000	8415
TestTable4	PK_TestTable4	2	1077210	2000000	8415
TestTable4	idx_TestTable4_cBig	3	19693	1000000	153
TestTable1	idx_TestTable1_cBig	3	19690	1000000	153
TestTable2	idx_TestTable2_cBig	3	19611	1000000	153
TestTable3	idx_TestTable3_cBig	3	19610	1000000	153
TestTable3	idx_TestTable3_cDate1	2	14458	1000000	112
TestTable1	idx_TestTable1_cDate1	2	14372	1000000	112
TestTable4	idx_TestTable4_cDate1	2	14282	1000000	111
TestTable2	idx_TestTable2_cDate1	2	14264	1000000	111
TestTable3	idx_TestTable3_cSguid	2	7778	1000000	60
TestTable1	idx_TestTable1_cSguid	2	7676	1000000	59
TestTable2	idx_TestTable2_cSguid	2	7673	1000000	59
TestTable4	idx_TestTable4_cSguid	2	7588	1000000	59
TestTable2	idx_TestTable2_cGuid	2	4543	1000000	35
TestTable1	idx_TestTable1_cGuid	2	4486	1000000	35
TestTable3	idx_TestTable3_cGuid	2	4423	1000000	34
TestTable4	idx_TestTable4_cGuid	2	4412	1000000	34

Index physical stats ( script 101 )

Index Id	Index type	Index depth	Index level	Page count	Record count	Min record size (bytes)	Max record size (bytes)	Avg record size (bytes)
1	CLUS	3	0	76924	1000000	595	595	595
1	CLUS	3	1	285	76924	11	11	11
1	CLUS	3	2	1	285	11	11	11
1	CLUS	1	0	1000000	2000000	84	6014	3049
2	NC	3	0	4437	1000000	24	24	24
2	NC	3	1	27	4437	27	30	29.999
2	NC	3	2	1	27	27	30	29.888
3	NC	3	0	7491	1000000	40	40	40
3	NC	3	1	64	7491	43	46	45.999
3	NC	3	2	1	64	43	46	45.953
4	NC	3	0	5964	1000000	44	44	44
4	NC	3	1	69	5964	47	50	49.999
4	NC	3	2	1	69	47	50	49.956
5	NC	3	0	11391	1000000	44	44	44
5	NC	3	1	147	11391	47	50	49.999
5	NC	3	2	1	147	47	50	49.979

(The other three tables have the same stats)

Index activity ( script 103 )

TableName	IndexName	Index Type	Seeks	Scans	Lookups	Updates
TestTable3	idx_TestTable3_cGuid	NC	0	0	0	1000000
TestTable3	PK_TestTable3	CLUS	0	0	0	1000000
TestTable3	idx_TestTable3_cBig	NC	0	0	0	1000000
TestTable3	idx_TestTable3_cDate1	NC	0	0	0	1000000
TestTable3	idx_TestTable3_cSguid	NC	0	0	0	1000000
TestTable1	idx_TestTable1_cBig	NC	0	0	0	1000000
TestTable1	idx_TestTable1_cSguid	NC	0	0	0	1000000
TestTable1	idx_TestTable1_cDate1	NC	0	0	0	1000000
TestTable1	PK_TestTable1	CLUS	0	0	0	1000000
TestTable1	idx_TestTable1_cGuid	NC	0	0	0	1000000
TestTable2	idx_TestTable2_cDate1	NC	0	0	0	1000000
TestTable2	idx_TestTable2_cSguid	NC	0	0	0	1000000
TestTable2	idx_TestTable2_cBig	NC	0	0	0	1000000
TestTable2	idx_TestTable2_cGuid	NC	0	0	0	1000000
TestTable2	PK_TestTable2	CLUS	0	0	0	1000000
TestTable4	idx_TestTable4_cSguid	NC	0	0	0	1000000
TestTable4	idx_TestTable4_cDate1	NC	0	0	0	1000000
TestTable4	idx_TestTable4_cBig	NC	0	0	0	1000000
TestTable4	PK_TestTable4	CLUS	0	0	0	1000000
TestTable4	idx_TestTable4_cGuid	NC	0	0	0	1000000

Index operational stats ( script 104 )

Table Name	Index Name	Index Type	Total writes	Total insert writes	Leaf writes	Non leaf writes	Insert leaf writes	Insert non leaf writes	Leaf page splits	Non leaf page splits	reads
TestTable1	PK_TestTable1	CLUS	0	1076924	0	0	1000000	76924	76924	286	0
TestTable1	idx_TestTable1_cGuid	NC	0	1004438	0	0	1000000	4438	4438	31	0
TestTable1	idx_TestTable1_cSguid	NC	0	1007551	0	0	1000000	7551	7551	65	0
TestTable1	idx_TestTable1_cDate1	NC	0	1005964	0	0	1000000	5964	5964	70	0
TestTable1	idx_TestTable1_cBig	NC	0	1011391	0	0	1000000	11391	11391	148	0
TestTable2	PK_TestTable2	CLUS	0	1076924	0	0	1000000	76924	76924	286	0
TestTable2	idx_TestTable2_cGuid	NC	0	1004543	0	0	1000000	4543	4543	31	0
TestTable2	idx_TestTable2_cSguid	NC	0	1007545	0	0	1000000	7545	7545	64	0
TestTable2	idx_TestTable2_cDate1	NC	0	1005964	0	0	1000000	5964	5964	70	0
TestTable2	idx_TestTable2_cBig	NC	0	1011391	0	0	1000000	11391	11391	148	0
TestTable3	PK_TestTable3	CLUS	0	1076924	0	0	1000000	76924	76924	286	0
TestTable3	idx_TestTable3_cGuid	NC	0	1004546	0	0	1000000	4546	4546	30	0
TestTable3	idx_TestTable3_cSguid	NC	0	1007593	0	0	1000000	7593	7593	66	0
TestTable3	idx_TestTable3_cDate1	NC	0	1005964	0	0	1000000	5964	5964	70	0
TestTable3	idx_TestTable3_cBig	NC	0	1011391	0	0	1000000	11391	11391	148	0
TestTable4	PK_TestTable4	CLUS	0	1076924	0	0	1000000	76924	76924	286	0
TestTable4	idx_TestTable4_cGuid	NC	0	1004407	0	0	1000000	4407	4407	29	0
TestTable4	idx_TestTable4_cSguid	NC	0	1007620	0	0	1000000	7620	7620	67	0
TestTable4	idx_TestTable4_cDate1	NC	0	1005964	0	0	1000000	5964	5964	70	0
TestTable4	idx_TestTable4_cBig	NC	0	1011391	0	0	1000000	11391	11391	148	0

Performance can be calculated by dividing the operation by the elapsed time. My sample times for the single table insert

with secondary indexes produced figures of:-

4,608 Rows/sec

2,391 Mb/min

40 Mb/sec

Test 3

2 update queries per table . 8 total simultaneous processes

The Updates are performed by building two cursor lists per table based upon the odd and even integer sequence numbers ( NumKey ).

The actual selects are actually non sequential guids

( To obtain only the index stats for the update processes the database was taken offline to force a clear of the dmvs prior to the run .)

Index Activity ( script 103 )

TableName	IndexName	(No column name)	Seeks	Scans	Lookups	Updates
TestTable4	idx_TestTable4_cBig	NC	0	0	0	1000000
TestTable4	idx_TestTable4_cDate1	NC	0	0	0	1000000
TestTable4	idx_TestTable4_cSguid	NC	0	0	0	0
TestTable1	idx_TestTable1_cBig	NC	0	0	0	1000000
TestTable1	idx_TestTable1_cDate1	NC	0	0	0	1000000
TestTable1	idx_TestTable1_cSguid	NC	0	0	0	0
TestTable2	idx_TestTable2_cBig	NC	0	0	0	1000000
TestTable2	idx_TestTable2_cDate1	NC	0	0	0	1000000
TestTable2	idx_TestTable2_cSguid	NC	0	0	0	0
TestTable3	idx_TestTable3_cBig	NC	0	0	0	1000000
TestTable3	idx_TestTable3_cDate1	NC	0	0	0	1000000
TestTable3	idx_TestTable3_cSguid	NC	0	0	0	0
TestTable3	idx_TestTable3_cGuid	NC	1000000	0	0	0
TestTable1	idx_TestTable1_cGuid	NC	1000000	0	0	0
TestTable4	idx_TestTable4_cGuid	NC	1000000	0	0	0
TestTable2	idx_TestTable2_cGuid	NC	1000000	0	0	0
TestTable2	PK_TestTable2	CLUS	2000002	2	0	1000000
TestTable4	PK_TestTable4	CLUS	2000002	2	0	1000000
TestTable1	PK_TestTable1	CLUS	2000002	2	0	1000000
TestTable3	PK_TestTable3	CLUS	2000002	2	0	1000000

Table and Index data ( script 102 )

TableName	IndexName	IndexDepth	TotalPages	LeafRows	Mb
TestTable1	PK_TestTable1	2	1077210	2000000	8415
TestTable2	PK_TestTable2	2	1077210	2000000	8415
TestTable3	PK_TestTable3	2	1077210	2000000	8415
TestTable4	PK_TestTable4	2	1077210	2000000	8415
TestTable1	idx_TestTable1_cBig	3	19838	1000000	154
TestTable4	idx_TestTable4_cBig	3	19809	1000000	154
TestTable3	idx_TestTable3_cBig	3	19779	1000000	154
TestTable2	idx_TestTable2_cBig	3	19648	1000000	153
TestTable2	idx_TestTable2_cDate1	2	14366	1000000	112
TestTable3	idx_TestTable3_cDate1	2	14331	1000000	111
TestTable4	idx_TestTable4_cDate1	2	14279	1000000	111
TestTable1	idx_TestTable1_cDate1	2	14277	1000000	111
TestTable1	idx_TestTable1_cSguid	2	7757	1000000	60
TestTable4	idx_TestTable4_cSguid	2	7647	1000000	59
TestTable3	idx_TestTable3_cSguid	2	7593	1000000	59
TestTable2	idx_TestTable2_cSguid	2	7531	1000000	58
TestTable3	idx_TestTable3_cGuid	2	4538	1000000	35
TestTable2	idx_TestTable2_cGuid	2	4511	1000000	35
TestTable4	idx_TestTable4_cGuid	2	4504	1000000	35
TestTable1	idx_TestTable1_cGuid	2	4352	1000000	34

Index Operational stats ( script 104 )

Table Name	Index Name	Index Type	Total writes	Total insert writes	Leaf writes	Non leaf writes	Insert leaf writes	Insert non leaf writes	Leaf page splits	Non leaf page splits	reads
TestTable1	PK_TestTable1	CLUS	1000000	0	1000000	0	0	0	0	0	2000562
TestTable1	idx_TestTable1_cDate1	NC	6096	1008477	0	6096	1000000	8477	8477	97	0
TestTable1	idx_TestTable1_cBig	NC	11509	1007928	0	11509	1000000	7928	7928	71	0
TestTable2	PK_TestTable2	CLUS	1000000	0	1000000	0	0	0	0	0	2000557
TestTable2	idx_TestTable2_cDate1	NC	6084	1007660	0	6084	1000000	7660	7660	89	0
TestTable2	idx_TestTable2_cBig	NC	11504	1007950	0	11504	1000000	7950	7950	66	0
TestTable3	PK_TestTable3	CLUS	1000000	0	1000000	0	0	0	0	0	2000570
TestTable3	idx_TestTable3_cDate1	NC	6080	1008453	0	6080	1000000	8453	8453	98	0
TestTable3	idx_TestTable3_cBig	NC	11503	1008053	0	11503	1000000	8053	8053	74	0
TestTable4	PK_TestTable4	CLUS	1000000	0	1000000	0	0	0	0	0	2000565
TestTable4	idx_TestTable4_cDate1	NC	6087	1008753	0	6087	1000000	8753	8753	102	0
TestTable4	idx_TestTable4_cBig	NC	11506	1008046	0	11506	1000000	8046	8046	76	0

Performance can be calculated by dividing the operation by the elapsed time. My sample times for the 8 simultaneous update

runs produced :-

12 million seeks

12 million updates

30,708 seeks/updates per second

( Note that the physical operations would likely be far less than this as we’d hope that some of the io would be batched, however as

far as SQL Server is concerned it did this number of io )

Test 4

8 simultaneous updates and 4 simultaneous Inserts to tables in test 3

( the inserts finish in approx 50% the time of the updates; e.g. 6 mins 45 secs and 15 mins ( av times ) )

Index Activity ( script 103 )

Table Name	Index Name	Index Type	Seeks	Scans	Lookups	Updates
TestTable4	idx_TestTable4_cBig	NC	0	0	0	1372456
TestTable4	idx_TestTable4_cDate1	NC	0	0	0	1372456
TestTable4	idx_TestTable4_cSguid	NC	0	0	0	200000
TestTable1	idx_TestTable1_cBig	NC	0	0	0	1385521
TestTable1	idx_TestTable1_cDate1	NC	0	0	0	1385521
TestTable1	idx_TestTable1_cSguid	NC	0	0	0	200000
TestTable2	idx_TestTable2_cBig	NC	0	0	0	1375287
TestTable2	idx_TestTable2_cDate1	NC	0	0	0	1375287
TestTable2	idx_TestTable2_cSguid	NC	0	0	0	200000
TestTable3	idx_TestTable3_cBig	NC	0	0	0	1375599
TestTable3	idx_TestTable3_cDate1	NC	0	0	0	1375599
TestTable3	idx_TestTable3_cSguid	NC	0	0	0	200000
TestTable4	PK_TestTable4	CLUS	1172456	0	0	1372456
TestTable2	PK_TestTable2	CLUS	1175287	0	0	1375287
TestTable3	PK_TestTable3	CLUS	1175599	0	0	1375599
TestTable1	PK_TestTable1	CLUS	1185521	0	0	1385521
TestTable4	idx_TestTable4_cGuid	NC	1172456	1172458	0	200000
TestTable2	idx_TestTable2_cGuid	NC	1175287	1175289	0	200000
TestTable3	idx_TestTable3_cGuid	NC	1175599	1175601	0	200000
TestTable1	idx_TestTable1_cGuid	NC	1185521	1185523	0	200000

Index Operational stats ( script 104 )

Table Name

Index Name

Index Type

Total writes

Total insert writes

Leaf writes

Non leaf writes