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_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.

Lectura recomendada por el autor: Recomendado por el Autor