CODEWITHSUNDEEP
sqlsql-serverindexing
kati novikov
kati novikov

Reputation: 71

two duplicated indexes different sizes

I have two duplicated indexes with same row count and the same columns, only one is [22,488,980 rows; 672.7MB] and the other is [22,488,980 rows; 3.9GB]

How is this possible?

The columns for the index are as follows: index 1:

[3 KEYS] user_hour_log_user_id {int 4}, user_hour_log_out_type_id {tinyint 1}, user_hour_log_id {bigint 8} [1 INCLUDE] user_hour_log_out_time {datetime 8}

Reads: 764,131,409 (764,131,409 seek) Writes:3,051,570

22,488,980 rows; 672.7MB

index 2:

[3 KEYS] user_hour_log_user_id {int 4}, user_hour_log_out_type_id {tinyint 1}, user_hour_log_id {bigint 8}

Reads: 0 Writes:3,051,570

22,488,980 rows; 3.9GB

I have used sp_BlitzIndex by Brent Ozar

Upvotes: 0

Views: 155

Answers (2)

Paul Daubian
Paul Daubian

Reputation: 91

As commented in the other answer, the problem is the FILLFACTOR option set to 10 in the second index.

FILLFACTOR=10 means that only 10% of a page file will be filled, while FILLFACTOR=95 means 95% of a page will be.

A FILLFACTOR=10 option means your index will use 10 times more pages (and thus, size) that it needs. A data page file is 8KB. 10% of 8KB is 800B. One row of data of the second index is 4+1+8 = 13B, so the engine is able to write 63 rows per data page file. On the first index, it uses 95% of 8KB, so 7600B. One row is 4+1+8+8 = 21. The engine can write 361 rows/page, 5.7 times more than 63 rows. This ratio is roughly the same as 3900MB/672MB, the ratio of the sizes of the two indexes.

I wouldn't recommend in any situation a fillfactor of 10.

Imagine a bookshelf. An index is like a bookshelf: you store books (rows) in alphabetical order. FILLFACTOR=100 means that when you are tidying (rebuilding) your bookshelf, you leave no space in any bookshelf's row (the data page).

A FILLFACTOR=90 means you leave 10% of the row's space left (10% of the data page file).

A FILLFACTOR=10 means you leave 90% of the space left.

Now, why does that help us? Well, if you acquire new books (insert new rows) in the bookshelf, if you have some space left on the row, you can insert the book without moving any other book in other rows. If your FILLFACTOR=100, no luck, you'll need to make space for your book, and move other books in the previous or next row.

This is called PAGE SPLITTING and has a performance impact, because this generates extra IO. That's why it's recommended to decrease the fillfactor on indexes that you know will be frequently modified, to reduce the number of page splits.

You can read Brent Ozar's paper here to get more recommendation about fillfactor options:

https://www.brentozar.com/blitz/fill-factor/

Cheers.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271241

If these indexes are the same columns on the same table, then you are probably witnessing index fragmentation in action. There are many resources that describe this in detail (such as this one). Here I just want to give you an idea of what is happening.

When you create an index on a table, it should be built quite efficiently -- exactly the data structure needed for the table.

However, when you start inserting, deleteing, and updateing records, the index changes. In particular, values are not added at the end of the index but "in the middle". New space needs to be found, so an existing index page is split into two partially filled pages. This process contributes to index fragmentation.

So, if you defined an index on an empty table and then inserted rows, you probably have a fragmented index. If you created an index on the same columns after the table was populated, it will be a sleek, efficient index.

Upvotes: 0

Related Questions