Check scoped index fragmentation

A large index participates in relevant reads and we want to know its fragmentation before considering maintenance. The review should be limited to a specific object so as not to make the diagnosis an unnecessary burden.

What we need to distinguish

Logical fragmentation measures how far physical page order departs from index order. A page is the 8 KB storage unit; scan mode determines how much detail and work the measurement uses.

Fragmentation percentage needs context: number of pages, storage type, reading pattern, and scanning mode. A high percentage in a small index is often of little importance, and measuring the entire instance may be more expensive than the problem being investigated.

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
DECLARE @Esquema SYSNAME = N'dbo';
DECLARE @Tabla SYSNAME = N'Pedido';
DECLARE @Indice SYSNAME = NULL;
DECLARE @MinimoPaginas BIGINT = 1000;
DECLARE @Modo NVARCHAR(10) = N'LIMITED';

DECLARE @IdObjeto INT = OBJECT_ID
(
    QUOTENAME(@Esquema) + N'.' + QUOTENAME(@Tabla),
    N'U'
);

IF @IdObjeto IS NULL
    THROW 50000, 'No se encontró la tabla indicada.', 1;

DECLARE @IdIndice INT =
(
    SELECT i.index_id
    FROM sys.indexes AS i
    WHERE i.object_id = @IdObjeto
      AND i.name = @Indice
);

IF @Indice IS NOT NULL AND @IdIndice IS NULL
    THROW 50001, 'No se encontró el índice indicado.', 1;

SELECT
    sch.name AS Esquema,
    t.name AS Tabla,
    i.name AS Indice,
    ips.partition_number AS Particion,
    ips.index_type_desc AS TipoIndice,
    ips.alloc_unit_type_desc AS UnidadAsignacion,
    ips.page_count AS Paginas,
    ips.avg_fragmentation_in_percent AS FragmentacionPorcentaje,
    ips.fragment_count AS Fragmentos,
    ips.avg_fragment_size_in_pages AS PaginasPromedioPorFragmento
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    @IdObjeto,
    @IdIndice,
    NULL,
    @Modo
) AS ips
INNER JOIN sys.tables AS t
    ON t.object_id = ips.object_id
INNER JOIN sys.schemas AS sch
    ON sch.schema_id = t.schema_id
INNER JOIN sys.indexes AS i
    ON i.object_id = ips.object_id
   AND i.index_id = ips.index_id
WHERE ips.index_id > 0
  AND ips.page_count >= @MinimoPaginas
ORDER BY
    ips.avg_fragmentation_in_percent DESC,
    ips.page_count DESC;
GO

Parameters or permissions

Replaces diagram and table. If @Indice is set to NULL, all indexes on that table are checked; if it contains a name, the scope is reduced to that index. Validations prevent an incorrect name from accidentally becoming a wildcard. LIMITED conducts the lightest exam. SAMPLED offers an approximation and DETAILED runs through all the pages; Don’t change modes in production without estimating the cost. The role needs permissions on the object and the database, and can acquire shared intent locks.

How to read the result

Pages allows you to size the object before interpreting the percentage. Fragments and AveragePagesPerFragment help you understand whether adjacent pages were divided into many small portions. There is no universal threshold that mandates reorganizing or rebuilding. The decision changes depending on storage, maintenance windows, reading pattern, growth and benefit measured after intervening.

What this does establish

It allows measuring the physical state of one or more indexes of a table with an explicit scope and separating large objects from striking but not very relevant percentages.

What this still does not establish

It does not prove that fragmentation causes the slowness nor does it automatically tell you what maintenance to run. It also does not evaluate memory-optimized indexes.

Next reasonable check

Relates the index to its use and to the affected queries or plans. If maintenance is approved, measure before and after readings, duration and space, and maintain an operational rollback strategy.

Search SQL Server