Index entries are too large

Try to keep index entries as small as possible. You can create an index’s total key length up to one-third the page size. However, indexes with this key length can store very few rows per index page, and the index level may be high. This increases the number of pages to traverse from the index root to the leaf pages, and increases the amount of disk I/O needed during queries.

The following example uses values reported by sp_estspace to demonstrate how the number of index pages and leaf levels required increases with key size. It creates nonclustered indexes using 10-, 20-, and 40-character keys on a server configured for 2K pages.

create table demotable (c10 char(10), 
                        c20 char(20), 
                          c40 char(40))
create index t10 on demotable(c10)
create index t20 on demotable(c20)
create index t40 on demotable(c40)
sp_estspace demotable, 500000

Table 6-1 shows the results.

Table 6-1: Effects of key size on index size and levels

Index, key size

Leaf-level pages

Index levels

t10, 10 bytes

4311

3

t20, 20 bytes

6946

3

t40, 40 bytes

12501

4

The output shows that the indexes for the 10-column and 20-column keys each have three levels, while the 40-column key requires a fourth level.

The number of pages required is more than 50 percent higher at each level.