@
leyendo...
Tecnología

Capturando datos de diagnóstico y de información de salud en SQL Server 2012

En SQL Server 2012 unicamente, existe el procedimiento almacenado sp_server_diagnostics, el cual permite capturar datos de diagnóstico y de información de salud de la plataforma de base de datos.

El procedimiento almacenado retorna siempre 5 registros con los siguientes campos:

  • create_time: tiempo de creación del registro, el cual es el mismo para cada registro del conjunto resultado.
  • component_type: indica si el registro contiene datos para una instancia o para componentes del nuevo esquema de alta disponibilidad de SQL Server 2012.
  • component_name: indica el nombre del componente involucrado, del cual hablaremos más adelante, o del nombre del grupo “Always On”.
  • state: indica el estado de salud del componente. Puede ser 0, 1, 2 o 3.
  • state_desc: describe el estado de salud de acuerdo con el indicador del estado de salud. Puede ser “Unknown”, “Clean”, “Warning”, “Error”, en correspondencia ordenada con el estado.
  • data: un conjunto de datos, en esquema XML, que representa los datos que se recopilan específicos al componente.

En nombre del componente,existen los siguientes valores:

  • system: recolecta datos del sistema en general como utilización de CPU, páginas, “dumps” y violaciones de acceso, entre otros datos.
  • resource: recolecta datos desde una perspectiva de recursos, particularmente para memoria virtual y física, y otros objetos de memoria.
  • query_processing: recolecta datos desde una perspectiva de procesamiento de queries particularmente en “worker threads”, tareas, tipos de esperas y bloqueos.
  • io_subsystem: recolecta datos del subsistema de E/S. Adicionalmente, produce un resultado de limpieza o de atención para la salud de dicho subsistema.
  • events: recolecta datos sobre eventos del servidor.
  • nombre del “availability group”: recolecta datos para dicho grupo y relacionados con aspectos de alta disponibilidad.

Ahora bien, el procedimiento almacenado permite su ejecución repetitiva en intervalos de segundos, mínimo de 5 que son los que necesita para recopilar los datos. Sin embargo, si no se da un valor o se da un valor igual a 0, el procedimiento almacenado corre una sola vez. Si por el contrario, se da un valor menor a 5, arroja un error. Recuerden que si dan un valor de 5 o más, deben explicitamente detener la ejecución del procedimiento almacenado.

Una ejecución se ve de esta manera:

image 

Más en detalle, solo un conjunto resultado:

image

Si se dan cuenta, en la columna “data” los resultados vienen en formato XML. Así que para hacer un parse de éstos, se puede crear una tabla o un archivo dado el caso que no se tenga acceso al servidor. Aquí trataremos el escenario de la tabla, particularmente en la TEMPDB y para una única ejecución, pero se puede persistir para posteriores análisis en una base de datos particular y para múltiples ejecuciones, cosa que me quedará como tarea para ver a futuro como hacerlo y capturar información para comparar.

De la documentación en línea y de este post en el blog “The Baking DBA”, adapté el ejemplo y creo entonces la tabla temporal:

–tarda 5 segundos y solo es valido en SQL Server 2012…
IF OBJECT_ID(‘TEMPDB..##SpServerDiagnosticsResult’) IS NULL
BEGIN
CREATE TABLE ##SpServerDiagnosticsResult
(
rowId int IDENTITY PRIMARY KEY,
create_time DateTime,
component_type sysname,
component_name sysname,
state_id int,
state_desc sysname,
data varchar(max)
)

INSERT INTO ##SpServerDiagnosticsResult
EXEC sys.sp_server_diagnostics
END

 

La anterior tabla guarda una sola ejecución del procedimiento almacenado y agrega el registro como llave primaria mediante un campo de tipo IDENTITY.

Luego, para sacar la información de la memoria, corro este query:

–Memory Info
SELECT a.b.value(‘@description’,’varchar(100)’) AS descript, a.b.value(‘@value’,’bigint’) AS val FROM
(
SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult
)aaa
CROSS APPLY xml_data.nodes(‘/resource/memoryReport/*’) a(b)

 

Ya lo que queda es analizar muy bien el campo data para cada una de las 5 ocurrencias, o 6 si se corre en un grupo de alta disponibilidad “Always On”. Por ahora, les dejo de tarea lo anterior, cosa que yo haré más adelante y les compartiré los diferentes resultados.

Como dije antes, sigo analizando a ver como insertar en una tabla varios registros identificados por ejecución y luego hacer varios análisis.

Saludos.

Comentarios

2 comentarios en “Capturando datos de diagnóstico y de información de salud en SQL Server 2012

  1. Guillermo es una gran forma de poder ver la salud de nuestros Servidores SQL Server 2012, yo eh intentado lo siguiente espero sea una alternativa de solución.

    Primero creo la tabla:
    CREATE TABLE t1
    (
    rowId int IDENTITY PRIMARY KEY,
    create_time DateTime,
    component_type sysname,
    component_name sysname,
    state_id int,
    state_desc sysname,
    data varchar(max)
    )

    Luego creo el SP
    create procedure sp_evalue
    AS
    INSERT INTO t1
    EXEC sys.sp_server_diagnostics
    go

    Y por último creo un Job que me permita ejecutar cada 3 minutos el SP de esa manera voy almacenando información de manera periódica en una tabla.

    Solo me falta evaluar analizar el tema del campo data para ver los archivos XML.

    Saludos cordiales.
    Germán Cayo.

    Publicado por germancayo | abril 11, 2013, 11:08 AM
    • Gracias por el comentario y por el gran aporte, Germán. Lo tendré en cuenta para lo que quiero realizar de tener más de un conjunto de datos.

      ¡Saludos!

      Publicado por Guillermo Taylor | abril 11, 2013, 11:18 AM

Categorías

Archivos

A %d blogueros les gusta esto: