SQL Server database index fragmentation is a problem faced by many database administrators (DBAs). It is an issue that can seriously impact database performance and response time. Poorly performing databases result in dissatisfied users and attending to them is one of a DBA’s most important tasks.
Failure to address performance problems usually results in unpleasant escalations that include input from supervisors and management. Most DBAs prefer to avoid that type of notoriety. That is why they spend a lot of time searching for ways to optimize their system’s performance. SQL Server indexes offer an inviting opportunity for performance tuning.
Learn more: Beginning Performance Tuning for SQL Servers | Free White Paper
What is a SQL Server Index?
Executing SQL queries is how information is obtained from SQL Server databases. SQL Server indexes are structures designed to improve the performance of SQL queries and theoretically should result in more timely query responses. Good indexes reduce the amount of data the SQL Server Query Optimizer needs to scan when processing a query. Poorly constructed or fragmented indexes result in degraded performance.
An index contains keys built from one or more columns of a table or view. Keys are stored in a B-tree to enable rows associated with the key values to be located quickly and efficiently. There are two basic types of SQL Server indexes.
Clustered indexes
A clustered index sorts and stores a table’s data rows based on their key values. The key values are columns that are included in the index definition. A table can only have one clustered index and is called a clustered table. This type of index allows the rows to be sorted when storing them. Tables without a clustered index are called a heap and perform unordered storage of data rows.
Nonclustered indexes
The structure of a nonclustered index is separate from a table’s data rows. It consists of index key values, called row locators, that are pointers to data rows. Row locators for a clustered table are the clustered index keys. For heaps, a row locator is a pointer to a row.
Index creation can be tricky, and using the right indexes can have a dramatic impact on database response. A poorly designed index will never produce the increased performance that the DBA or developer anticipated. But even the most well-crafted indexes will be impacted by fragmentation.
Why Indexes Become Fragmented
SQL Server index fragmentation is a naturally occurring phenomenon caused by regular database activity. Unless you have a static database that never experiences any data deletions or insertions, your SQL Server indexes will become fragmented over time. The problem can be exacerbated in systems that undergo a high degree of data modification.
Index fragmentation should not be confused with disk fragmentation which is concerned with physical data storage. SQL Server index fragmentation occurs when the logical order of pages within the index does not match their physical ordering. This happens due to the Database Engine modifying indexes during insert, update, and delete operations.
As the degree of fragmentation grows, performance is impacted as additional I/O operations are required to provide query results. Fragmented indexes can be defragmented by being reorganized or rebuilt. Reorganizing an index is an online operation while rebuilding can be performed online or offline.
Reducing Database Index Fragmentation
IDERA’s SQL Defrag Manager offers database teams an effective tool with which to automate the process of identifying and fixing index fragmentation. It eliminates the time-consuming task of manually locating and defragmenting the indexes, freeing a database team to concentrate on other activities. Team productivity is improved with this reliable and versatile database automation tool.
SQL Defrag Manager features a centralized console that allows you to configure and monitor defragmentation across your entire physical and virtual SQL Server environment. Defragmentation can be triggered based on customizable policies focusing on instances, databases, or individual indexes. New indexes are automatically added and deleted objects are removed from future operations.
Comprehensive reporting and notification capabilities are built into SQL Defrag Manager. Email alerts can be configured to alert the team when a database is being defragmented. Report on detailed metrics like fragmentation percentage, index size, and page density. You can track improvements made by defragmenting SQL Server objects and produce reports suitable for technical and executive audiences.
Using SQL Defrag Manager lets your team enjoy the benefits of better-performing databases without expending a lot of their valuable time chasing down index fragmentation. They’ll find something to do.
Learn more about IDERA’s SQL Defrag Manager: