Sql-server – Why SQL Server has 900 byte index size limit

clustered-indexindexsql server

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 (900*9 = 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.

Best Answer

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.

Related Solutions

Sql-server – How to manage Primary Key length limit of 900 bytes when using hirarchyid 2 columns

Are you trying to create a key column that represents the a tree structure? If so, you could use concatenated, 32-bit hash values of each node in the tree to represent tre hierarchy.

For example, assume you wanted to represent the following:

C:\
C:\MyData\
C:\MyData\MyFolder\
C:\MyData\MyFolder\MySubFolder

You would store the following three rows with these keys:

Size = 4B:  Hash("C:") 
Size = 8B:  Hash("C:")+Hash("MyData")
Size = 12B: Hash("C:")+Hash("MyData")+Hash("MyFolder")
Size = 16B: Hash("C:")+Hash("MyData")+Hash("MyFolder")+Hash("MySubFolder")

...where + represents binary concatenation. You would then store the concatenated key in a VARBINARY(900), which leaves you space for a tree that is 900 / 4 = 225 nodes deep. Hopefully this would be enough? This can be indexed.

Note that you don't need the hash to be without collisions, as long as you can reduce the number of keys that must be searched.

MySQL InnoDB Index in swap

InnoDB caches both data pages and index pages into the InnoDB Buffer Pool

MyISAM caches index pages only. In fact, you can do something interest if you combine replication with MyISAM.

SUGGESTION #1

Since there only 6700 spoken languages on Earth, you should use SMALLINT for language_id instead of INT. Makes for a smaller table and smaller index.

SUGGESTION #2

If you use MySQL Replication, try out this crazy idea: Convert the keywords table on the Slave to MyISAM. Leave the Master's Copy of the keywords as InnoDB.

You could run this query on the Slave ONLY to convert it...

ALTER TABLE keywords ENGINE=MyISAM;

SUGGESTION #3

Once you make the keywords table MyISAM on the Slave, you could create a dedicated MyISAM Key Cache just for keywords as follows (Example: Setup 512M KeyCache for keywords):

First create a script on the Slave

cd /var/lib/mysql
echo "SET GLOBAL keywords_cache.key_buffer_size = 1024 * 1024 *512;" > InitMySQL.sql
echo "CACHE INDEX keywords IN keywords_cache;" >> InitMySQL.sql
echo "LOAD INDEX INTO CACHE keywords;" >> InitMySQL.sql

Next, create the following entry for the startup script in /etc/my.cnf on the Slave

[mysqld]
init-file=/var/lib/mysql/InitMySQL.sql

Then, just restart mysql

service mysql restart

This keeps keyword index pages away from the general MyISAM Key Cache and in a dedicated cache.

SUGGESTION #4

Perform your keyword lookups on the Slave

I hope this helps !!!