En el presente artículo, presentaremos las herramientas que son nativas de Windows y SQL Server que se utilizan con mayor frecuencia para resolver problemas de rendimiento de SQL Server. La selección de la mejor herramienta para la supervisión de rendimiento depende de los objetivos de supervisión y conocimientos del código, ya que dichas herramientas proporcionan diferente información y tienen una capacidad de análisis también diferente.
SQL Server Profiler
El SQL Server Profiler es una herramienta de monitoreo que se puede utilizar para poder crear y administrar seguimientos de SQL Server para poder supervisar la actividad en las instancias de SQL Server. Estos archivos de rastreo pueden ser analizados y reproducirse luego. El Profiler posee una interfaz gráfica, que hace que sea más sencillo usarlo.
Además de las acciones para la auditoría que se producen en una instancia de SQL Server, es útil para poder resolver problemas de rendimiento de SQL Server, de tal forma que se pueda realizar un seguimiento del procedimiento almacenado y también la ejecución de consultas mostrando los que ralentizan, captar las instrucciones y los eventos que causaron la degradación del rendimiento, que pueda reproducir un conjunto específico de eventos en otro entorno para fines de prueba, etc.
Para iniciar el analizador del SQL Server:
- Abrir: Inicio, todos los programas (Windows + C para Windows 8)
- Seleccione el programa SQL Server Profiler
O
- Abra SQL Server Management Studio
- En el menú de herramientas, seleccione el SQL Server Profiler
O
- Abra el símbolo del sistema
- Escriba profiler
- Presione Enter
Ahora, usted debe crear los rastros que recopilarán la información que necesita para monitorear y poder resolver los problemas en el funcionamiento del SQL Server
Para crear un nuevo rastreo:
- En el SQL Server Profiler, abra archivo en el menú
- Seleccione nuevo rastreo
- Seleccione una instancia de SQL Server, modo de autenticación y proporcione las credenciales
- En la pestaña de selección de eventos haga clic en Mostrar todos los eventos (Show all events)
-
Seleccione todos los eventos y las columnas de evento que desea rastrear
Para poder capturar la información sobre eventos que son producidos en una base de datos, por ejemplo, los archivos de datos y de registro crecen de manera automática y los eventos de reducción automática, seleccione estos tipos de eventos en el tipo de evento de base de datos
Para poder rastrear varios errores, como el límite de CPU excedido, seleccione el siguiente evento en el tipo de Errores y Advertencias EVENT
- En la pestaña General seleccione si es que desea guardar la traza en un archivo o en una tabla
- Haga clic en el botón Ejecutar (Run)
Se capturarán todos los eventos pertenecientes a los tipos de eventos que se han seleccionado para realizar el seguimiento.
Aunque el SQL Server Profiler proporciona bastante información muy útil, algunos Administradores de bases de datos evitan su uso en un servidor de producción, debido a que puede agregar una sobrecarga significativa. Además, si no se ha configurado la propiedad para realizar la colección de seguimiento de SQL Server para recopilar solo la información que realmente necesita para el análisis, existirá una gran cantidad de datos recopilados que puede que sean difíciles de analizar.
Monitor de rendimiento de Windows
El Monitor de Rendimiento de Windows es una herramienta de monitoreo que viene con el sistema operativo Windows. Permite realizar el seguimiento de varios contadores de rendimiento y supervisa el desempeño general del sistema y la aplicación. Los valores de los contadores se muestran en gráficos en tiempo real y se pueden ser guardados en archivos de registro y utilizar para su análisis posterior.
El Monitor de rendimiento de Windows ofrece una gran variedad de contadores. El monitorear demasiados contadores afectará el rendimiento del sistema y hará difícil el análisis, por lo cual es necesario determinar primero los objetivos del monitoreo y luego escoger los valores de los contadores para poder monitorear. Un grupo de valores máximos bien seleccionado provee la suficiente información para conclusiones útiles, el diagnóstico rápido y encontrar la razón del problema, por lo tanto, es una solución eficiente de los problemas. La captura de muestras de valores de contadores colectados demasiado pequeños o incorrectos no proporcionará suficiente información para conclusiones útiles, podría ser engañosa e inducir conclusiones erróneas.
Cuando se trata de seleccionar los contadores, es necesario comprender qué representan, cuáles son los valores aceptables, si existe un umbral o el valor determinado en función de una línea de tendencia.
Para abrir el Monitor de rendimiento de Windows:
- Abra el menú de Inicio, Ejecutar (Windows + R para Windows 8), escriba perfmon, y presione Enter
- Abra el Panel de control, Sistema y seguridad, Herramientas administrativas y haga clic en Monitor de rendimiento
Para añadir un tope:
- Seleccione Agregar contador en el menú contextual, o haga clic en el icono Agregar en el menú
- Seleccione la computadora que desea monitorear
3. En la lista de contadores disponibles, seleccione el grupo de topes, por ejemplo MSSQL$2012:Buffer Manager y luego Lazy writes/sec
El monitor de rendimiento de Windows mostrará información útil en gráficos completos que se almacenan en tiempo real y se puede guardar los datos históricos durante mucho tiempo, los cuales se pueden usar para análisis posteriores. Sus desventajas son las que no puede brindar información suficiente para poder realizar un análisis profundo ni mostrar una línea de tendencia o un umbral en el gráfico.
El monitor de actividad de SQL Server
El monitor de actividad que incluye SQL Server es una característica que está disponible en SQL Server Management Studio, útil para monitorear la actividad de forma rápida y básica de SQL Server. Solo muestra información sobre los procesos de SQL Server como su efecto en el rendimiento de SQL Server.
Muestra el % de tiempo del procesador, tareas que están en espera, I/O (entradas y salidas) de base de datos y solicitudes por grupos/seg en el panel Información general; la lista de procesos que actualmente están en ejecución en la base de datos SQL en el panel Procesos; la información sobre tiempo de espera de recursos en el panel espera de recursos; una lista de todas las bases de datos en la instancia de SQL Server junto con la información sobre los archivos de base de datos, sus rutas de acceso y los nombres en el panel de archivo de datos I/O y la lista de consultas ejecutadas recientemente que utilizan la mayoría de los recursos de memoria, disco y red en el panel de consultas costosas recientes.
Para iniciar el monitor de actividad, haga clic con el botón derecho del ratón en la instancia de SQL Server en el explorador de objetos y seleccione monitor de actividad
El monitor de actividad no está hecho para mostrar datos para una investigación en profundidad, almacenar registros históricos para análisis posteriores, ni es posible supervisar parámetros adicionales.
Recopilador de datos del SQL Server
El Recopilador de datos del SQL Server es otra característica incluida dentro de SQL Server Management Studio que se puede usar para la supervisión del rendimiento de SQL Server y la solución de problemas. Está disponible en SQL Server 2008 y versiones posteriores. El Recolector de datos puede recopilar métricas de rendimiento de varias instancias del SQL Server y almacenarlas en un repositorio único. Los datos son recopilados en función de la especificación utilizada. Existen tres especificaciones integradas que brindan la recopilación automática de las métricas de rendimiento que son más comunes: uso del disco, estadísticas de consulta y actividad del servidor. Es posible crear especificaciones de recopilador de datos personalizados, pero requiere codificación. La recopilación de datos se puede realizar solamente si el SQL Server se está ejecutando y el Administrador de datos de Almacenamiento de datos está configurado.
Para abrir los informes de recopilación de datos
- En el explorador de objetos de SQL Server Management Studio, expandir la administración
- Haga clic con el botón derecho en la opción de recopilación de datos y seleccione informes
Seleccione cualquiera de los informes disponibles: historial de actividad del servidor,
historial de estadísticas de consulta
oResumen de uso de disco
Vistas de administración dinámica (DMV)
Además de las utilidades para la supervisión disponibles en Windows y SQL Server Management Studio, las vistas de administración dinámica (DMV) también pueden ser utilizadas para poder supervisar el rendimiento de SQL Server y solucionar problemas. Estas vistas son: sys. DM _ exec_sessions, sys. DM _ os_performance_counters, sys. DM _ os_memory_brokers, sys. DM _ os_memory_nodes, sys. DM _ exec_procedure_stats, sys. _ dm_os_sys_info, sys. DM _ exec_requests, sys. DM _ exec_requests, y muchas otras más.
Presentaremos solamente algunas vistas de uso común para la supervisión del rendimiento de SQL Server en este artículo.
sys.dm_exec_sessions – devuelve una fila para cada sesión que se realiza en la instancia de SQL Server consultada, junto con detalles como ser el nombre del programa que inició la sesión, el estado de la sesión, el inicio de sesión de SQL Server, varios contadores de tiempo y otros datos relacionados.
sys.dm_exec_requests – devuelve una fila para cada usuario y la solicitud del sistema que se ejecuta en la instancia de SQL Server. Para encontrar las solicitudes bloqueadas, busque las solicitudes en las que la situación del valor de la columna de estado sea “suspendido”.
sys.dm_exec_query_stats – devuelve una fila para cada instrucción de consulta en un plan de consulta que está almacenado en caché. Brinda también información sobre el tiempo de procesador promedio, mínimo, máximo y total del tiempo que ha sido utilizado por el plan, junto con otra información útil para realizar el análisis de rendimiento.
sys.dm_os_performance_counters – devuelve una fila por cada contador de rendimiento de SQL Server. Proporciona una manera rápida de averiguar los valores actuales de contadores de rendimiento. No obstante, existen cinco tipos de contadores y para asegurarse de que está interpretando correctamente los valores devueltos, debe saber cómo calcular un valor para cada tipo de contador. Es más fácil con el tipo de contador 65792, ya que no es necesario ningún cálculo. Los tipos de contadores 1073874176, 537003264, y 272696576 requieren el cálculo adicional para proporcionar el valor actual de la métrica.
Si los valores no se calculan de manera adecuada, pueden resultar confusos, engañosos y llevar a conclusiones erróneas.
Los eventos extendidos de SQL Server
Los eventos extendidos de SQL Server permiten recolectar la información necesaria para poder solucionar problemas de rendimiento de SQL Server. Permiten también poder encontrar las consultas más costosas, causas de bloqueo, interbloqueo y bloqueo, solución de problemas de uso excesivo del procesador y más.
Para poder usar eventos extendidos de supervisión del rendimiento, determina que eventos desea supervisar y crea una sesión con las opciones de SQL Server Management Studio o T-SQL.
Para crear la sesión de ReadWriteActivity para capturar datos de eventos de lecturas y escrituras de archivos completadas y escribirlas, desde el archivo de caché del registro al archivo de registro físico:
|
1 2 3 4 5 6 7 8 9 |
CREATE EVENT SESSION ReadWriteActivity ON SERVER ADD EVENT sqlserver.file_read_completed, ADD EVENT sqlserver.file_write_completed ADD TARGET package0.asynchronous_file_target (SET filename = 'c:\PerfLogs\ExtELog.xel', metadatafile = 'c:\PerfLogs\ExtELog.xem') |
Una vez creada está sesión, para poder iniciarla:
- Expandir el administrador en el Explorador de objetos
- Expandir los eventos extendidos
- Expandir sesiones
- Encuentre la sesión del evento, haga clic derecho y seleccione Iniciar
Para poder ver los datos del evento en vivo junto con sus detalles, seleccione Ver datos en vivo en el menú contextual del evento en el Explorador de objetos.
There is also the built-in system_health session started automatically at SQL Server service start up, which provides useful information for performance troubleshooting
En este artículo, presentamos las utilidades para realizar la supervisión de rendimiento de SQL Server disponibles en Windows y SQL Server. La información que proporcionan y el conocimiento necesario para utilizarlos varían. Algunos de ellos, tales como Activity Monitor, son fáciles de utilizar y no requieren de saber codificación, pero al mismo tiempo proporcionan sólo información básica útil para obtener un pantallazo general. Por otro lado, los eventos extendidos pueden supervisar y controlar diversos errores en tiempo de ejecución, cuellos de botella de recursos, bloqueos, etc. Pero para obtener el máximo provecho de ellos, la codificación es necesaria.
Tabla de contenidos
| A DBA guide to SQL Server performance troubleshooting – Part 1 – Problems and performance metrics |
| Una guía de DBA para la solución de problemas de rendimiento de SQL Server – parte 2 – utilidades de supervisión |
Ella ha estado trabajando con SQL Server desde 2005 y tiene experiencia con SQL 2000 hasta SQL 2014.
Su tema favorito de SQL Server son las recuperaciones de desastres, la auditoría y el monitoreo de desempeño.
Vea todas las entradas de Milena “Millie” Petrovic
- Una guía de DBA para la solución de problemas de rendimiento de SQL Server – parte 2 – utilización de la supervisión - May 7, 2019
- Planes de ejecución de consultas – Entendiendo y leyendo los planes - April 19, 2018
- Mala indexación de base de datos – El asesino de las consultas SQL – recomendaciones - April 18, 2018