Herramientas de monitoreo de SQL Server

En la Parte 1 de esta serie, cubrimos una serie de características que SQL Server proporciona para optimizar su uso de recursos. Por ejemplo, puede ajustar la forma en que se compilan los lotes de consultas, configurar la caché del búfer para que se vacíe a diferentes intervalos y crear tablas optimizadas para la memoria. Para aprovechar al máximo estas funciones, debe obtener información en tiempo real sobre el estado y el rendimiento de SQL Server. Aquí examinaremos las herramientas de monitoreo de SQL Server dentro de las funciones integradas y las aplicaciones de uso común, incluidas:

• Consultas T-SQL: Utilice el lenguaje de consultas de SQL Server para recopilar datos recopilados internamente
• SQL Server Management Studio (SSMS): Obtenga vistas en tiempo real de su sistema, diagramas de consultas T-SQL e informes bajo demanda
• Monitor de rendimiento: Correlacionar métricas de SQL Server con datos de sus hosts de Windows

Las herramientas de supervisión de SQL Server pueden ayudarlo a acceder a las métricas que analizamos en la Parte 1. Algunas de estas herramientas informan de las mismas métricas, y es posible que prefiera una interfaz sobre la otra. Por ejemplo, puede usar el Monitor de rendimiento o las consultas T-SQL para obtener métricas de los contadores de rendimiento de SQL Server. Puede optar por los gráficos en tiempo real de los primeros frente a la capacidad de escribir los segundos. En esta publicación, explicaremos cómo usar las herramientas de monitoreo de SQL Server para obtener una vista completa de su infraestructura de base de datos.

Mediante consultas de T-SQL

Puede supervisar SQL Server utilizando su propio lenguaje de consultas, T-SQL, para recopilar métricas. Las consultas de T-SQL son flexibles. Puede ejecutarlos con una herramienta de administración gráfica como SSMS o una utilidad de línea de comandos como sqlcmd. Y dado que se ejecutan y devuelven datos como cualquier otra consulta de base de datos, puede incorporarlos fácilmente en una solución de monitoreo automatizado de cosecha propia. En esta sección, mostraremos cómo las consultas T-SQL pueden ser una herramienta poderosa para la supervisión de SQL Server, ya sea que utilice vistas de administración dinámica, funciones integradas, procedimientos almacenados o conjuntos de recopilación de datos del sistema.

Vistas de administración dinámica

SQL Server realiza un seguimiento de los datos sobre su propio estado y rendimiento, y hace que esta información esté disponible a través de las vistas de administración dinámica (DMV). Debido a que los DMV se muestran como tablas virtuales, se prestan tanto a consultas ad hoc como automatizadas. Algunos DMV devuelven el valor actual de una métrica o configuración (p. ej., el tamaño actual del registro de transacciones en megabytes). Otros, en particular las métricas de tasas dentro de los contadores de rendimiento del DMV, miden valores a intervalos regulares y toman la diferencia entre muestras consecutivas (por ejemplo, solicitudes de lotes por segundo). Puede leer sobre DMV específicos en la documentación de SQL Server.

Si está supervisando SQL Server con vistas de administración dinámica, es probable que desee consultar los contadores de rendimiento DMV, sys.dm_os_performance_counters. Cada objeto de rendimiento de SQL Server (que puede representar cualquier cosa, desde una base de datos hasta la caché del plan) mantiene su propio conjunto de contadores de rendimiento, que se asignan a muchas de las categorías de métricas discutidas en la Parte 1: estadísticas SQL, bloqueos y el administrador de búfer.

Por ejemplo, puede consultar los contadores de rendimiento del DMV para ver los datos del objeto de rendimiento del administrador de búfer y limitar los resultados a métricas con valores distintos de cero:

SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name="SQLServer:Buffer Manager" AND cntr_value != 0;GO

Obtendrás un resultado similar a este (¡pero con muchas más filas!):

object_name counter_name cntr_value---------------------------------------------------------------------------------------SQLServer:Buffer Manager Buffer cache hit ratio 30SQLServer:Buffer Manager Buffer cache hit ratio base 30SQLServer:Buffer Manager Page lookups/sec 11091500SQLServer:Buffer Manager Database pages 5819SQLServer:Buffer Manager Target pages 212992SQLServer:Buffer Manager Integral Controller Slope 10

Puede encontrar una lista de las vistas de administración dinámica disponibles organizadas por categoría aquí. Dentro de cada categoría, las vistas son diversas, mientras que algunas calculan métricas de rendimiento y números de salida, otras informan nombres y propiedades. Puede enumerar los nodos de SQL Server en un clúster, recuperar datos de uso de índices y obtener estadísticas para sus planes de ejecución, como el tiempo de finalización y el uso de recursos. Y dado que las vistas de administración dinámica se comportan como tablas, puede usar funciones integradas para agregar y clasificar los datos.

Es importante verificar la documentación de cualquier DMV que planee usar. Una de las razones es que los DMV requieren permisos diferentes, y la documentación de cada DMV explica el nivel requerido. Otra razón es que los DMV pueden contener miles de filas y las columnas pueden cambiar con nuevas versiones de SQL Server.

Funciones integradas

SQL Server también incluye funciones integradas para ayudarle a acceder a la información del sistema. A diferencia de las vistas de administración dinámica, que devuelven datos en forma de tablas virtuales, las funciones integradas devuelven los datos del sistema como valores numéricos únicos, calculados desde la última vez que se inició el servidor. Puede llamar a cada función incorporada como argumento de una instrucción SELECT. Por ejemplo, puede usar la función incorporada @@connections para devolver la suma de conexiones exitosas y fallidas a lo largo del tiempo:

SELECT @@connections AS "Total Connections";GO

Recibirá una salida similar a:

Total Connections----------------- 1571

Las funciones integradas a veces se parecen a las vistas de administración dinámicas. @@connections es similar al contador User Connections dentro del objeto estadísticas generales. Pero mientras User Connections rastrea el número de usuarios conectados actualmente, @@connections aumenta cada vez que un usuario intenta iniciar sesión (incluso si el intento no tiene éxito).

La única función de estadísticas del sistema incorporada que no devuelve un solo valor numérico es sys.fn_virtualfilestats, que devuelve una tabla con datos en E/S de disco para archivos de base de datos y produce la misma información que la vista de administración dinámica sys.dm_io_virtual_file_stats.

Procedimientos almacenados en el sistema

Nota del editor: SQL Server utiliza los términos «maestro» para identificar su base de datos principal. Datadog no utiliza este término.

Otra característica incorporada que puede usar para consultar métricas es el procedimiento almacenado del sistema. La mayoría de los procedimientos almacenados ayudan con tareas administrativas, como adjuntar una base de datos o agregar un inicio de sesión, pero algunos procedimientos almacenados informan métricas. Por ejemplo, sp_spaceused mide el consumo de disco dentro de una base de datos. Llama a procedimientos almacenados del sistema con instrucciones EXEC en lugar de SELECT. Este comando llama al procedimiento almacenado sp_spaceused, que devolverá la información de uso del disco como dos conjuntos de resultados (es decir, dos filas de tabla, cada fila con columnas diferentes):

EXEC sp_spaceused;GO

La salida tendrá un formato similar al siguiente:

database_name database_size unallocated space--------------- ----------------- ---------------------master 6.00 MB 0.52 MBreserved data index_size unused--------------- ----------------- ------------------ ------------3568 KB 1536 KB 1600 KB 432 KB

Conjuntos de recopilación de datos del sistema

Si utiliza consultas de T-SQL para recopilar métricas de SQL Server y desea poder almacenar los datos y generar informes, puede considerar usar los conjuntos de recopilación de SQL Server. Un conjunto de colecciones extrae datos de una variedad de comandos de informes y vistas de administración dinámica, y envía los datos a una base de datos dedicada llamada Almacén de datos de administración.

El proceso se basa en los servicios de integración de SQL Server para automatizar la tarea de consultar la base de datos y escribir los resultados en el Almacén de datos de administración.

Por ejemplo, a partir de SQL Server 2008, el conjunto de colección de uso de discos consulta la vista de administración dinámica sys.dm_io_virtual_file_stat y otras vistas como sys.partitions y sys.allocation_units. También puede crear un conjunto de colecciones personalizado que agrupe una secuencia de consultas T-SQL en un trabajo periódico que se ejecute en segundo plano. Puede obtener más información sobre la configuración del Almacén de Gestión de datos aquí.

SQL Server Management Studio

SQL Server Management Studio (SSMS) es un entorno gráfico que le ayuda a supervisar su sistema de varias maneras:

• Estadísticas en vivo en el Monitor de actividad
• Un mapa rico en datos de una consulta determinada
• Informes que combinan tablas, gráficos y texto en un formato fácil de imprimir

Para usar SSM, deberá descargarlo en uno de sus hosts, abrir el instalador y seguir las indicaciones. El software puede supervisar instancias remotas de SQL Server, incluidas las que se ejecutan en Linux. Para conectarse a un host, vaya al menú «Archivo» y haga clic en «Conectar explorador de objetos».»En el diálogo que sigue, especifique el host y el puerto en el campo «Nombre del servidor», en el formato 0.0.0.0,0000 (note la coma). Seleccione » Autenticación de SQL Server «en el menú desplegable» Autenticación»y rellene el nombre de usuario («Inicio de sesión») y la contraseña.

Si se ha conectado correctamente, verá que la ventana «Explorador de objetos» se rellena con un árbol de archivos que muestra los componentes de su instancia de SQL Server, incluidas las bases de datos. A continuación, podrá supervisar su instancia con las funciones que se muestran a continuación.

Monitor de actividad

El Monitor de actividad permite ver métricas de SQL Server en tiempo real, con una galería de gráficos, una visión general de los procesos y estadísticas sobre sus consultas. Si ya está utilizando SSM para tareas de administración, como configurar grupos de recursos o crear tablas, el Monitor de actividad es fácil de agregar a su flujo de trabajo. Para usar el Monitor de actividad, escriba «Ctrl-Alt-A» o haga clic en el icono dentro de la barra de herramientas de SSMS.

Puede usar el Monitor de actividad para obtener información en tiempo real sobre la demanda de su instancia de SQL Server. La sección «Descripción general» muestra cuatro gráficos que muestran métricas de trabajo y recursos en tiempo real. De forma predeterminada, estas métricas se actualizan cada 10 segundos, pero puede actualizar el intervalo de actualización haciendo clic con el botón derecho en el panel «Información general». Si bien el intervalo de actualización puede ser tan frecuente como una vez por segundo, esto viene con el costo de rendimiento de consultas de base de datos más frecuentes.

El panel «Consultas caras recientes» dentro del Monitor de actividad puede ayudarlo a proporcionar la información que necesita para hacer que sus consultas sean más eficientes. Aquí encontrará métricas relacionadas con las consultas, como ejecuciones por minuto, lecturas físicas por segundo y el número de duplicados de un plan de ejecución dentro de la caché. Si un único plan de ejecución tiene un gran número de duplicados o ejecuciones por minuto, es posible que pueda aumentar el rendimiento mediante el uso de sugerencias de consulta, como se explica en la Parte 1.

El Monitor de actividad proporciona una visión general de alto nivel conveniente de su base de datos, pero tiene sus límites. Por un lado, no puedes ajustar el tamaño de los gráficos ni las métricas que muestran. Tampoco se puede cambiar la forma en que el Monitor de actividad agrega sus estadísticas para el rendimiento de las consultas o para ver los datos más allá de la ventana de visualización predeterminada.

Visualización de consultas

Los SSM pueden ayudarlo a optimizar el rendimiento de las consultas al permitirle visualizar cómo SQL Server ejecuta sus planes de consultas y mostrarle el uso de recursos asociado con la ejecución de cada paso de un plan de consultas. Como comentamos en la Parte 1, SQL Server compila lotes de sentencias T-SQL mediante el uso de un optimizador automático para transformar el lote en un plan de ejecución. Puede inspeccionar un plan de ejecución en SSM como un diagrama de pasos computacionales y averiguar exactamente cómo el optimizador interpretó el lote. Para visualizar una consulta, vaya al panel «Consultas caras recientes» del Monitor de actividad, haga clic con el botón derecho en una de las consultas y haga clic en «Mostrar plan de ejecución».»La vista que sigue se verá algo como esto:

Si pasa el ratón sobre un nodo dentro del diagrama, puede ver una breve explicación del paso que representa el nodo, así como una lectura rápida del «Costo estimado del operador del nodo».»Este valor es calculado por el optimizador de SQL Server al ejecutar la consulta. Dado que el proceso de optimización es automático, esto le brinda una forma de verificar que sus lotes se hayan compilado según lo previsto. Y como cada paso del plan de ejecución se clasifica por costo, puede ver en qué pasos debe centrarse si desea aumentar el rendimiento.

En este ejemplo, podemos ver que Compute Scalar (convertir una cadena en un flotador) es mínimo, con costo cero en el plan de ejecución. La operación más costosa es una técnica de optimización, la cola de mesa, que copia filas en una mesa temporal oculta.

Reports

SSMS ofrece 20 informes estándar que proporcionan un estudio a gran altitud de la implementación de SQL Server, desde el uso de recursos de la base de datos hasta datos históricos sobre cambios de esquema y consistencia de la base de datos. Puede encontrar un desglose detallado de los informes aquí.

Los informes están fijos en el diseño y el contenido: muestran los datos disponibles en el momento en que crea el informe, en lugar de actualizarlos en tiempo real. La interactividad en los informes estándar es limitada. Puede ordenar algunas tablas por columna y expandir otras cuando la información está anidada. El diseño fijo facilita la creación de impresiones o documentos (PDF, Word y Excel).

En 2017, Microsoft agregó el informe Panel de rendimiento, que muestra el uso de la CPU, el recuento actual de sesiones de usuario y otra información del sistema para las instancias de SQL Server.

Para generar un informe, haga clic con el botón derecho en el nombre de una base de datos en el Explorador de objetos, pase el ratón sobre «Informes», luego sobre «Informes estándar» y seleccione un informe en el menú.

Si no puede encontrar la vista que necesita en los informes estándar de SSMS, puede crear un informe personalizado. Los informes personalizados se escriben en el Lenguaje de definición de informes (RDL), una extensión de XML. Después de especificar la estructura de un informe personalizado, puede rellenarlo desde el menú «Informes» haciendo clic en «Informes personalizados».»Estos informes permanecen separados de la lista de informes estándar.

También vale la pena señalar que Microsoft ha desarrollado varias herramientas para crear informes gráficos que van más allá de la funcionalidad de los SSM. Power BI puede visualizar datos de varias fuentes, incluido SQL Server, y viene con un conjunto de herramientas de edición visual más completo. SQL Server Reporting Services (SSRS) es una herramienta gráfica de informes diseñada para SQL Server que puede generar informes paginados listos para PDF, así como visualizaciones de datos para dispositivos móviles y la web.

Monitor de rendimiento

El monitor de rendimiento de Windows le ayuda a visualizar el uso de recursos a nivel de sistema desde sus hosts de Windows y le permite correlacionar estas métricas con contadores de rendimiento de SQL Server en gráficos de series de tiempo.

El monitor de rendimiento está integrado en el sistema operativo Windows. Para usarlo, abra la ventana Ejecutar en el menú Inicio e ingrese el nombre del programa perfmon. Aparecerá un gráfico en tiempo real en el árbol de navegación en «Herramientas de monitoreo».»A continuación, puede seleccionar contadores de rendimiento de SQL Server y métricas de recursos del sistema que desee trazar, y utilizar las opciones para diseñar sus gráficos.

Herramientas de monitoreo de SQL Server en tiempo real más ricas

En esta publicación, mostramos cómo usar las herramientas de monitoreo de SQL Server y las funciones integradas para generar descripciones generales en tiempo real de sus bases de datos, así como para obtener datos detallados bajo demanda sobre el estado y el rendimiento de SQL Server.

En la siguiente parte de esta serie, le mostraremos cómo usar Datadog para recopilar, graficar y alertar sobre métricas de SQL Server históricas y en tiempo real. También le mostraremos cómo configurar paneles con visualizaciones de arrastrar y soltar y correlacionar métricas de SQL Server con datos de toda la pila.