MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_01C83847.AA2E65A0" This document is a Single File Web Page, also known as a Web Archive file. If you are seeing this message, your browser or editor doesn't support Web Archive files. Please download a browser that supports Web Archive, such as Windows® Internet Explorer®. ------=_NextPart_01C83847.AA2E65A0 Content-Location: file:///C:/204BDD12/AnalysingIndexesPart4Sizedoesmatter.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" Analysing Indexes Part 4 - Size does matter

Analysing Indexes Part 4 - Size does matter

v= I’ll say now that this is a whole lot of figures and values, I’ve attempte= d to summarize what I can at the end of the article; for those who want to cut to the chase.

o    ( The naming = used for these examples is not representative of any conventions or standards )<= o:p>

v= As part of my analysis of indexes I was particularly interested in the size of indexes, the database I am working with has many tables of several hundred million rows, these tables are often wide with clustered primary keys of up= to 6 columns and twenty or more secondary indexes.

v= I’m not interested in the theory of indexing here or making any ground breaking revelations, I’m just observing that an awareness of how the index is formed may help us understand why the optimiser may sometimes seem to ignore what we may think is a perfectly good index

v= My initial interest in this subject was aroused when I noticed I had an index = of 8gb for a single datetime column on a 10gb table which had a considerable number of columns.

1.   For my tests I created two t= ables and populated them with two million rows each.

Why two tables? Well I wanted to= see what difference a nullable column might have.

Whilst I would have liked to cre= ate much larger tables the reality is that I needed to run repeated calls to sys.dm_db_index_phy= sical_stats and I know from experience that these calls can take a long time to run on a four hundred million row table.

Results for the initial table creation – the nullable table returns the exact same size results as = my non nullable table., using sp_spaceused and sys.dm_db_index_physi= cal_stats

name

rows

reserved

data

index_size

unused

Testtable2

2000000

1333576 KB

1333392 KB

8 KB

176 KB

with nulls

index_id

index_type_desc

index_depth

index_level

page_count

record_count

a= vg_record

s= ize_in_bytes

0

HEAP

1

0

166674

2000000

577

name

rows

reserved

data

index_size

unused

TestTable

2000000

1333576 KB

1333392 KB

8 KB

176 KB

no nulls

index_id

index_type_desc

index_depth

index_level

page_count

record_count

a= vg_record

s= ize_in_bytes

0

HEAP

1

0

166674

2000000

577

For all my results I will scale up to four hundred million rows, I’ll refer to this as my “target table”

2.   Leaving the tables as HEAPs I created a non clustered secondary index on a datetime column, what was noticeable was that the index on the nullable column showed an average reco= rd size 3 bytes larger, this might not sound much but on my target table of fo= ur hundred million rows that equates to 1.1gb.

Testtable2 ( no nulls )

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

0

HEAP

1

0

166674

2000000

577

2

NONCLUSTERED INDEX

3

0

4695

2000000

17

2

NONCLUSTERED INDEX

3

1

15

4695

23

2

NONCLUSTERED INDEX

3

2

1

15

23

Testtable ( nulls )

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

0

HEAP

1

0

166674

2000000

577

2

NONCLUSTERED INDEX

3

0

5435

2000000

20

2

NONCLUSTERED INDEX

3

1

19

5435

26

2

NONCLUSTERED INDEX

3

2

1

19

26

So what is all the fuss about with index size?

Well indexes make pages and as far as the optimiser is concerned pages make prizes , ok not actually, but the number of pages used is an integral = part of the cost of a query so the fewer the pages the better. <= /span>

In my two million row test table there is a difference of 744 pages, 15%, 5.8Mb or 1.1Gb for my tar= get table.

We know that when there is no clustered index a rid is added to the index, so how big is a rid ? Seems li= ke 9 bytes, so our 8 byte datetime actually widens to 17 bytes and 20 bytes where the column is nullable.

3. Next step was to create a clustered primary key on an integer column.

Testtable2 ( no nulls )

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

1

CLUSTERED INDEX

3

0

153847

2000000

577

1

CLUSTERED INDEX

3

1

248

153847

11

1

CLUSTERED INDEX

3

2

1

248

11

2

NONCLUSTERED INDEX

3

0

3711

2000000

13

2

NONCLUSTERED INDEX

3

1

10

3711

19

2

NONCLUSTERED INDEX

3

2

1

10

19

Testtable ( nulls )

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

1

CLUSTERED INDEX

3

0

153847

2000000

577

1

CLUSTERED INDEX

3

1

248

153847

11

1

CLUSTERED INDEX

3

2

1

248

11

2

NONCLUSTERED INDEX

3

0

4455

2000000

16

2

NONCLUSTERED INDEX

3

1

14

4455

22

2

NONCLUSTERED INDEX

3

2

1

14

22

The secondary index reduces in size by 4 bytes each entry, that will save 1.5gb on my target table<= /li>
The addition now is presumably 4 bytes for the= int plus an extra byte; maybe.

The extra storage required for the clustered i= ndex is 1.6mb for the test table, 320mb for my target table.

This means adding a clustered index to the heap has reduced overall storage by approx 1.5gb for the secondary index on= my target table.

The conclusion here is that indexed HEAPs are = less efficient and take more storage than the same table with a clustered index.

Perhaps also note that the size of the actual table has also reduced by some 12,578 pages ( 98Mb ), that equates to 19.2GB for my target table

If we add the saving for the secondary index t= his means we have saved 20.7Gb on this table.

NOTE1: It is very important to consider the number of pages that an index consumes when looking at efficiency, f= or my target table the secondary index requires 197,800 LESS pages with the clust= ered index. ( 744,400 vs 942,200 - the index on the HEAP is 27% larger )

NOTE2: For the remainder of the analysis I am concentrating on the table without nullable columns.

NOTE3: DBA Question: “ How can you remove fragmentation from a HEAP?”<= o:p>

=           Answer: “ Add a clustered index and = then remove the clustered index “

=           Should I ever interview you for a = job as a DBA this is one of the questions I may ask – there are at least two other answers.

NOTE4: There may have been some fragmentation created during the population of the table; however, technically populating a heap shouldn’t cause fragmentation, or maybe it does?

4. Replacing the clustered Prima= ry Key with a non clustered Primary Key on the integer column gives us two seconda= ry indexes, each entry being + 9 bytes

The primary key is index 3<= /o:p>

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

0

HEAP

1

0

153847

2000000

577

2

NONCLUSTERED INDEX

3

0

4695

2000000

17

2

NONCLUSTERED INDEX

3

1

15

4695

23

2

NONCLUSTERED INDEX

3

2

1

15

23

3

NONCLUSTERED INDEX

3

0

3711

2000000

13

3

NONCLUSTERED INDEX

3

1

6

3711

11

3

NONCLUSTERED INDEX

3

2

1

6

11

5. A non clustered unique constraint is the same ( index 3 again )<= /span>

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

0

HEAP

1

0

153847

2000000

577

2

NONCLUSTERED INDEX

3

0

4695

2000000

17

2

NONCLUSTERED INDEX

3

1

15

4695

23

2

NONCLUSTERED INDEX

3

2

1

15

23

3

NONCLUSTERED INDEX

3

0

3711

2000000

13

3

NONCLUSTERED INDEX

3

1

6

3711

11

3

NONCLUSTERED INDEX

3

2

1

6

11

6. A clustered unique constraint is the same as a clustered Primary Key

The results are the same as step= 3

( Index 2 is the same index as created in step 2 )

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

1

CLUSTERED INDEX

3

0

153847

2000000

577

1

CLUSTERED INDEX

3

1

248

153847

11

1

CLUSTERED INDEX

3

2

1

248

11

2

NONCLUSTERED INDEX

3

0

3711

2000000

13

2

NONCLUSTERED INDEX

3

1

10

3711

19

2

NONCLUSTERED INDEX

3

2

1

10

19

7. Change the Clustered primary Key to a guid from an integer. Secondary index= on datetime remains. ( Note this is a sequential guid )

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

1

CLUSTERED INDEX

4

0

153847

2000000

577

1

CLUSTERED INDEX

4

1

477

153847

23

1

CLUSTERED INDEX

4

2

2

477

23

1

CLUSTERED INDEX

4

3

1

2

23

2

NONCLUSTERED INDEX

3

0

6689

2000000

25

2

NONCLUSTERED INDEX

3

1

28

6689

31

2

NONCLUSTERED INDEX

3

2

1

28

31

Our key depth is now 23 bytes, but the seconda= ry index is now 8 + 17 bytes per entry, for my target table changing the clustered key from an integer to a guid increases the size of my seco= ndary index by 4.6Gb

Again the increase in pages used by the second= ary index is significant, 59= 9,200

·         Personally I dislike uniq= ue identifiers, in most cases there is no reason for their use over an integer column – here’s another reason to dislike them as your choice of surrogate key or row identifier.

·         Index 2 is still my origi= nal datetime index

8. As for 7. but the datetime column is now nullable; note that the width per key entry for the 8 byte datetime is now 28 bytes in the leaf and 34 bytes in t= he tree.

For our target table the index f= or 3gb of datetime data ( all values populated ) is actually 11.4gb, nearly 4 times the size of the data.

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

1

CLUSTERED INDEX

4

0

153847

2000000

577

1

CLUSTERED INDEX

4

1

477

153847

23

1

CLUSTERED INDEX

4

2

2

477

23

1

CLUSTERED INDEX

4

3

1

2

23

2

NONCLUSTERED INDEX

3

0

7435

2000000

28

2

NONCLUSTERED INDEX

3

1

34

7435

34

2

NONCLUSTERED INDEX

3

2

1

34

34

9. This just shows a clustered index with a non clustered PK integer and the datetime.

Both secondary indexes gain +17 bytes per entry; The only interest is that the integer tree levels only gai= n 7 bytes.

Index

id

index_typec<= o:p>

Index

depth<= o:p>

Index

level<= o:p>

Page

count<= o:p>

Record

count<= o:p>

avg_record

size_in

bytes<= o:p>

1

CLUSTERED INDEX

4

0

153847

2000000

579

1

CLUSTERED INDEX

4

1

477

153847

23

1

CLUSTERED INDEX

4

2

2

477

23

1

CLUSTERED INDEX

4

3

1

2

23

2

NONCLUSTERED INDEX

3

0

6689

2000000

25

2

NONCLUSTERED INDEX

3

1

28

6689

31

2

NONCLUSTERED INDEX

3

2

1

28

31

3

NONCLUSTERED INDEX

3

0

5682

2000000

21

3

NONCLUSTERED INDEX