See Also: Main_Page - Database Administration - Database Design - Indexing

How Indexes Are Stored

All SQL Server data is physically organized and stored in data pages. Unlike other relational database management systems, SQL Server does not provide the option of choosing the page size for different data types or tables. All SQL Server pages are 8KB in size. When you create primary or secondary database files, SQL Server allocates pages and extents for data storage. Each page has a number, starting from 0; the number of the last page in the database is determined by the database file size. When the database file size is increased through the ALTER DATABASE statement, new data pages are appended to the end of the file. Similarly if you shrink a database, then its data pages are removed, starting from the end of the file and gradually moving to the beginning.

Eight data pages (8KB each) make up a single extent. Therefore, each extent is 64KB. SQL Server has two classes of extents: uniform and mixed. Uniform extents are dedicated to a single object. Normally SQL Server allocates multiple uniform extents for each data table. However, if a table is small, SQL Server won't allocate an entire extent for it. Instead it will allocate data pages from a mixed extent, which can be thought of as a pool of pages for small tables. Each mixed extent can be shared by multiple tables. Since each extent has eight data pages, up-to eight objects can share data pages from a single mixed extent. When you first create a table SQL Server starts by allocating a data page to it from a mixed extent. Once the table has enough data to warrant a full extent, SQL Server will allocate a uniform extent to it. Similarly if you build an index on a table that has at least eight pages SQL Server will dedicate a uniform extent for storing the index data.

A set of pages used to hold table or index data within a single partition is referred to as an allocation unit. An allocation unit can be one of three types:

• In-row data. This type holds column data with most data types, except data types for large objects.
   
• Large object (LOB) data. This type holds large data type columns, such as TEXT, NTEXT, IMAGE, VARHCAR(MAX), NVARHCAR(MAX), XML and VARBINARY(MAX).
   
• Row-overflow data. This type holds data for variable length columns that exceed the row size limit of 8060 bytes.

The Relationship Between Index Size and Page Size

A data record (or data row) within any SQL Server table is always stored on a single page. However, if the row contains large data types and all of its data won't fit on an 8KB page, then portions of the row (variable length columns, for example those with VARCHAR, VARBINARY, SQL_VARIANT data types) can be moved to data pages in row overflow data allocation unit. SQL Server maintains such row overflow data dynamically by adding a pointer to the original data page. If data in variable length columns is subsequently trimmed by an UPDATE statement and the total row size becomes less than 8060 bytes, SQL Server moves the variable length columns back to their original location. Note that the length of each column must still be less than 8000 characters; their combined length can exceed this limit. Note also, that such dynamic allocation of row overflow data comes with the performance penalty.

Normally you should try to keep the row length small enough to fit on a single page. If you have several variable length columns whose combined length will frequently exceed the 8060 byte limit, split the existing table into multiple tables that have one-to-one relationship with each other to ensure that the whole row can fit on a single data page. You cannot build a clustered index on a varying length column whose data is on row-overflow pages; however, you can include such columns as the key or non-key columns of non-clustered indexes.

Your table rows could contain large data, stored in TEXT, NTEXT, VARCHAR(MAX), VARBINARY(MAX), XML and IMAGE data types. Data rows with TEXT, NTEXT and IMAGE data type are stored outside of the normal data pages in special storage structures, allowing up to 2GB of storage. The data page contains a 16-byte pointer to the TEXT and IMAGE pages. Note that these data types are deprecated and will be removed from future versions of SQL Server. You should favor VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) columns for all new development. If you anticipate columns with TEXT, NTEXT or IMAGE data types to contain relatively small amount of data, you can advise SQL Server to attempt storing such content in row; as opposed to creating new TEXT and IMAGE pages. To do so you can use system procedure sp_tableoption with the 'text in row' option. If the data is indeed relatively small, then the storage will work the same way as it does with VARCHAR, NVARCHAR and VARBINARY data types. If the data can fit on a data page it is stored there; otherwise it is stored in row-overflow pages.

Columns built using large variable length data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML) are normally stored on the data page and handled the same way as VARCHAR, NVARCHAR and VARBINARY data types. You could use sp_tableoption system procedure with 'large value types out of row' option and advise SQL Server to manage these data types same way as TEXT, NTEXT and IMAGE data types. If you do choose this option the data page will contain a 16-byte pointer to the TEXT and IMAGE pages.

Other Things Stored in the Index

In addition to storing data, all auxiliary information used for managing free space, tracking modified extents, allocating new pages and extents and so forth is also stored in pages. The following table summarizes different types of pages used by SQL Server:

Retrieving Database Page Metadata

You can use DBCC PAGE command to retrieve meta-data about a database page. Normally you should only execute this (undocumented) DBCC command when troubleshooting any issues with Microsoft support engineers. Before executing DBCC PAGE you need to execute DBCC TRACEON (3604) in order to return the output to the screen.

Click here to learn more about DBCC commands.

More SQLServerPedia Articles on Indexes

How SQL Server Indexes Work

• Index Overview - the basics of how indexes work.
• Data Pages & Extents - about how indexes and data are stored on disk.
• Table and Record Structure - more about the mechanics of data storage.
• Effects of Data Modifications - the more indexes you have, the longer inserts/updates/delete take.
• Index Statistics - the basics of how statistics affect query execution.

Types of Indexes in SQL Server

• Included Columns - starting with SQL Server 2005, you can add fields to indexes with a smaller performance hit.
• Indexed Views - how to build indexes on top of views for more speed.
• XML Indexes Overview - how to index XML documents and fragments.
• XML Index Rules - some additional considerations and requirements for XML indexes.

Best Practices on How to Design Database Indexes

• Indexing Strategies - choosing between clustered/nonclustered, long/short, selectivity, and more.
• Best Practices for Creating Indexes - tips to start with when designing a new index strategy.
• Choosing Index Keys - how to pick which fields to use for a primary key.
• Index Selectivity and Column Order - how to pick which columns to put first, and how it affects different kinds of queries.
• Wide Index Performance - does adding more fields to an index cause it to run slower?
• Tuning Indexes - including articles on the Index Tuning Wizard and Database Tuning Advisor.

Maintaining Indexes for Top Performance

Indexes need regular maintenance in order to perform well.

• Index Maintenance Tutorial - video and script explaining how to defragment and rebuild your indexes automatically.
• Index Maintenance in SQL Server 2005 and Beyond - SQL 2005 introduced new syntax to ALTER INDEX.
• Index Maintenance Overview - analyzing fragmentation and statistics to get the best performance possible out of your indexes.