Y bien amigos, otra vez tengo el placer de ponerme en contacto con Uds...
En esta oportunidad quiero hablarles de un tema fundamental para el mejor funcionamiento de nuestro entorno... ESTADISTICAS...
Me atrevería a afirmar que LAS ESTADISCTAS son tan importantes como los índices a la hora de darle a nuestro motor de base de datos las mejores herramientas para que elija el plan de ejecución óptimo, ese que permita que nuestros tiempos de respuesta sean la envidia de otros quienes no leen este post ;)
Encontrarán en este artículo toda la teoría y los scripts necesarios para que sus estadísticas estén al % 100 de sus posibilidades
Sin más, "go down to business"... cualquier duda estoy para responder sus preguntas.
1) QUE SON LAS
ESTADISTICAS?
Son objetos
utilizados por el Optimizador de Consultas del Sql Server para entender la
cantidad y distribución de la data dentro una o varias columnas y de esa forma
tomar la mejor elección dentro del mismo.
Hay dos tipos de
estadísticas:
Index Statics: que son creadas
automáticamente cuando se crea cualquier tipo de idx (con el mismo nombre que
el índice)
Column Statics: son creadas manualmente
por el DBA usando el comando “CREATE STATICS” o automáticamente por el Sql
Server cuando la opción “Auto Create Statics” está seteada en “True”.-
2) MANTENIMIENTO DE ESTADISTICAS
Cuando la data en una BD cambia las
estadísticas pueden quedar
desactualizadas y hacer que los planes de ejecución no tomen las mejores
decisiones.
Si hay una
diferencia considerable en el “Actual Number of Rows” y el “Estimated Number of
Rows” cuando examinamos un plan de ejecución, seguramente debemos actualizar
estadísticas.
Mantener “Auto Create Statics”
enabled: esta opción le permite
al SQL crear automáticamente estadísticas cuando para columnas no indexadas no existen estadísticas previamente creadas y
el Sql las necesita para un Join o un Where.
La estadísticas creadas automáticamente por el SQL Server comienzan con
“_WA_” en el nombre
Mantener “Auto Update
Statics” enabled: esta opción le
permite al
SQL actualizar las estadísticas cuando considera que están
desactualizadas.
Hay tres condiciones que pueden desencadenar la actualización
automática de
estadísticas de parte del Sql Server:
-- Cuando la tabla no tiene registros y
al menos suma uno o más
-- Cuando la tabla tiene menos de 500 registros
y los mismos se incrementan en
500 o más desde el último udpate.
-- Cuando la tabla tiene más de 500 registros y
su valor se incrementa en un % 20
de su tamaño.
3) PENSAR EN UNA RUTINA DE MANTEMIENTO DE
ESTADISTICAS
El preparar un job que haga un
update de estadísticas fuera de las horas pico puede
ayudar al Sql a que
reduzca la necesidad de actualizar estadísticas durante las horas pico.
La frecuencia en la que debe
correr nuestro plan estará sujeta a nuestro entorno.
Si tenemos un entorno con
actualizaciones frecuentes nuestras estadísticas van a
quedar desactualizadas con
mayor frecuencia.
También debemos tener en cuenta
que el actualizar estadísticas fuerza a
las queries
a recompilar los planes de ejecución, y eso tiene un costo.
Dentro
de la rutina de mantenimiento de estadísticas no debemos, al menos una vez
al
mes, el borrar las estadísticas “innecesarias” (luego veremos en detalle este
concepto)
4)
SCRIPTS DE MANTENIMIENTO DE
ESTADISTICAS
Consta de dos pasos a
saber:
Paso 1: Borrado
de Estadísticas Innecesarias y/o Redundantes
El tener estadísticas por demás genera una mayor sobrecarga en el
sql server a la hora de mantener las mismas.
Cuando hablamos de “estadísticas
innecesarias y/o redundantes” podemos dividir las mismas en dos casos puntuales
a saber:
-
Estadísticas automáticas creadas por el SQL
Server para resolver alguna consulta puntual que no han tenido actualización
desde su creación, lo cual nos habla de estadísticas que ya no se utilizan.
-
Estadísticas de Columnas que ya están cubiertas
por Estadísticas de Indices, siempre que compartan la misma columna leader
Script “Erase_Unnecesary_Statictis”
GO
=========================================================
--Author: Gustavo Herrera
-- Create date: Abril 2014
-- Description: Mantenimiento
de Estadístcas - Borrado de Estadísticas Innecesarias y/o Redundantes --
=========================================================
ALTER PROCEDURE [dbo].[DBA_Erase_Unnecesary_Statictis]
AS
BEGIN
SET NOCOUNT
ON;
---------------------------------- ---------------------------------------------------------------------
-- 1) BORRADO ESTADISTICAS CREADAS POR SQL SERVER Y NUNCA
UPDETEADAS
-------------------------------------------------------------------------------------------------------
-- Declaración Variables Generales --
Declare
@Table varchar(30),
@Static varchar(30),
@SQL nvarchar(MAX),
@VSentenciaBorrado
varchar(300)
-- Declaro cursor --
Declare Cur_Delete_1 Cursor
For
-- Guardo en el Cursor --
SELECT
OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) as 'Table',
st.name as 'Stat'
FROM sys.stats st with (nolock)
--
Join sys.stats_columns AS
scol (NOLOCK)
ON st.stats_id = scol.stats_id AND
st.object_id = scol.object_id
--
WHERE
STATS_DATE(st.object_id, st.stats_id) is null AND -- alguna vez actualizadas
OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)not like'sys%' and
st.name LIKE '_WA%'
--
ORDER BY
OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)
-- Abro el Cursor --
Open Cur_Delete_1
-- Lo recorro --
Fetch Next From
Cur_Delete_1
into
@Table,
@Static
--
While @@FETCH_STATUS = 0
Begin
SET @SQL = 'DROP STATISTICS ' +
@Table + '.' + @STATIC
EXEC sp_executesql @SQL
--Paso al siguiente
valor del vector
Fetch Next
From Cur_Delete_1
into
@Table,
@Static
End
-- Cierro cursor --
Close Cur_Delete_1
-- Saco cursor de memoria --
Deallocate Cur_Delete_1
----------------------------------------------------------------
-- 2) BORRADO DE ESTADISTICAS REDUNDANTES --
----------------------------------------------------------------
-- A) Guarda en una tabla temporal las Etadísticas de
Columna Autocreadas por el sql --
SELECT
a.object_id ,
a.stats_id ,
a. name ,
b.column_id,
a.auto_created
INTO #autostats
FROM sys.stats as a with (nolock)
---
left JOIN sys.stats_columns as b
ON a.object_id = b.object_id
AND a.stats_id = b.stats_id
--
WHERE
a.auto_created = 1 AND -- creada por el sql server
b.stats_column_id = 1
-- Declaro cursor --
Declare Cur_Delete_2 Cursor For
-- Guardo en el Cursor --
-- B) Comparando con la tabla ##autostats se determinan
cuales estadísticas se superponen y deben ser borradas --
SELECT
'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id)+ '].[' + OBJECT_NAME(sys.stats.object_id) + '].['+ #autostats.name + ']' as sentence
FROM sys.stats with (nolock)
----
INNER JOIN sys.stats_columns
ON
sys.stats.object_id = sys.stats_columns.object_id and -- pertencen al mismo objeto
sys.stats.stats_id = sys.stats_columns.stats_id
-- el mismo id de estadistica
---
INNER JOIN #autostats
ON
sys.stats_columns.object_id =
#autostats.object_id
and -- que sea el mismo objeto
sys.stats_columns.column_id
= #autostats.column_id -- que se la
misma columna
----
INNER JOIN sys.columns ON
sys.stats.object_id = sys.columns.object_id and -- que sea la misma
columna
sys.stats_columns.column_id = sys.columns.column_id
---
WHERE
sys.stats.auto_created =
0 AND -- que sea autocreado
sys.stats_columns.stats_column_id = 1 AND
sys.stats_columns.stats_id <>
#autostats.stats_id AND
-- compara id de
estadística
OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0
-- Abro el Cursor --
Open Cur_Delete_2
-- Lo recorro --
Fetch Next From
Cur_Delete_2
into
@VSentenciaBorrado
While @@FETCH_STATUS = 0
Begin
SET @SQL =
@vsentenciaborrado
-- print
@vsentenciaborrado
EXEC sp_executesql @SQL
--Paso al siguiente
valor del vector
Fetch Next
From Cur_Delete_2
into
@VSentenciaBorrado
End
-- Cierro vector --
Close Cur_Delete_2
-- Lo saco de memoria --
Deallocate Cur_Delete_2
END
Paso 2: UPDATE
DE LAS ESTADISTICAS EXISTENTES
Como hemos explicado en el punto
3) , es
una tarea de suma importancia portancia mantener nuestras estadísticas actualizadas.
Diría que es tan importante como mantener nuestro índices.}
Para ello hay dos formas que
explico debajo.
Mi elección claramente es
inclinarse por la más laboriosa pero efectiva de estas dos formas. Les dejo un
script listo para su utlización al final de esta breve explicación
3.1) SP_UPDATESTATS : el cual regenera todas las estadísticas
de una base de datos en base a los cambios registrados en la columna rowmodctr
de la vista sys.sysindexes.
Ventaja: Es fácil de implementar
Desventajas: Es poco preciso. Termina regenerando
estadísticas innecesariamente cuando, por ejemplo, una tabla tuvo un
insert/delete o update de un solo registro (algo que afecta la columna
rowmodctr de la vista sys.sysindexes).
Es poco
customizable, siempre regenera estadísticas tomando el mismo algoritmo el cual
no es modificable y en ocasiones no representa un muestreo válido de la tabla.
e.g:
EXEC sp_updatestats
3.2) UPDATE STATISCS: para hacer un update de las estadísticas de
una tabla/de un índice específico/de una campo de una tabla en particular.
Ventaja: Es mucho más cutomizable y efectivo que el
sp_updatestats.
Desventaja: Es de
implementación algo más compleja que el sp_updatestats
La mayor efectividad de este tipo de
update está basada en la posibilidad de elegir el % de muestreo que el motor de
base de datos tendrá en consideración a la hora de actualizar las estadísticas.
UPDATE STATICS Table Name (Stats Name) WITH FULLSCAN à
insume más tiempo y recursos pero asegura estadísticas exactas. Toma muestreo
el % 100 de la tabla
UPDATE STATICS Table Name (Stats Name) WITH SAMPLE 50 PERCENT à al usar solo el %50 como muestreo insume menos recursos y
tiempos pero las estadísticas no son tan exactas. Toma el % de muestreo que le indiquemos.
e.g:
-- Update all statistics on a table
UPDATE STATISTICS Sales.SalesOrderDetail
-- Update a specific index on a
table
UPDATE STATISTICS Sales.SalesOrderDetail
IX_SalesOrderDetail
-- Update one column on a table
specifying sample size
UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT
Script “Erase_Unnecesary_Statictis”
USE [xxxx]
GO
SET QUOTED_IDENTIFIER ON
GO
==========================================================
-- Author: Gustavo
Herrera --
-- Create date: 28/04/2014 --
-- Description: Mantenimiento
de Estadístcas - Actualización Semanal de Estadísticas
--
=========================================================
CREATE PROCEDURE [dbo].[DBA_Update_Statictis]
AS
BEGIN
SET NOCOUNT
ON;
-- Declaración de
Variables --
Declare
@Table varchar(30),
@Static varchar(30),
@SQL nvarchar(MAX),
@SampleSize varchar(20)
Set @SampleSize = ' WITH FULLSCAN'
-- Declaro cursor --
Declare Cur_Stats Cursor
For
-- Guardo en el Cursor --
SELECT
distinct
OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) as 'Table'
FROM sys.stats st WITH (nolock)
--
Join sys.stats_columns AS scol (NOLOCK)
ON st.stats_id
= scol.stats_id
AND
st.object_id = scol.object_id
WHERE
STATS_DATE(st.object_id, st.stats_id) is not null and -- que alguna vez se hayan actualizado
DATEDIFF(DAY, STATS_DATE(st.object_id, st.stats_id), GETDATE()) > 6 and --ult upd > 6 d
OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)not like'sys%' -- que no --sean objetos del sistema
ORDER BY
OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)
-- Abro el Cursor --
Open Cur_Stats
-- Lo recorro --
Fetch Next From Cur_Stats
into
@Table
--
While @@FETCH_STATUS = 0
Begin
SET @SQL = N'UPDATE STATISTICS ' +
@Table +@SampleSize
EXEC sp_executesql @SQL
--Paso al siguiente
valor del vector
Fetch Next
From Cur_Stats
into
@Table
End
-- Cierro vector --
Close Cur_Stats
-- Lo saco de memoria --
Deallocate Cur_Stats
END
GO
1 5) CONSIDERACIONES FINALES
SOBRE ESTADISTICAS
5.1) El INDEX
REBUILD hace un update de las estadísticas con Full Scan. Por lo tantono se
recomienda hacer un mantenimiento de estadísticas luego de un mantenimiento de
idx para no duplicar el trabajo.
5.2) EL INDEX
REORGANIZE hace un update de estadísticas pero sin FULL SCAN.
5.3) No confiar en
el Auto Update Statics, puesto que, como hemos visto, se desencadena luego de
que la tabla ha tendido modificaciones en un % 20 de sus registros. Si la tabla
es muy grande… pocas chances de que suceda.
5.4) Es muy
importante elegir criteriosamente % del muestreo que el Sql Server va a tomar
para hacer la recreación de las estadísticas. En tablas de un tb por ejemplo es
aconsejable que sea un FULL SCAN, de lo contrario, las estadísticas serán inexactas y el Sql Server
no tomará las decisiones correctas
5.5) Recordar que
TODOS LOS PLANES DE EJECUCION QUE UTILIZAN ESTADISTICAS SERAN REGENERADOS
CUANDO DICHAS ESTADISTICAS SEAN ACTUALIZADAS.
5.6) A priori, el
actualizar las estadísticas una vez por semana en horas de mínima concurrencia
es una buena medida para entornos con moderada a alta actualización de sus
registros.
