Tag Archives: indexes

Index Fragmentation in SQL Server 2005

Index fragmentation is a phenomenon where the index contents are scattered. Normally the contents are in contiguous fashion which helps in fast retrieval of the underlying data. When the indexes are fragmented the data access becomes time consuming because of the scattered data that needs to be searched and read.

Continue reading Index Fragmentation in SQL Server 2005

Moving Index to a separate Filegroup

To improve the query performance we decided to have separate filegroup for indexes. We decided to move the indexes of very large tables indexes to the new filegroup. Our intention was to separate the index from the data i.e. have the table data (Clustered index) in one filegroup and Nonclustered indexes in another separate filegroup to improve I/O.

Continue reading Moving Index to a separate Filegroup

Indexes

Indexes are used to speedup data access in the database. By using them one can quickly find the data in a table without having to read all the data from the tables.

The index structure resembles an inverse tree similar to a directory structure. This tree begins with the 1st page of an index which is the root node which contains pointers to other pages in the index. Then comes the intermediate node or branch node which also contains pointers to leaf nodes or other branch nodes. The leaf node is the lowest level page in an index and it contains either an Row identifier (RowId) that points to the actual data in a table or it may even contain a clustering key itself.

Continue reading Indexes