Esta semana retomaremos el tema de mi última publicación para terminar lo que empezamos y acabar de introducir nuestros datos de rendimientos en nuestro Servidor SQL.
Hasta ahora, hemos visto cómo recopilar datos de Perfmon y los diferentes formatos en los que podemos almacenar tanto en archivos como en SQL, pero sin poner realmente ningún dato en nuestro servidore SQL.
Para poder insertar en SQL Server, tenemos una herramienta llamada relog.exe que hará el trabajo sucio por nosotros con una configuración mínima, así que dejadme que os muestre cómo hacerlo.
Antes de usar relog.exe, como creamos un DSN
Relog es una aplicación y, como tal, tiene conectarse a un servidor SQL. En este caso, la forma de conectarse es mediante un controlador ODBC, por tanto, tenemos que crear un nuevo DSN en el ordenador en el que queremos ejecutar Relog.exe, que no necesariamente tiene que ser el mismo donde está instalado nuestro servidor SQL.
Si hacemos clic en Inicio y comenzamos a escribir ODBC, se mostrarán las 2 versiones disponibles en los sistemas operativos modernos que son de 32 bits y 64 bits, debemos elegir la versión de 64 bits para que funcione.
Parece ser que relog.exe es una herramienta un poco antigua, así que el único controlador que realmente funciona es el más antiguo, pero da igual mientras funcione, así que elegimos controlador SQL Server 10.00.xxx.
Luego tenemos que configurar el nuevo DSN siguiendo el asistente, primero el nombre que usaremos más adelante al ejecutar relog.exe
Y después la seguridad, no hay gran cosa que configurar aquí.
Y luego seleccionar la base de datos donde volcar los datos, es preferible haber creado una base de datos y no dejar la que viene predeterminada [master].
Algunas otras configuraciones que no dejo las que hay por defecto.
Y finalmente probamos la conectividad, que obviamente tiene que funcionar.
Ahora ya estamos listos para ejecutar relog.exe e insertar todos esos contadores en nuestra base de datos.
Antes de usar relog.exe, como limitamos la cantidad de datos
Puede ocurrir que tengamos todo tipo de contadores (para ser honestos, acumulamos demasiados de datos), pero solo estamos interesados en un pequeño subconjunto, entonces ¿para qué cargar todo en la base de datos si no lo necesitamos? Simple, no cargues todo si no lo necesitas.
Los parámetros que podemos usar para limitar la cantidad de datos son:
- -cf, para especificar solo ciertos contadores de su archivo. Para ello necesitamos crear un archivo y escribir allí un contador por línea, podemos usar la plantilla XML para ver los contadores, y quitar las etiquetas XML
- -t, tomará un contador por cada número que coloque aquí, así que por ejemplo si tenemos 100 filas en el archivo y aquí decimos 2, obtendremos 50 filas, si decmios 4, 25 filas y así sucesivamente.
- -b and -e, estos definirán una ventana de tiempo para el contador, por lo que si solo necesitas datos de 1:53 a 2:17 a.m. cuando ha habido algún problema, puedes hacer eso también.
Usando mi plantilla XML, si solo quisiera ver los contadores de SQL, haría lo siguiente
\MSSQL$MSSQL2016:Buffer Manager\Page life expectancy \MSSQL$MSSQL2016:Buffer Node(*)\Page life expectancy \MSSQL$MSSQL2016:General Statistics\User Connections \MSSQL$MSSQL2016:Memory Manager\Memory Grants Pending \MSSQL$MSSQL2016:SQL Statistics\Batch Requests/sec \MSSQL$MSSQL2016:SQL Statistics\SQL Compilations/sec \MSSQL$MSSQL2016:SQL Statistics\SQL Re-Compilations/sec
y luego especificamos la ruta de este archivo al ejecutar relog; de lo contrario, se incluirán todos los contadores.
Qué pasa si no quiero usar relog.exe
Ok, yo ya he dicho que no lo haría, pero si quereis insertar los datos perfmon directamente en SQL, se puede configurar el recopilador de datos para hacerlo directamente.
Solo necesitamos ir a las propiedades del recopilador de datos y elegir SQL como Formato de salida, luego eligimos el DSN que acabamos de crear en el cuadro.
Así todos los datos se insertarán en SQL.
Ejecutando relog.exe
No es muy complicado y ya he explicado algunos parámetros que podemos usar, pero se puede consultar la documentación aquí para obtener la referencia completa.
Obviamente, debemos especificar el archivo a procesar y, en nuestro caso, la configuración de la base de datos para que funcione.
- -f, especifica el formato de salida, SQL
- -o, especifica la ruta del archivo de salida, en nuestro caso, el formato es SQL:nombre_DNS!SQLServerName.
En mi ejemplo, sería SQL:relog!Localhost\MSSQL2016, pero para ser honesto en lugar del nombre del servidor podeis poner lo que querais, porque el nombre del servidor ya está especificado en el DSN
Y con esto ya está listo.
C:\WINDOWS\system32>relog "C:\PerfLogs\Admin\DBA_Collector\SQLDOUBLEG_20171221-000348\SQLDOUBLEG_DBA_DataCollector20171221_104025.blg" -cf "C:\PerfLogs\template_counters.txt" -f SQL -o SQL:relog!localhost\MSSQL2016
Después de ejecutar esto, deberiamos ver algo como esto
Si ves algún tipo de error relacionado con SQL como “A SQL failure occurred. Check the application event log for any errors.” comprobad que el DSN se creó como expliqué anteriormente.
Datos de Perfmon en SQL Server
Después de ejecutar con éxito relog.exe con SQL como destino, deberíamos tener las nuevas tablas en la base de datos que hemos especificado en nuestro DSN.
Los datos se distribuyen de la siguiente manera
- CounterData, estos son los valores para los diferentes contadores
- CounterDetails, los diferentes contadores incluidos en el archivo de registro que hemos cargado
- DisplayToID, las diferentes máquinas[s] de las que hemos cargado información
El Truco
Y finalmente, después de todo esto, aquí estamos, ya tenemos los datos cargados en SQL Server y queremos mostrarlos.
En mi opinión, la mejor manera de visualizar estos datos es de la misma manera que podemos hacerlo en Excel, es decir, mostrar un contador por cada columna para que los diferentes valores para un punto dado en el tiempo estén en la misma fila, pero esa no es la forma en que los datos se almacenan en [CounterData] por lo que tenemos que procesar los datos y (redoble de tambores) usar Pivot 🙁
Parece que cada truco que publico involucra los tipos de consultas que más odio escribir, ya sea XML o PIVOT, y la verdad que tiene sentido, porque lo que quiero es ahorrarte el dolor de escribir estas consultas.
De modo que tengo que lograr que sean lo más flexibles que se pueda, luego siempre podeis modificarlos si quereis, claro.
Debido a que no sé qué contadores estarán presentes en estas tablas o incluso qué nombres tienen (recordad que los contadores de SQL dependen del nombre de la instancia o en contadores del sistemao operativo, como el procesador que está relacionado con el numero de nucleos-hilos), necesitamos obtener dinámicamente los valores (contadores) para luego usar PIVOT y transformarlos en las diferentes columnas.
Aunque ya he mostrado cómo filtrar los archivos al insertarlos en SQL, he añadido un parámetro para filtrarlos nuevamente de las tablas, para darle más flexibilidad.
USE [master];
GO
IF OBJECT_ID('dbo.sqlg_parseRelogOutput') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE dbo.sqlg_parseRelogOutput AS RETURN';
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author: Raul Gonzalez @SQLDoubleG
-- Create date: 18/12/2017
-- Description: Formats the output generated by relog.exe and exists in the following tables.
-- - CounterData
-- - CounterDetails
-- - DisplayToID
--
-- Parameters:
-- @dbname -> Name of the database where the Perfmon data exist
-- @CounterFilter -> To select only the counters that match the filter
--
-- Usage: Call this stored proc and provide the name of the database where your perfmon logs have been loaded
-- It is recommended not to run in the same server we are trying to analyze due to the
-- amount of resources used by this query
--
-- EXECUTE master.[dbo].[sqlg_parseRelogOutput] N'Perfmon_logs', N'Processor';
--
-- Assumptions: You have loaded perfmon information using relog as explained in
-- https://www.sqldoubleg.com/2017/12/20/getting-perfmon-data-into-sql-server/
--
-- Change Log: 18/12/2017 RAG Created
--
-- Copyright: (C) 2017 Raul Gonzalez (@SQLDoubleG https://www.sqldoubleg.com)
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
-- TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--
-- THE AUTHOR SHALL NOT BE LIABLE TO YOU OR ANY THIRD PARTY FOR ANY INDIRECT,
-- SPECIAL, INCIDENTAL, PUNITIVE, COVER, OR CONSEQUENTIAL DAMAGES OF ANY KIND
--
-- YOU MAY ALTER THIS CODE FOR YOUR OWN *NON-COMMERCIAL* PURPOSES. YOU MAY
-- REPUBLISH ALTERED CODE AS LONG AS YOU INCLUDE THIS COPYRIGHT AND GIVE DUE CREDIT.
--
-- =============================================
ALTER PROCEDURE [dbo].[sqlg_parseRelogOutput]
@dbname sysname
, @CounterFilter NVARCHAR(256) = NULL
AS
BEGIN
DECLARE @column_list NVARCHAR(MAX);
DECLARE @pivot_list NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @RC INT;
IF DB_ID(@dbname) IS NULL BEGIN
RAISERROR (N'The database provided does not exist', 16, 1, 1);
RETURN -100;
END;
-- Get the different perfmon counters to be new columns for the output
SET @SQL = N'USE ' + QUOTENAME(@dbname) + N'
IF OBJECT_ID(''dbo.CounterData'') IS NULL OR OBJECT_ID(''dbo.CounterDetails'') IS NULL BEGIN
RAISERROR (N''The Relog generated tables are not present on this database, please ensure that [dbo].[CounterData], [dbo].[CounterDetails] and [dbo].[DisplayToID] exist'', 16, 1, 1)
RETURN
END
SET @column_list = (SELECT DISTINCT N'', '' + QUOTENAME(CONCAT([ObjectName], CHAR(92), [CounterName], NULLIF(CONCAT(N'' ('', InstanceName, N'')''),N'' ()'' ))) AS [text()]
FROM [dbo].[CounterDetails]
WHERE [CounterName] LIKE CONCAT(N''%'', @CounterFilter + N''%'')
FOR XML PATH(''''))
SET @pivot_list = (STUFF(@column_list, 1, 2, ''''))';
EXECUTE @RC = sys.sp_executesql
@stmt = @SQL
, @params = N'@column_list NVARCHAR(MAX) OUTPUT,@pivot_list NVARCHAR(MAX) OUTPUT, @CounterFilter NVARCHAR(256)'
, @column_list = @column_list OUTPUT
, @pivot_list = @pivot_list OUTPUT
, @CounterFilter = @CounterFilter;
IF @RC <> 0 BEGIN
RETURN @RC;
END;
-- Now generate the the query with the right values to PIVOT and get the data out
SET @SQL = N'USE ' + QUOTENAME(@dbname) + N'
SELECT [ComputerName]
, [CounterDateTime]
' + @column_list + N'
FROM (
SELECT CONCAT(det.[ObjectName], CHAR(92), det.[CounterName], NULLIF(CONCAT('' ('', det.InstanceName, '')''),'' ()'' )) AS PermonCounter
,did.[DisplayString] AS [ComputerName]
,dat.[CounterDateTime] AS [CounterDateTime]
,dat.[CounterValue]
FROM [dbo].[CounterData] AS dat
LEFT JOIN [dbo].[CounterDetails] AS det
ON det.CounterID = dat.CounterID
LEFT JOIN [dbo].[DisplayToID] AS did
ON did.[GUID] = dat.[GUID]
WHERE det.[CounterName] LIKE CONCAT(N''%'', @CounterFilter + N''%'')
) AS s
PIVOT(
SUM([CounterValue])
FOR [PermonCounter] IN (' + @pivot_list + ' )) AS pvt
ORDER BY [CounterDateTime] ASC';
EXECUTE @RC = sys.sp_executesql
@stmt = @SQL
, @params = N'@CounterFilter NVARCHAR(256)'
, @CounterFilter = @CounterFilter;
IF @RC <> 0 BEGIN
RETURN @RC;
END;
END;
GO
EXECUTE master.[dbo].[sqlg_parseRelogOutput] N'Perfmon_logs', N'Processor';
Si ejecutamos el ejemplo propuesto, la salida sería algo como esto.
Conclusión
Como ya dije en la primera parte de esta publicación, es importante tener una idea de cómo se ven nuestros servidores SQL en un estado normal, como de ocupados están y como rinden, en caso de que pase algo y todos empiezen a buscar cumplables. Y creedme, el primer lugar al que todos apuntan es a la base de datos, independientemente de si es la culpable o no.
Espero que os haya dado algunas ideas de como comenzar a recopilar datos de rendimiento y ayudaror a visualizar cuándo los dedos comienzan a señalar.
Gracias por leer y, como siempre, cualquier comentario o pregunta es bienvenido.
