SQL ServerVideos
447
0

Estadísticas en SQL y Performance

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 

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed