El análisis de dispositivos físicos, esperas y eficiencia de índices son elementos fundamentales a la hora de hacer tuning en SQL Server. Nos van a servir para diagnosticar u optimizar una instancia. Los motivos son simples:
- No es posible mejorar o arreglar un problema de rendimiento sin conocer, medir y analizar las partes que lo forman
- Los datos están organizados en filegroups, estos en archivos y estos se almacenan en discos. Los discos suelen ser los elementos más lentos de un sistema por lo que un empeoramiento de estos redundará en decrecimiento del rendimiento del sistema
-
Esperas, son grandes desconocidas, nos pueden ayudar a identificar y cuantificar donde pierde el tiempo nuestra instancia
-
Indices, todos conocemos la importancia de los índices pero el hecho de que los tengamos implementados en nuestras bases de datos no quiere decir que se estén utilizando o sean útiles. También es importante revisar de vez en cuando los índices que nos sugiere SQL Server.
Uso de Dispositivos Físicos
Este es el primer elemento que revisamos, como hemos dicho lo que tratamos de medir es el rendimiento de los discos y procedemos a diagnosticar si esta bien o mal y recomendaciones concretas. Vayamos paso por paso:
-
Recoger Datos Periódicamente
- Realizar consultas consolidadoras que nos permitan extraer conclusiones de esos datos agregados
Recoger Datos Periódicamente
El esquema de funcionamiento para recoger datos periódicamente es el siguiente:
Como veis utilizamos para SQL Server 2000 “::fn_virtualfilestats” y para versiones iguales o mayores a SQL Server 2005 la función “sys.dm_io_virtual_file_stats”. Puesto que cada vez quedan menos instancias con SQL Server 2000 vamos a centrarnos en ver que nos aporta la dmf “sys.dm_io_virtual_file_stats”.
SYS.DM_IO_VIRTUAL_FILE_STATS
Este objeto de SQL Server nos devuelve las estadísticas de entrada/salida de los archivos de logs y de datos. Posee dos parámetros de entrada:
-
Id de base de datos
-
Id de archivo
Si no se informan se devuelven los datos de todas las bases de datos y sus archivos. . Esto más adelante veremos que tiene algunas consecuencias….
La función nos devuelve la siguiente información:
-
Id de base de datos
-
Id de archivo
-
Sample_ms, número de milisegundos desde que se inició el equipo
-
Num_of_reads, número de operaciones de lectura realizadas en el archivo
-
Num_of_bytes_read, número de bytes leído en el archivo
-
Io_stall_read_ms, tiempo en milisegundos que los usuarios han esperado a que se completen las operaciones de lectura en el archivo
-
Num_of_writes, número de operaciones de escritura realizadas en el archivo
-
Num_of_bytes_written, cantidad de bytes escrita en el archivo
-
Io_stall_write_ms, tiempo en milisegundos que los usuarios han esperado a que se completen las operaciones de escritura en el archivo
-
Io_stall, tiempo en milisegundos que los usuarios han esperado a que se completen las operaciones de entrada/salida en el archivo
-
Size_on_disk_bytes, número de bytes utilizados en el disco para este archivo
-
File_handle, identificador de archivo de Windows para este archivo
Esta dmf es acumulativa ya que almacena los datos desde la fecha de reinicio del equipo sin embargo nosotros solo capturamos variación respecto a la muestra anterior. El porque se debe a si queremos ver la actividad en un determinado periodo solo tendremos que sumar estos incrementos ya que aparte de estos datos capturamos la fecha de la muestra.
Fuente: http://msdn.microsoft.com/es-es/library/ms190326.aspx
Consolidación y Extracción de Conclusiones
Tanto para la de recolección de datos como para este proceso utilizamos Health Check que es un programa de SOLIDQ. Como podéis imaginar nos automatiza todo teniendo solo que configurarlo en el cliente una sola vez hasta el final del análisis.
Tras extraer datos suficientes Health Check lanza consultas que permiten consolidar resultados. Existe una amplia variedad de informes que podemos extraer de estos datos:
- Análisis de latencias de lectura y escritura por disco
- Análisis de latencias de lectura y escritura por base de datos
- Análisis de latencias de lectura y escritura por tipo de fichero (datos o logs)
- Análisis de latencias de lectura y escritura por disco y tipo de fichero
- Análisis de latencias de lectura y escritura por base de datos y tipo de fichero (datos o logs)
Estos tres informes base generan al menos dos más cada uno si añadimos la dimensión tiempo. Podemos conocer la distribución por horas, por días o por cualquier otra unidad de tiempo o período. A continuación podéis ver un ejemplo de ello con datos ficticios:
Estadísticas Por Drive:
Estadísticas Por Drive y Distribución de Horas:
Estadísticas por Drive y Tipo de Archivo (Log)
A menudo contrastamos estos datos con los contadores de disco físico de performance monitor los datos que sacamos de aquí, el motivo es que en performance monitor en la traza definimos una latencia de muestreo (por defecto es 15 segundos), lo cual quiere decir que se pierden muchas operaciones entre cada intervalo de muestreo. En este objeto de SQL Server captura todas las lecturas o escrituras con lo cual tenemos una base estadística mayor y mas precisa ya que al añadir la fecha de captura de la muestra podemos examinar el periodo que más nos convenga.
Esperas
SQL Server almacena las esperas y el tiempo que espera, estas esperas son de muy distinta naturaleza y en algunos casos pueden indicarnos problemas. A continuación mostramos una lista de las esperas más comunes:
-
CXPackets, esta espera es derivada del paralelismo, esto es cuando una tarea se realiza en paralelo en varias CPUs es inevitable que unos hilos terminen antes que otros. Hasta que no termina el ultimo hilo la tarea el resto tienen que esperar. La espera por este motivo son los CXPackets
-
AsyncNetworkIO, esta espera se produce cuando por ejemplo desde un PC cliente se solicita a SQL Server un informe con una cantidad de datos muy grande. Supongamos que SQL Server resuelve la consulta y tiene que devolver 10.000.000 de registros, lo más probable es que la instancia tenga que ir devolviendo esos registros poco a poco ya que el PC cliente es probable que no sea capaz de capturarlos todos de golpe. El tiempo que pasa debido a este evento es el que da nombre a esta espera
-
BackupBufferIO, tiene lugar cuando una tarea de copia de seguridad espera por datos, o espera a un búfer donde se almacenarán datos
-
TraceWrite, esta espera se produce cuando se espera al SQL Trace (Servicio de trazas) espera un búfer libre o el procesamiento de un búfer con eventos.
-
SOSSchedulerYield, tiene lugar cuando una tarea genera de forma voluntaria el programador para que se ejecuten otras tareas. Mientras que se ejecutan las otras acumula el tiempo de espera
Para recoger los datos hacemos un bucle como comentamos en el punto anterior “Uso de Dispositivos Físicos” pero en este caso con la dmv sys.dm_os_wait_stats.
SYS.DM_OS_WAIT_STATS
Esta dmv devuelve los siguientes datos:
-
Wait_type, nombre del tipo de espera
-
Waiting_tasks_count, número de esperas de este tipo de espera
-
Wait_time_ms, tiempo total de espera de este tipo en milisegundos
-
Max_wait_time_ms, tiempo de espera máximo de este tipo de espera
-
Signal_wait_time_ms, diferencia entre el momento en que se indicó el subproceso en espera y el momento en que empezó a ejecutarse.
Fuente: http://msdn.microsoft.com/es-es/library/ms179984.aspx
Tras extraer datos suficientes Health Check lanza consultas que permiten consolidar resultados y nos proporcionan información como la siguiente:
-
Distribución de las esperas por tiempo esperado
-
Distribución de las esperas por número de peticiones
Distribución de Esperas por Tiempo Esperado
Distribución de Esperas por Número de Peticiones
Con estos datos conocemos las esperas y su naturaleza, con ello podemos diagnosticar y sugerir medidas para disminuir su efecto en la instancia.
Eficiencia de Indices
La eficiencia de índices es un factor indispensable a la hora de afinar el rendimiento en una instancia de SQL Server, el razonamiento es obvio sin ellos se incrementa el uso de cpu, lecturas o duración y con un número excesivo se aumenta el uso de cpu y/o escrituras (para actualizarlos) junto con un incremento del espacio.
Como en los casos anteriores montamos un bucle para recopilar datos y utilizamos las siguientes dmvs:
Indices faltantes (Missing Index):
- Sys.dm_db_missing_index_groups, devuelve información acerca de qué índices que faltan están contenidos en un grupo concreto de índices que faltan. Digamos que esta tabla hace de nexo de unión entre las estadísticas del grupo de índices y las columnas y detalles del índice
- Sys.dm_db_missing_index_group_stats, devuelve información de resumen sobre los grupos de índices que faltan, excluidos los índices espaciales. Como podéis ver en la siguiente imagen esta dmv nos informa del impacto que tendría el índice en el sistema de ser creado (“avg_user_impact”) así como del numero de veces que podría ser usado (seek’s y scans)
- Sys.dm_db_missing_index_details, devuelve información detallada acerca de índices que faltan. Esta dmv nos va a permitir conocer los detalles del índice para poder crear la sentencia del índice, fijaos que informa de las columnas, la tabla etc.
- Sys.dm_db_missing_index_columns, devuelve información sobre las columnas de la tabla de la base de datos de un índice faltante y nos indica como participan en el índice.
Indices sobrantes:
- Sys.dm_db_index_usage_stats, devuelve recuentos de diferentes tipos de operaciones de índice y la hora en que se realizó por última vez cada uno de los tipos de operación.
Fuentes:
- http://msdn.microsoft.com/es-es/library/ms345364(v=sql.90).aspx
- http://msdn.microsoft.com/es-es/library/ms345434(v=sql.90).aspx
- http://msdn.microsoft.com/es-es/library/ms345421(v=SQL.105).aspx
- http://msdn.microsoft.com/es-es/library/ms345407(v=sql.90).aspx
- http://technet.microsoft.com/es-es/library/ms188755.aspx
Una vez hemos acaparado suficiente información Health Check analiza la información y obtenemos los siguientes informes:
- Missing Indexes, (informe de índices faltantes) este informe reporta un conjunto de índices que convendría crear en la instancia para mejorar el rendimiento. Se basa en las dmvs anteriores y se reporta el porcentaje de mejora, usos y por supuesto la sentencia para crearlos.
-
Indexes Not Used, (índices no usados), como os podéis imaginar son aquellos que no han sido utilizados en ninguna consulta y se ha actualizado su estructura debido a actualizaciones, inserciones o borrados para mantener su orden (recordemos que un índice por definición es una estructura ordenada). En este informe aparte de demostrar que no se han utilizado en consultas, indicamos las actualizaciones y la sentencias para deshabilitarlo o borrarlo). Si lo deshabilitamos, el índice no se actualizará pero seguirá ocupando espacio, si lo borramos recuperamos el espacio.
- Indexex Not Used Overlaped, (índices no usados y solapados), este es un caso especial dentro del anterior, estos índices además de no usarse están solapados con otros, esto es, existe otro que realiza su función, está duplicado:
Ejemplo 1:
Ejemplo 2:
Conclusión
Para garantizar el buen funcionamiento de nuestras instancias de SQL Server conviene revisar periódicamente el estado de nuestros dispositivos de almacenamiento, esperas e índices. Debido a que es algo que se debe realizar con un volumen de datos suficiente en SOLIDQ proveemos en nuestra aplicación Health Check una solución nada intrusiva que hemos utilizado en muchos clientes y que nos permite reportar y documentar el estado de estos ítems.
