Índices en SQL Server: columnstore vs rowstore para analytics y OLTP

Los índices son la principal palanca de rendimiento en SQL Server. La elección entre rowstore (B-tree tradicional) y columnstore puede significar la diferencia entre una consulta de 30 segundos y 300 milisegundos.

Rowstore vs Columnstore

Rowstore (B-tree):
  - Almacena datos fila a fila
  - Ideal para OLTP: inserciones/updates individuales, lookups por PK
  - Pésimo para agregaciones en tablas grandes

Columnstore:
  - Almacena datos columna a columna (compresión 10x típica)
  - Ideal para OLAP/DW: agregaciones sobre millones de filas
  - Batch mode processing — procesa 900 filas a la vez

Crear un Clustered Columnstore Index

-- Tabla de hechos en el DW — columnstore es la elección correcta
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

-- Verificar la compresión conseguida
SELECT
    i.name,
    p.rows,
    SUM(a.total_pages) * 8 / 1024.0 AS size_mb,
    SUM(a.data_pages)  * 8 / 1024.0 AS data_mb
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.object_id = OBJECT_ID('dbo.FactSales')
GROUP BY i.name, p.rows;

Nonclustered Columnstore para OLTP

-- Añadir columnstore a una tabla OLTP sin eliminar el clustered rowstore
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sales_Analytics
ON dbo.Sales (fecha_venta, id_producto, importe, cantidad)
WHERE fecha_venta >= '2023-01-01';  -- índice filtrado

Índices filtrados

-- Solo filas activas — índice más pequeño y rápido
CREATE NONCLUSTERED INDEX IX_Clientes_Activos
ON dbo.Clientes (nombre, email)
WHERE estado = 'Activo';

Identificar índices que faltan

SELECT
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_' + REPLACE(mid.equality_columns, ', ', '_') AS suggested_index,
    mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups  mig  ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid  ON mig.index_handle  = mid.index_handle
ORDER BY improvement_measure DESC;

Conclusión

Use clustered columnstore en todas las tablas de hechos del data warehouse. Para OLTP puro, use rowstore con índices cuidadosamente elegidos. sys.dm_db_missing_index_* es su mejor aliado para encontrar oportunidades rápidas de optimización.