Las estadísticas en los índices de nuestras tablas son uno de los pilares más importantes para tener una buena performance en nuestro motor.
Si las tenemos desactualizadas podemos sufrir varios problemas de rendimiento y a la larga mayores consumos de recursos y dinero.
En el siguiente video te voy a mostrar
- Cómo funcionan estas estadísticas
- Que sucede si no están bien actualizadas
- Cómo mirar en tus planes de ejecución si hay problemas con ellas.
Código de ejemplo utilizado
USE AdventureWorks2019
GO
DROP TABLE IF EXISTS DBO.T1
SELECT * INTO DBO.T1
FROM Sales.SalesOrderDetail
SP_HELPINDEX 'DBO.T1'
-- VEMOS LAS ESTADISTICAS DE LA TABLA
SELECT * FROM sys.stats
WHERE object_id = OBJECT_ID('T1')
-- CREAMOS INDICES CLUSTERED
CREATE CLUSTERED INDEX IX1 ON T1 ([SalesOrderDetailID])
-- VEMOS LAS ESTADISTICAS DE LA TABLA
SELECT * FROM sys.stats
WHERE object_id = OBJECT_ID('T1')
-- CONSULTAS
SELECT SalesOrderDetailID, OrderQty
FROM DBO.T1
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM DBO.T1
WHERE ProductID = 870;
-- CREAMOS INDICE
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON DBO.T1
(
[ProductID] ASC
)
-- REPETIMOS CONSULTA
SELECT SalesOrderDetailID, OrderQty
FROM DBO.T1
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM DBO.T1
WHERE ProductID = 870;
-- miramos estadisticas
SELECT sp.stats_id,
name,
filter_definition,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('dbo.t1');
-- NO ACTUALIZACION DE ESTADISTICAS ----------------
TRUNCATE TABLE T1
UPDATE STATISTICS DBO.T1
WITH FULLSCAN
-- INSERTAMOS solo los 897
INSERT INTO [dbo].[T1]
([SalesOrderID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[LineTotal]
,[rowguid]
,[ModifiedDate]
,[TOTAL])
select [SalesOrderID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[LineTotal]
,[rowguid]
,[ModifiedDate]
,[TOTAL]
from sales.SalesOrderDetail
where ProductID = 897
UPDATE STATISTICS DBO.T1
WITH FULLSCAN
-- vemos estadisticas
SELECT sp.stats_id,
name,
filter_definition,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('dbo.t1');
-- NO ACTUALIZAMOS MAS ESTADISTICAS
UPDATE STATISTICS DBO.T1
WITH FULLSCAN,NORECOMPUTE
-- INSERTAMOS <> 897
INSERT INTO [dbo].[T1]
([SalesOrderID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[LineTotal]
,[rowguid]
,[ModifiedDate]
,[TOTAL])
select [SalesOrderID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[LineTotal]
,[rowguid]
,[ModifiedDate]
,[TOTAL]
from sales.SalesOrderDetail
where ProductID <> 897
-- vemos estadisticas
SELECT sp.stats_id,
name,
filter_definition,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('dbo.t1');
-- corremos querys
SELECT SalesOrderDetailID, OrderQty
FROM DBO.T1
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM DBO.T1
WHERE ProductID = 870;
UPDATE STATISTICS DBO.T1
WITH FULLSCAN
SELECT sp.stats_id,
name,
filter_definition,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('dbo.t1');
SELECT SalesOrderDetailID, OrderQty
FROM DBO.T1
WHERE ProductID = 897;
SELECT SalesOrderDetailID, OrderQty
FROM DBO.T1
WHERE ProductID = 870;
DROP TABLE IF EXISTS T1
