patternsqlModerate
Why SQL Server has 900 byte index size limit
Viewed 0 times
whysqlbytesizelimithasserverindex900
Problem
Why can't we create an index having size greater than 900 bytes? This question was raised in a presentation to which no one had an answer.
I can think of this way to answer this question.
Page size 8K with usable 8060
900 bytes 8 = 7200 bytes (9009 = 8100), so only 8 records can fit in a page at max. If this index is a non clustered, it would also need to store clustered index key which can again be 900 byte long
i.e.
For a single record memory required would be (900 + 900 bytes) i.e. 4 records per page can be stored.
If only 4 records can be stored in a page, IO operations overhead compared to performance gain would nullify, hence index size limit is 900 bytes.
I can think of this way to answer this question.
Page size 8K with usable 8060
900 bytes 8 = 7200 bytes (9009 = 8100), so only 8 records can fit in a page at max. If this index is a non clustered, it would also need to store clustered index key which can again be 900 byte long
i.e.
For a single record memory required would be (900 + 900 bytes) i.e. 4 records per page can be stored.
If only 4 records can be stored in a page, IO operations overhead compared to performance gain would nullify, hence index size limit is 900 bytes.
Solution
From Microsoft SQL Server 2008 Internals:
In fact, improving scalability is the primary reason for the limit to an index key of 900 bytes, or 16 columns, whichever comes first.
At 900 bytes per key, you can have a maximum of 8 index entries per page. As you decrease the maximum number of index entries per page, you vastly increase the number of levels required to store any given index. The smaller the key size, the more efficient the index, since it requires fewer pages of data.
As of SQL Server 2016, the maximum byte size for non-clustered indexes has been increased to 1,700 bytes, and 32 columns.
In fact, improving scalability is the primary reason for the limit to an index key of 900 bytes, or 16 columns, whichever comes first.
At 900 bytes per key, you can have a maximum of 8 index entries per page. As you decrease the maximum number of index entries per page, you vastly increase the number of levels required to store any given index. The smaller the key size, the more efficient the index, since it requires fewer pages of data.
As of SQL Server 2016, the maximum byte size for non-clustered indexes has been increased to 1,700 bytes, and 32 columns.
Context
StackExchange Database Administrators Q#122000, answer score: 18
Revisions (0)
No revisions yet.