Buscar este blog

miércoles, 30 de abril de 2014

MANTENIMIENTO DE ESTADISTICAS (update statistics) - Tarea Impostergable Para Mejorar la Performance de Nuestras Queries

    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_UPDATESTATSel 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


            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.



Lectura recomendada por el autor: Recomendado por el Autor

9 comentarios:

  1. Excelente trabajo. Gracias.

    ResponderEliminar
  2. Impecable, una visión clara y practica

    ResponderEliminar
  3. Excelente aporte gracias mil

    ResponderEliminar
  4. Veo que es muy útil, sin embargo estoy comenzando con SQL y cuando lo ejecuto me da un error en la linea 7 "ALTER PROCEDURE [dbo].[DBA_Erase_Unnecesary_Statictis]" me podrias indicar a que se debe? Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Es un sp nuevo, por lo tanto lo correcto es CREATE PROCEDURE en lugar del ALTER

      Eliminar