Review age and modification of statistics

A plan estimates row quantities that are very different from those observed or changes unexpectedly. We need to know when its statistics were updated, how much it was sampled, and how many modifications the main column of the histogram records.

What we need to distinguish

A statistic summarizes the value distribution so the optimizer can estimate rows. The histogram divides values into ranges, and the sample is the subset of rows used to build that summary.

Date alone does not determine whether a statistic is appropriate. A stable table can preserve old and useful statistics; a changing distribution can degrade estimates even if the date seems recent.

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
SELECT
    sch.name AS Esquema,
    t.name AS Tabla,
    st.name AS Estadistica,
    st.auto_created AS CreadaAutomaticamente,
    st.user_created AS CreadaPorUsuario,
    st.no_recompute AS SinRecalculoAutomatico,
    st.has_filter AS TieneFiltro,
    st.filter_definition AS DefinicionFiltro,
    sp.last_updated AS UltimaActualizacion,
    sp.rows AS FilasAlActualizar,
    sp.rows_sampled AS FilasMuestreadas,
    CONVERT
    (
        DECIMAL(6, 2),
        100.0 * sp.rows_sampled / NULLIF(sp.rows, 0)
    ) AS MuestreoPorcentaje,
    sp.steps AS PasosHistograma,
    sp.modification_counter AS ModificacionesColumnaPrincipal,
    CONVERT
    (
        DECIMAL(8, 2),
        100.0 * sp.modification_counter / NULLIF(sp.rows, 0)
    ) AS ModificacionesPorcentaje
FROM sys.stats AS st
INNER JOIN sys.tables AS t
    ON t.object_id = st.object_id
INNER JOIN sys.schemas AS sch
    ON sch.schema_id = t.schema_id
OUTER APPLY sys.dm_db_stats_properties
(
    st.object_id,
    st.stats_id
) AS sp
WHERE t.is_ms_shipped = 0
ORDER BY
    sp.last_updated,
    sp.modification_counter DESC,
    sch.name,
    t.name,
    st.name;
GO

Parameters or permissions

Execute the query in the investigated database. Permissions are required to query the statistical columns, be the owner of the table, or belong to a role with sufficient permissions. LastUpdate can be NULL in a new, empty table or in filtered statistics whose predicate produced no rows. MainColumnModifications counts changes related to the first column of the statistic, not all table modifications.

How to read the result

Compares RowsOnUpdate, RowsSample, and the current volume of the table. A small percentage is not automatically incorrect: sampling may be sufficient for one distribution, but insufficient for another. SinRecalculoAutomatico deserves attention because it prevents automatic updates of that statistic. The calculated percentages serve to prioritize the review, not as a fixed maintenance threshold.

What this does establish

It allows you to find old, undersampled or cumulatively changed statistics and gather the context needed to revise a problematic estimate.

What this still does not establish

It does not prove that a statistic is causing a poor plan or determine the appropriate update method. It also does not show the histogram distribution by itself.

Next reasonable check

Compares the estimated and observed rows of retrieved execution plan. For a candidate statistic, review its columns, filter, and histogram before deciding on a controlled update.

Search SQL Server