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