Skip to content
DBAExperts
SQL Server Operations

How to update statistics and rebuild indexes in SQL Server

Over time, indexes fragment and statistics go stale, and performance drops. This periodic maintenance keeps things healthy.

  1. 1

    Measure index fragmentation

    Before acting, measure. sys.dm_db_index_physical_stats tells you the fragmentation percentage of each index.

    SELECT OBJECT_NAME(ips.object_id) AS tabla,
       i.name AS indice,
       ips.avg_fragmentation_in_percent AS frag_pct,
       ips.page_count AS paginas
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000
ORDER BY frag_pct DESC;
  2. 2

    Reorganize or rebuild based on the threshold

    Rule of thumb: under 5%, do nothing; between 5% and 30%, REORGANIZE (a light, online operation); over 30%, REBUILD (heavier, but it leaves the index like new).

    -- Fragmentacion moderada: reorganizar
ALTER INDEX IX_Ventas_Fecha ON dbo.Ventas REORGANIZE;

-- Fragmentacion alta: reconstruir
ALTER INDEX IX_Ventas_Fecha ON dbo.Ventas
REBUILD WITH (ONLINE = ON);  -- ONLINE requiere Enterprise
  3. 3

    Update the statistics

    Statistics guide the optimizer. If they are stale, it picks bad plans. REBUILD updates them automatically; after a REORGANIZE, do it separately.

    -- Todas las estadisticas de una tabla con muestreo completo
UPDATE STATISTICS dbo.Ventas WITH FULLSCAN;

-- O de toda la base
EXEC sp_updatestats;
  4. 4

    Automate with a proven solution

    Instead of reinventing maintenance, use a mature solution like Ola Hallengren's, which decides whether to reorganize or rebuild by threshold automatically.

    -- Ejemplo con los scripts de Ola Hallengren (IndexOptimize)
EXEC dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @UpdateStatistics = 'ALL';

// common mistake

REBUILD OFFLINE locks the table while it runs: do not schedule it during business hours unless you use ONLINE (Enterprise only). Rebuilding indexes generates a lot of log: in the FULL model it can bloat the log and trigger backups. Do not run aggressive maintenance daily if you do not need it; measure first.

// when it's worth an expert

A poorly tuned maintenance plan burns resources, fills the log, or blocks operations. At dba.mx we configure threshold-based maintenance in safe windows with monitoring, at a fixed price, so it performs without getting in the way.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.