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	Insert leaf writes	Insert non leaf writes	Leaf page splits	Non leaf page splits	reads
TestTable1	PK_TestTable1	CLUS	1185521	215384	1185521	0	200000	15384	15384	57	1185521
TestTable1	idx_TestTable1_cGuid	NC	0	200879	0	0	200000	879	879	1	2371044
TestTable1	idx_TestTable1_cSguid	NC	0	201208	0	0	200000	1208	1208	2	0
TestTable1	idx_TestTable1_cDate1	NC	0	1395459	0	0	1385521	9938	9938	101	0
TestTable1	idx_TestTable1_cBig	NC	568	1395439	0	568	1385521	9918	9918	94	0
TestTable2	PK_TestTable2	CLUS	1175287	215384	1175287	0	200000	15384	15384	57	1175287
TestTable2	idx_TestTable2_cGuid	NC	0	201011	0	0	200000	1011	1011	4	2350576
TestTable2	idx_TestTable2_cSguid	NC	0	201201	0	0	200000	1201	1201	4	0
TestTable2	idx_TestTable2_cDate1	NC	0	1385129	0	0	1375287	9842	9842	86	0
TestTable2	idx_TestTable2_cBig	NC	568	1385108	0	568	1375287	9821	9821	90	0
TestTable3	PK_TestTable3	CLUS	1175599	215384	1175599	0	200000	15384	15384	57	1175599
TestTable3	idx_TestTable3_cGuid	NC	0	201052	0	0	200000	1052	1052	3	2351200
TestTable3	idx_TestTable3_cSguid	NC	0	201301	0	0	200000	1301	1301	7	0
TestTable3	idx_TestTable3_cDate1	NC	0	1385822	0	0	1375599	10223	10223	94	0
TestTable3	idx_TestTable3_cBig	NC	568	1385826	0	568	1375599	10227	10227	105	0
TestTable4	PK_TestTable4	CLUS	1172456	215384	1172456	0	200000	15384	15384	57	1172456
TestTable4	idx_TestTable4_cGuid	NC	0	200978	0	0	200000	978	978	3	2344914
TestTable4	idx_TestTable4_cSguid	NC	0	201296	0	0	200000	1296	1296	11	0
TestTable4	idx_TestTable4_cDate1	NC	0	1382249	0	0	1372456	9793	9793	90	0
TestTable4	idx_TestTable4_cBig	NC	568	1382138	0	568	1372456	9682	9682	87	0

Table and Index data ( script 102 )

Table Name	Index Name	Index Depth	Total Pages	Leaf Rows	Mb
TestTable1	PK_TestTable1	2	1292651	2400000	10098
TestTable2	PK_TestTable2	2	1292651	2400000	10098
TestTable3	PK_TestTable3	2	1292651	2400000	10098
TestTable4	PK_TestTable4	2	1292651	2400000	10098
TestTable3	idx_TestTable3_cDate1	3	19717	1200000	154
TestTable4	idx_TestTable4_cDate1	3	18740	1200000	146
TestTable1	idx_TestTable1_cDate1	3	18615	1200000	145
TestTable3	idx_TestTable3_cBig	3	18521	1200000	144
TestTable1	idx_TestTable1_cBig	3	18040	1200000	140
TestTable2	idx_TestTable2_cBig	3	17958	1200000	140
TestTable4	idx_TestTable4_cBig	3	17918	1200000	139
TestTable2	idx_TestTable2_cDate1	3	17680	1200000	138
TestTable4	idx_TestTable4_cSguid	2	9009	1200000	70
TestTable3	idx_TestTable3_cSguid	2	8979	1200000	70
TestTable2	idx_TestTable2_cSguid	2	8917	1200000	69
TestTable1	idx_TestTable1_cSguid	2	8897	1200000	69
TestTable3	idx_TestTable3_cGuid	2	5545	1200000	43
TestTable2	idx_TestTable2_cGuid	2	5528	1200000	43
TestTable1	idx_TestTable1_cGuid	2	5487	1200000	42
TestTable4	idx_TestTable4_cGuid	2	5459	1200000	42

Typical Table Data

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	92308	1200000	595	595	595
1	CLUS	3	1	342	92308	11	11	11
1	CLUS	3	2	1	342	11	11	11
1	CLUS	1	0	1200000	2400000	84	6014	3049
2	NC	3	0	5454	1200000	24	24	24
2	NC	3	1	32	5454	27	30	29.999
2	NC	3	2	1	32	27	30	29.906
3	NC	3	0	8830	1200000	40	40	40
3	NC	3	1	66	8830	43	46	45.999
3	NC	3	2	1	66	43	46	45.954
4	NC	4	0	18415	1200000	44	44	44
4	NC	4	1	197	18415	47	50	49.999
4	NC	4	2	2	197	47	50	49.984
4	NC	4	3	1	2	47	50	48.5
5	NC	4	0	17851	1200000	44	44	44
5	NC	4	1	186	17851	47	50	49.999
5	NC	4	2	2	186	47	50	49.983
5	NC	4	3	1	2	47	50	48.5

9.4 million seeks  4.7 million scans

18 million updates

=============================================================

The scripts

Index physical stats ( script 101 )

( This script applies to a named table )

select index_id,index_type_desc,index_depth,index_level,page_count,record_count,min_record_size_in_bytes,max_record_size_in_bytes,avg_record_size_in_bytes

from sys.dm_db_index_physical_stats (db_id(), object_id('testtable1'), null ,null ,'detailed' ) ;

Index activity ( script 103 )

( Query to retrieve index usage stats )

select object_name(s.object_id) as TableName,isnull(i.name,'HEAP') as IndexName,

case i.index_id

when 0 then 'HEAP'

when 1 then 'CLUS'

else 'NC'

end as 'Index Type',

user_seeks as Seeks, user_scans as Scans, user_lookups as Lookups, user_updates as Updates

from sys.dm_db_index_usage_stats s join sys.indexes i on i.object_id = s.object_id

and i.index_id = s.index_id

where database_id = db_id() and objectproperty(s.object_id,'IsUserTable') = 1

order by (user_seeks + user_scans + user_lookups ) asc;

Query for index operational stats ( script 104 )

select object_name(s.object_id) as TableName, isnull(i.name,'HEAP') as IndexName,

case i.index_id

when 0 then 'HEAP'

when 1 then 'CLUS'

else 'NC'

end as IndexType

,'total_writes'=leaf_update_count+ leaf_delete_count+nonleaf_update_count + nonleaf_delete_count

,'total_insert_writes'=leaf_insert_count+nonleaf_insert_count

, 'leaf_writes'=leaf_update_count+ leaf_delete_count

, 'nonleaf_writes'=nonleaf_update_count + nonleaf_delete_count

, 'insert_leaf_writes'=leaf_insert_count

, 'insert_nonleaf_writes'=nonleaf_insert_count

, 'leaf_page_splits' = leaf_allocation_count

, 'nonleaf_page_splits' = nonleaf_allocation_count

, reads=range_scan_count + singleton_lookup_count

from sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL) s join sys.indexes i

on i.object_id = s.object_id and i.index_id = s.index_id

where objectproperty(s.object_id,'IsUserTable') = 1

and (leaf_update_count+ leaf_delete_count+nonleaf_update_count + nonleaf_delete_count+leaf_insert_count+nonleaf_insert_count)>0

order by TableName asc;

Table and Index data ( script 102 )

declare @table table(object_id int,ind_name sysname,index_id int)

insert into @table

select top 25 si.[object_id],si.name,si.index_id

from sys.indexes si

where objectproperty(si.[object_id],'IsUserTable') = 1

--

select object_name(ui.[object_id]) as TableName,ui.ind_name as IndexName,

max(fps.index_level)as IndexDepth,sum(fps.page_count) as TotalPages,max(fps.record_count) as LeafRows, sum(fps.page_count)/128 as Mb

from @table ui

CROSS APPLY master.dbo.fn_db_index_physical_stats (DB_ID(),ui.[object_id],ui.index_id,NULL, 'Detailed') AS fps

group by ui.[object_id],ui.ind_name

order by sum(fps.page_count) desc ;

Function for above query

USE [master]

GO

/****** Object:  UserDefinedFunction [dbo].[fn_db_index_physical_stats]    Script Date: 10/16/2008 14:41:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create function [dbo].[fn_db_index_physical_stats]

--

-- sys.dm_db_index_physical_stats is actually a function not a view

-- sadly you can't use CROSS APPLY with it, however, drop it in a table

-- valued function and you can!

--

(

@db_id int

,@object_id int

,@index_id int

,@partition_number int

,@mode nvarchar(16)

)

Returns @table TABLE

(

[database_id] [smallint] NULL,

[object_id] [int] NULL,

[index_id] [int] NULL,

[partition_number] [int] NULL,

[index_type_desc] [nvarchar](60) NULL,

[alloc_unit_type_desc] [nvarchar](60) NULL,

[index_depth] [tinyint] NULL,

[index_level] [tinyint] NULL,

[avg_fragmentation_in_percent] [float] NULL,

[fragment_count] [bigint] NULL,

[avg_fragment_size_in_pages] [float] NULL,

[page_count] [bigint] NULL,

[avg_page_space_used_in_percent] [float] NULL,

[record_count] [bigint] NULL,

[ghost_record_count] [bigint] NULL,

[version_ghost_record_count] [bigint] NULL,

[min_record_size_in_bytes] [int] NULL,

[max_record_size_in_bytes] [int] NULL,

[avg_record_size_in_bytes] [float] NULL,

[forwarded_record_count] [bigint] NULL

)

BEGIN

insert into @table

select *

from sys.dm_db_index_physical_stats (@db_id, @object_id, @index_id ,@partition_number ,@mode )

return

END;

--end function

Table Populate script 

One script for Test 1 

Four scripts for Test 2

( You need four of these – change the table name to create the other three  )

create table dbo.TestTable1

(

NumKey int identity(1,1)not null,

cGuid uniqueidentifier default newid()null,

cSguid uniqueidentifier default newid() not null,

cBig bigint null,

cInt int null,

cDate1 datetime not null,

cDate2 datetime null,

cVchar1 varchar(50) null,

cChar1 char(20) null,

cVchar2 varchar(500) null,

cChar2 char(100),

cText text

);

go

alter table dbo.TestTable1 add constraint PK_TestTable1 primary key clustered(Numkey);

create index idx_TestTable1_cGuid on dbo.TestTable1(cGuid);

create index idx_TestTable1_cSguid on dbo.TestTable1(cSguid,cGuid);

create index idx_TestTable1_cDate1 on dbo.TestTable1(cDate1,cChar1,cBig);

create index idx_TestTable1_cBig on dbo.TestTable1(cBig,cDate2,cInt,csGuid);

go

--

--

set nocount on

--

declare @num bigint,@count int,@count2 int,@count3 int

set @count = 1

set @count2 = 12

set @count3 = 1

while @count<1000001

begin

IF (@count%2)=0

begin

insert into dbo.TestTable1(cBig,cInt,cDate1,cDate2,cVchar1,cChar1,cVchar2,cChar2,cText)

values( 36854775807+(36854775807/@count),@count2*3,dateadd(dd,@count3,'25 MAY 2007'),dateadd(mi,@count,getdate()),

reverse(convert(varchar(50),dateadd(dd,@count3,'25 MAY 2007'))),'0000'+convert(varchar(7),@count)+convert(varchar(4),@count3),

replicate(convert(char(36),newid()),10),convert(varchar(20),datename(dw,getdate()+@count)+datename(month,getdate()+@count))+'blahblahblahblahblahblahblahblahblahblahblahblah'

,convert(char(6000),getdate()))

end

else

begin

insert into dbo.TestTable1(cBig,cInt, cDate1,cDate2,cVchar1,cChar1,cVchar2,cChar2,cText)

values( 854775807-(36854775807/@count),@count2*2,dateadd(dd,@count3,'5 dec 2006'),dateadd(mi,@count,getdate()),

reverse(convert(varchar(50),dateadd(dd,@count3,'25 MAY 2007'))),'0000'+convert(varchar(7),@count)+convert(varchar(4),@count3),

replicate(convert(char(36),newid()),10),convert(varchar(20),datename(dw,getdate())+datename(month,getdate()))+'blahblahblahblahblahblahblahblahblahblahblahblah'

,convert(char(6000),getdate()))

end

set @count = @count+1;

set @count2 = @count2+2;

IF @count2>50

set @count2 = 12;

IF (@count%3000)=0

set @count3 = @count3+1;

end

Table Update Scripts ( there are two per table ) Test 3 and Test 4

Create the other 6 scripts by changing the table name in the scripts

Script 1 of 2

set nocount on

DECLARE @cGuid           uniqueidentifier,@NumKey int

--

DECLARE TableCursor1 CURSOR FAST_FORWARD FOR

SELECT             cGuid from dbo.TestTable1 where Numkey % 2 = 1

--

OPEN TableCursor1

FETCH NEXT FROM TableCursor1 INTO @cGuid

WHILE @@FETCH_STATUS = 0

BEGIN

select @Numkey = Numkey from dbo.TestTable1 where cGuid=@cGuid

update dbo.TestTable1 set cBig = cBig*2,cDate1 = '19 Jan 1995',cDate2 = '25 dec 1999',cChar1 ='ohar123456789aabbccd'

where Numkey = @Numkey

FETCH NEXT FROM TableCursor1 INTO @cGuid

END

CLOSE TableCursor1

DEALLOCATE TableCursor1

GO

Script 2 of 2

set nocount on

DECLARE @cGuid           uniqueidentifier,@NumKey int;

--

DECLARE TableCursor1 CURSOR FAST_FORWARD FOR

SELECT             cGuid from dbo.TestTable1 where Numkey % 2 = 0;

--

OPEN TableCursor1

FETCH NEXT FROM TableCursor1 INTO @cGuid

WHILE @@FETCH_STATUS = 0

BEGIN

select @Numkey = Numkey from dbo.TestTable1 where cGuid=@cGuid

update dbo.TestTable1 set cBig = cBig*2,cDate1 = '19 Jan 1950',cDate2 = '25 dec 1950',cChar1 ='zhar123456789aabbccd'

where Numkey = @Numkey

FETCH NEXT FROM TableCursor1 INTO @cGuid

END

CLOSE TableCursor1;

DEALLOCATE TableCursor1;

GO

Add additional Rows ( Test 4 only )

( You need four of these – change the table name to create the other three  )

--

--

set nocount on

--

declare @num bigint,@count int,@count2 int,@count3 int

set @count = 1

set @count2 = 12

set @count3 = 1

while @count<200001

begin

IF (@count%2)=0

begin

--Begin tran fred

insert into dbo.TestTable1(cBig,cInt,cDate1,cDate2,cVchar1,cChar1,cVchar2,cChar2,cText)

values( 36854775807+(36854775807/@count),@count2*3,dateadd(dd,@count3,'25 MAY 2007'),dateadd(mi,@count,getdate()),

reverse(convert(varchar(50),dateadd(dd,@count3,'25 MAY 2007'))),'0000'+convert(varchar(7),@count)+convert(varchar(4),@count3),

replicate(convert(char(36),newid()),10),convert(varchar(20),datename(dw,getdate()+@count)+datename(month,getdate()+@count))+'blahblahblahblahblahblahblahblahblahblahblahblah'

,convert(char(6000),getdate()))

--commit tran fred

end

else

begin

--begin tran fred

insert into dbo.TestTable1(cBig,cInt, cDate1,cDate2,cVchar1,cChar1,cVchar2,cChar2,cText)

values( 854775807-(36854775807/@count),@count2*2,dateadd(dd,@count3,'5 dec 2006'),dateadd(mi,@count,getdate()),

reverse(convert(varchar(50),dateadd(dd,@count3,'25 MAY 2007'))),'0000'+convert(varchar(7),@count)+convert(varchar(4),@count3),

replicate(convert(char(36),newid()),10),convert(varchar(20),datename(dw,getdate())+datename(month,getdate()))+'blahblahblahblahblahblahblahblahblahblahblahblah'

,convert(char(6000),getdate()))

--commit tran fred

end

set @count = @count+1;

set @count2 = @count2+2;

IF @count2>50

set @count2 = 12;

IF (@count%3000)=0

set @count3 = @count3+1;

end