SQL Index Fragmentation

SQL Indexing

Indexes are applied to tables to better performance in query execution, instead of the database engine searching through each and every row for the queried data, indexing provides an indexed subset of data that can be queried to get the required rows.

For example: Searching for all the occurrences of the phrase "I like that" in a book, you could slowly and painstakingly search through each and every page for the phrase, but if the book had an index with the page numbers associated with the phrase you would know exactly which pages to go to...

Querying or searching can be done by searching through all the rows known as table scan or index seek which are the indexed records.

There are two types of indexes namely:

• Clustered
• Non-clustered

Data usage:

EXEC sp_spaceused MyTable

List of indexes:

EXEC sp_helpindex MyTable

Index Fragmentation

Indexes increase performance substantially, but if they become too fragmented this can hamper this performance.

When indexes are created they are stored sequentially in order but as modifications to records are made (INSERT, UPDATE, DELETE commands) the new indexes to be stored after these modifications are stored elsewhere which are then not in sequence with the original indexes anymore and results in index fragmentation. This causes random I/O access to be used to retrieve the fragmented indexes instead of faster sequential I/O access which hinders performance.

Re-indexing can be performed on the database to rectify this:

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

GO

EXEC sp_updatestats

GO

This was just a brief overview of how to tweak SQL data retrieval and how to maintain it with re-indexing, till next time...