viernes, 17 de marzo de 2017

SQL Server Profiler - Scripteando Un Traceo en el Servidor - (Server Side Trace)

Amigos, cuando estudiamos problemas de performance en un server, sin lugar a dudas uno de nuestros aliados es el PROFILER.
   Esta herramienta nos permite recolectar distintos valores e incluso sentencias que están corriendo en nuestro server.
     Ahora bien, qué ocurre si queremos tracear una base de datos por espacio prolongado en horas?  Seguramente trataremos en primera instancia el hacerlo utilizando la vía más fácil, es decir, la Consola Sql Profiler desde fuera del server.
     El gran problema del uso de la consola del Profiler en estos casos es que,  corrida desde la consola a nivel de usuario contra el servidor, es pasible de sufrir interrupciones ante la pérdida de conexión con el server.  Estas interrupciones nos van a impedir la colección de datos necesarios para atacar el problema que estamos buscando.
  Cuál sería la solución al problema si debemos tracear un servidor por unas cuantas horas?

  La solución, y a la vez el objetivo de este post, es  generar un script que nos permita hacer el mismo traceo que con el Profiler, pero del lado del server. En inglés se lo conoce como “Server Side Trace”y consta de los siguientes comandos, veamos:

sp_trace_create Crea la definición del nuevo traceo que se inicializará como “stopeado

Su sintaxis es la siguiente:
sp_trace_create @TraceID OUTPUT@Options@fileName@maxFileSize, @Stoptime, @FileCount  

Vemos los parámetros a definir:
@TraceID = es el nº de id que el sql le da por default al traceo. Sirve para identificar al mismo posteriormente. Se recomienda utilizar una variable integer y dejarla nula (ver en el ejemplo)
@Options =  Aquí podemos setear un 2 como valor en cuyo caso cuando se alcance el máximo tamaño por archivo se generará un nuevo sin borrar el anterior con el mismo nombre y un integer adosado al mismo…
                  … O un 4, que especifica que , ante una falla que no le permita al trace escribir sobre el archivo sql server se apagará. Opción útil para traces en traceos de seguridad.
@FileName =  Donde debe definirse la ruta y el nombre del file donde se va a grabar el trace. Ejemplo 'C:\filetrace’. Aquí debemos indicar que el file será acompañador con una extensión .trc que el sql le adosará en forma automática y que el mismo NO DEBE UTILIZAR EL CARÁCTER “UDERSCORE” es decir nunca utilizar un nombre como este “my_trace”.
@maxFileSize = donde se especifica el máximo número en mb que un  archivo de traceo puede alcanzar. El default es 5 mb.
 @stoptime= su valor puede ser nulo y es donde se especifica el horario en el cual el trace debe detenerse (este valor tiene precedencia sobre el @maxfilesize cuando no se especifica el valor 2 en options.
@filecount=   puede ser nulo o no. Su valor si se setea debe ser un integer mayor a 1 . Con este parámetro se determinan la máxima cantidad de files que el sql server va a mantener para este traceo antes de borrar el más viejo si la opción @Options = 2
Las posibles respuestas que debemos validar:

Return code            Description
0              No error.
1              Unknown error.
10            Invalid options. Returned when options specified are incompatible.
12            File not created.
13            Out of memory. Returned when there is not enough memory to perform the specified action.
14            Invalid stop time. Returned when the stop time specified has already happened.
15            Invalid parameters. Returned when the user supplied incompatible parameters.

sp_trace_setevent Permite agregar eventos a tracear al trace previamente creado y stopeado

Su sintaxis es la siguiente:
sp_trace_setevent @TraceID, @EventId, @ColumId, @on

Vemos los parámetros a definir:
@TraceID = es el nº de del trace al cual vamos a agregarle eventos
@EventID = es el id del evento a agregar y puede ser uno a más de los sgtes:

Event number         Event name             Description
10            RPC:Completed       Occurs when a remote procedure call (RPC) has completed.
12            SQL:BatchCompleted              Occurs when a Transact-SQL batch has completed.
15            Audit Logout          Occurs when a user logs out of SQL Server.
16            Attention Occurs when attention events, such as client-interrupt requests or broken client connections, happen.
El listado total de eventos se encuentra en https://msdn.microsoft.com/en-us/library/ms186265.aspx

@ColumnId = es el id de columna a ser agregado para el evento. Los valores:

Column number     Column name         Description
1              TextData Text value dependent on the event class that is captured in the trace.
2              BinaryData              Binary value dependent on the event class captured in the trace.
3              DatabaseID             ID of the database specified by the USE database statement, or the default database if no USE database statement is issued for a given connection.

El listado total de eventos se encuentra en https://msdn.microsoft.com/en-us/library/ms186265.aspx

@On = es un valor de tipo “bit”y puede tener un 0 o un 1
1 = “on” el evento es “prendido”
0 = “off” el evento es “apagado”

sp_trace_filter : Determina los filtros a setear para el trace

Su sintaxis es la siguiente:
sp_trace_filter @TraceID, @ColumnId@LogicalOperator@Comparison_Operator, @Value  

Vemos los parámetros a definir:
@TraceID = es el nº de del trace al cual vamos a agregarle eventos
@ColumnId = es el id de columna sobre la que establecemos el filtro
@LogicalOperator = es un integer = 0 si la operación es AND, =1 si es OR
@Comparison Operator = es un integer que puede representar alguna de las siguientes:

Valor   Operador de comparación
0              (Es igual a)
1              (No es igual a)
2              (Mayor que)
3              (Menor que)
4              (Mayor o igual que)
5              (Less Than Or Equal)
6              LIKE
7              NOT LIKE
@Value = valor que se va filtrar (puede incluir % si lo precede un operador lógico)

sp_trace_status Modifica el estado actual del trace

Su sintaxis es la siguiente:
sp_trace_status @TraceID, @Status

Vemos los parámetros a definir:
@TraceID = es el nº de del trace
@ColumnId = es un integer que determina la acción a tomar acorde a la sgte tabla:

Status  Description
0              Detiene el seguimiento especificado.
1              Inicia el seguimiento especificado.
2              Cierra el seguimiento especificado y elimina su definición del servidor.




Y bien amigos les propongo un script de ejemplo en el cual vamos a tracear el evento "Scan”con el objetivo de entender en que instancias nuestro motor ejecuta esta operación tan poco performante y tomar las acciones correctivas posteriormente.

/***************************************************/
-- Traceo Por Script Desde el Servidor            --
-- Autor: Gustavo Herrera - Sql Server Para Todos --
-- Detalle: Se tracea el evento "Scan: Started"   --
/***************************************************/

-- Declaración de Variables
DECLARE
@Trc INT ,
@TraceID INT,
@MaxFileSize bigint,
@FileName NVARCHAR(128),
@On bit,
@MaxDatetime datetime,
@FileCount int

-- Set variables
SET @MaxFileSize =  500 -- (500 mb)
SET @FileName = 'C:\TrcBuscaScan'  -- Ubicación de los Files
SET @On = 1
SET @FileCount = 2  -- que vaya pisando c/2 files
SET @MaxDatetime = '2017-03-17 23:59:59' -- hora de fin del traceo

-- Definimos el Trace y guardamos el resultado en @Trc
EXEC @Trc = sp_trace_create @TraceID output, 2, @fileName, @maxFileSize, @MaxDatetime, @FileCount

-- Si La Definición Da Error que salgo por error
IF (@Trc != 0) GOTO error

-- Seteo el evento a tracear y las columnas que deseo
EXEC sp_trace_setevent @TraceID, 51,  1, @on  --TextData
EXEC sp_trace_setevent @TraceID, 51, 11, @on  --LoginName
EXEC sp_trace_setevent @TraceID, 51, 12, @on  --Spid
EXEC sp_trace_setevent @TraceID, 51, 13, @on  --Duration
EXEC sp_trace_setevent @TraceID, 51, 14, @on  --StarTime
EXEC sp_trace_setevent @TraceID, 51, 15, @on  --EndTime
EXEC sp_trace_setevent @TraceID, 51, 16, @on  --Reads
EXEC sp_trace_setevent @TraceID, 51, 17, @on  --Writes
EXEC sp_trace_setevent @TraceID, 51, 18, @on  --CPU

-- Seteo Filtros

-- Filtro: solo traceamos sobre la base de datos con iddatabase = 6
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6

-- filtro2: excluímos la app SQL Profiler
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Comezamos el traceo
EXEC sp_trace_setstatus @TraceID, 1
-- Muestra en pantalla el trace id 
SELECT TraceID=@TraceID 
GOTO finish 

-- Atrpamos el error
error: 
SELECT ErrorCode=@Trc 

-- exit
finish: 
GO


Eso es todo amigos, espero que les haya sido útil. Saludos desde Argentina.

1 comentario:

  1. compadre, esto se ve buenisimo, tiene mucho potencial, voy a probarlo.
    gracias

    ResponderEliminar