EX0013 : Identify fragmented indexes that need rebuilding or re-indexing

Introduction

Index fragmentation can significantly hinder query performance in SQL Server due to the misalignment between logical and physical ordering of index pages.

Description

Index fragmentation is a common issue in SQL Server databases where the physical ordering of data pages in an index doesn’t match the expected logical ordering. This can lead to increased I/O operations as reads require accessing more pages than necessary, slowing down query execution. As indexes become highly fragmented, it impacts database efficiency, leading to slower response times for applications relying on quick data retrieval.

Example of problematic indexes due to fragmentation:

SELECT * FROM LargeDataTable WHERE IndexedColumn = 'SomeValue';

In this example, querying a large table with a fragmented index results in inefficient data access patterns. The engine reads more data than needed due to scattered data pages, exacerbating latency. Regular index maintenance is crucial to prevent such performance issues.

Degraded query performance due to increased random I/O operations.
Inefficient CPU and memory usage caused by unnecessary data page reads.

How to fix

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Name	Description	Default Value
MaximumAllowedFragmentation	Maximum allowed fragmentation for an index that to be ignored by the rule.	5
MinimumPagesCount	Minimum number of pages an index must have in order to be considered by the rule.	1000
MaximumFragmentationForReorgainizeIndex	Maximum fragmentation percent below which index reorganization will be suggested by the rule. Above this specified percent, rebuilding of the index will be suggested.	30

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Analysis Rules