Estadisticas de SQL Server, parte 2
Continuando con la revisión de las estadísticas de SQL Server, veremos ahora cuando es recomendable no habilitar la actualización automática y reemplazarla por un proceso manual. Debo aclarar que el proceso debe realizarse igual para obtener el máximo rendimiento, pero como no será responsabilidad del servidor, será nuestra responsabilidad elegir el momento y cómo se hará.
En el post anterior revisamos que son las estadísticas, cómo se componen y cuándo se generan. Veamos ahora como se actualizan.
Si recordamos la consulta para obtener las estadísticas de una tabla llamada test, y la volvemos a ejecutar obtendremos información como la siguiente:
select * from sysindexes where id = object_id('test')
id status first indid root minlen keycnt ... rowmodctr ... name ...
----------- ----------- -------------- ------ -------------- ------ ------ ... --------- ... ------------------------- ...
1043495492 2066 0x300000000100 1 0x470000000100 16 1 ... 100 ... PK_TEST ...
1043495492 8388704 NULL 2 NULL 0 1 ... 0 ... _WA_Sys_00000003_3E327A44 ...
|
Previamente mencionamos que SQL Server las actualiza automáticamente, pero ¿cuando sabe que tiene que actualizarlas?. Como no existen procesos mágicos, la única forma es darse cuenta de que la estadística ya no refleja la realidad es por que los registros han sido modificados, y para eso el servidor lleva la cuenta de cuantos registros se han modificado, insertado y eliminado. Es posible que haya nuevos datos y que se generen nuevos rangos (RANGE_HI_KEY), como también más valores repetidos (EQ_ROWS). Puede incluso que la densidad de nuestra estadística sea menor y ahora si sea útil para las búsquedas.
Una columna que no agregamos en la publicación anterior, y que es existe en sysindexes, es rowmodctr. Ésta lleva el registro de los cambios en la tabla, pero sólo para la o las columnas involucradas en la estadística, y cuando este contador llega a cierto límite, es momento de que sean actualizadas. Hagamos algunas modificaciones a la tabla entonces y volvamos a consultar por las estadísticas.
update test set apellido = 'Alvaro Vegaaaaa' where apellido = 'Alvaro Vega'
Recordemos que el cambio debe hacerse en la o las columnas involucradas. Es obvio que si cambian otras columnas, la estadística seguirá siendo representativa. Una nueva consulta a sysindexes retorna:
id status first indid root minlen keycnt ... rowmodctr ... name ...
----------- ----------- -------------- ------ -------------- ------ ------ ... --------- ... ------------------------- ...
1043495492 2066 0x300000000100 1 0x470000000100 16 1 ... 100 ... PK_TEST ...
1043495492 8388704 NULL 2 NULL 0 1 ... 1 ... _WA_Sys_00000003_3E327A44 ...
|
El límite que mencionamos recién, y que corresponde al momento en que el servidor decide actualizar las estadísticas está dado por la siguiente ecuación:
Cantidad de modificaciones > 500 + 0,2 * (cantidad de filas de la tabla)
De esta ecuación se puede concluir que mientras más grande la tabla, son necesarias más modificaciones para generar una actualización. Esto está bien ya que mientras más larga la tabla, las modificaciones afectan en menor forma la variación de los datos y las estadísticas seguirán reflejando la realidad por más tiempo. Con una base de 500 se garantiza que no haya actualizaciones muy seguido para tablas más pequeñas.
En sysindexes existe otra columna que no hemos visto hasta ahora. Esta es StatVersion, que refleja la cantidad de veces que la estadística ha sido actualizada. Después de cierta cantidad de actualizaciones (numero no conocido) , la estadística se elimina. En caso de necesitarse posteriormente, se vuelve a crear. Desde el punto de vista del rendimiento, crear un estadística de cero o actualizar una existente no es muy diferente.
Para inhabilitar la actualización automática de estadísticas, se debe configurar la base de datos de esa forma. Las únicas justificaciones para hacerlo pueden deberse a que por la naturaleza de la aplicación no son necesarias o por que el costo de la actualización es prohibitivo en un servidor con mucha carga. En este último caso se pueden actualizar manualmente en algún momento donde no haya demasiada carga como los fines de semana o un día de madrugada, aunque esto dependerá del tipo de aplicación.
Para actualizar las estadísticas manualmente se pueden ejecutar cualquiera de estas dos opciones:
- update statistics
- sp_updatestats
La diferencia principal entre estos métodos es que con el primero se puede controlar que tabla se quiere actualizar, y si se quiere para todos sus índices y estadísticas o para alguno específico, o para todos los índices o todas las estadísticas de la tabla. Además se pueden definir otros parámetros como la cantidad de filas de la tabla a revisar (numero, porcentaje, etc.) como también si se quiere utilizar el mismo valor que se utilizó la primera vez. Por otra parte, el procedimiento almacenado actualiza las estadísticas de todas las tablas de la base de datos que tengan cambios.
Un ejemplo que actualiza las estadísticas del apellido de nuestra tabla se muestra a continuación. Podrán encontrar muchas más opciones de ejecución en los libros en línea de SQL Server.
update statistics test _WA_Sys_00000003_5649C92D
Una vez ejecutado, el contador de actualizaciones (rowmodctr) vuelve a cero. Para una actualización masiva que actualiza todas las estadísticas de columnas e índices que han sufrido alguna modificación (rowmodctr > 0), ejecutamos
sp_updatestats
Por último, para la creación de estadísticas, existen instrucciones análogas a las primeras. Estas son create statistics y sp_createstats. El control de create statistics es completo comparado con sp_createstats, pero este último tiene la ventaja de que crea estadísticas en todas las columnas de todas las tablas que poseen las siguientes características:
- No son columnas computadas (formulas)
- No son de tipo text, ntext e image
- No poseen estadísticas
- No son la primera columna de un índice.
Con estas instrucciones se puede controlar de forma completa las estadísticas de SQL Server. Les recuerdo que más información podrán encontrar en los libros en línea.