Administering SQL Server Indexes

Overview

When you have a primary key on a table, that automatically creates a "clustered" index.

Any index beyond the primary key is called a "non-clustered" index.

Indexes can be created so that they are stored on a different filegroup, or using partitions.

Special Index Types

* Sparse column that has a filtered index

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements. See http://msdn.microsoft.com/en-us/library/cc280604.aspx

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data.

For example:

CREATE NONCLUSTERED INDEX FIProductAccessories ON Production.Product (ProductSubcategoryID, ListPrice) Include (Name) WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36; GO

* XML Index

On XML data type field. Special rules apply:

* Before you create XML index, you must have a clustered index on primary key of table

* There are 2 types of XML indexes: primary and secondary

* You can create only 1 primary index on an XML column

* There are 3 types of secondary XML indexes: PATH, VALUE and PROPERTY

* See MIcrosoft's documentation on secondary indexes: http://msdn.microsoft.com/en-us/library/bb522562.aspx

Miscellaneous notes/comments about administering SQL Server indexes:

sp_updatestats stored procedure

Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

Rebuilding or Reorganizing Indexes

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. For more information, see this Microsoft Web site.

You can remedy index fragmentation by either reorganizing an index or by rebuilding an index.

The reorganize process uses minimal system resources. Also, reorganizing is automatically performed online. The process does not hold long-term blocking locks; therefore, it will not block running queries or updates. Reorganize an index when the index is not heavily fragmented.

Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.

Online T-SQL commands:

* ALTER INDEX with the REORGANIZE clause

* ALTER INDEX with the REBUILD WITH ONLINE=On statement option.

* DBCC INDEXDEFRAG (older command that Microsoft is discontinuing) that reorganizes indexes

Offline T-SQL commands:

* DBCC DBREINDEX (older command that Microsoft is discontinuing) - rebuilds an index for a table or all indexes defined for a table

* Create Index with Drop Existing if changing an index that has an XML data type.

To Check Integrity of Indexes

DBCC CHECKDB Databasename

This command does a lot, including verifying the integrity of the indexes.

sys.dm_db_index_operational_stats

Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.