Por qué no capturar las trazas desde SQL Profiler y qué mecanismo utilizar
Existen distintas formas de crear trazas de SQL Profiler, y en general, en cuantos más proyectos de Mentoring te ves envuelto, cada cliente utiliza aproximaciones diferentes; estos son los casos más frecuentes que me he encontrado:
- Servidor de bases de datos, capturando traza a fichero .trc
- Servidor de bases de datos, capturando traza a tabla en el mismo servidor
- Servidor de bases de datos, capturando traza a tabla en otro servidor de bases de datos
- Equipo cliente, capturando traza a fichero .trc
- Equipo cliente, capturando traza a tabla en el mismo servidor
- Equipo cliente, capturando traza a tabla en otro servidor de bases de datos
- Equipo cliente, sniffing tráfico de red, y capturando capturando traza a tabla en otro servidor de bases de datos (opción no soportada y que requiere amplios conocimientos de TCP/IP)
Como puedes ver, los modelos de captura pueden ser bastante variables, y a excepción del último caso, todos implican que cierta aplicación, solicita información al servidor de base de datos para guardarlo de alguna forma.Mi primera recomendación es no capturar la traza en una tabla en el mismo servidor, porque dependiendo de la actividad que se produce en el servidor, puede afectar en mayor o menor medida al rendimiento del propio servidor: imagínate que el servidor tiene problemas de IO, si guardas la traza en una tabla del propio servidor, puedes estar incurriendo en IO en el mismo sistema de ficheros, y de alguna manera “desvirtuando” la actividad real del servidor.
Por otro lado, si tienes una aplicación cliente como SQL Profiler que está capturando la información de traza, estaremos incurriendo en actividad de red entre cliente y servidor, que dependiendo del tipo de consultas podrá ser mayor o menor el tráfico. Imagínate aplicación cliente que utiliza mayormente consultas ad-hoc con un tamaño medio en bytes de cada consulta de 200 bytes, y algo así como 300 batches por segundo; estaríamos utilizando un tráfico de red de 2 Kb * 800 = 1600 KBs que es algo más de 1MB/segundo. Quizás pienses que 2Kb por petición es demasiado, siempre depende de cómo está codificada la aplicación cliente. ¿Y sobre 800 peticiones por segundo? He tenido la suerte de esta en clientes que procesan desde 100 peticiones por segundo, hasta cerca de 4000 peticiones por segundo: cada cliente es un mundo distinto, con unos requerimientos concretos, y casi siempre con soluciones diferentes a sus problemas.
¿Y a qué viene tanto rollo? precisamente para buscar una justificación para crear las trazas en el propio servidor. La situación ideal sería crear una traza directamente en el servidor, guardando la traza en un fichero local (nada de ficheros en servidores remotos, ni tablas de SQL Server), y a poder ser sin utilizar SQL Profiler (porque consume memoria que deberíamos dejar libre para SQL Server).
Pues bien, esa situación ideal la tenemos en nuestras manos: crear traza de SQL Profiler que capture información en un fichero local, sin intervención de SQL Profiler 🙂
Cómo crear la definición de la traza
Para aprender a crear trazas de SQL Profiler, podeis utilizar la documentación de SQL Server (Books OnLine) en la siguiente URL (How to: Create a Trace (SQL Server Profiler)).
Los eventos que típicamente suelo utilizar son RPC:Completed, SP:BatchCompleted, y dependiendo si busco ejecución de sentencia dentro de un procedimiento almacenado añado el evento SP:StmtCompleted. No prentendo reducir todos los eventos que expone SQL Server a una lista de 3-4, cada escenario requerira eventos específicos, pero para el caso de análisis de ejecución de procesos nos son suficientes los comentados anteriormente.
Recordar que una vez definida la traza y tras pulsar en ‘Aceptar’, la traza se pone automáticamente en marcha, por lo que deberemos parar la traza (botón ‘Stop’) porque queremos aprender a hacerlo de otro modo 🙂
Una vez definida la traza, lo que hay que hacer es guardar la definición de la traza: este es el paso con el que es posible que esteis menos familiarizados. En lugar de elegir en el menú ‘Save As…’ un formato concreto (fichero XML, fichero .trc, tabla de base de datos, etc.), lo que haremos será elegir del menú ‘File’, el menú ‘Export’, ‘Script Trace Definition’, y elegiremos ‘For SQL Server 2005’ , o ‘For SQL Server 2000’ dependiendo de la versión de SQL Server con la que estemos trabajando. Fíjate que lo que nos pedirá es guardar la traza COMO FICHERO T-SQL!!! Ahí está el secreto de este mecanismo 🙂
Guarda el fichero que lo vamos a referenciar en la próxima sección. Por cierto, ya puedes cerrar SQL Profiler porque ya no lo vamos a necesitar más para crear nuestra traza…
Cómo personalizar la definición de la traza
Lo siguiente que debemos hacer es editar el fichero con SQL Server Management Studio. Abrimos el fichero .sql e ‘identificaremos’ varias secciones:
- Tamaño del fichero de captura
— Create a Queue declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
El valor @maxfilesize indica el tamaño que tendrá el fichero en MB. Por defecto, el tamaño del fichero es de 5MB. Deberás personalizarlo para tus necesidades concretas, pero yo suelo crear ficheros del orden de 100 o 200MB para tener un periodo de actividad lo suficientemente grande. Recuerda que cuantos más eventos añadas a la definición de la traza, más facilmente se llenará el fichero; por ejemplo, si añades eventos relativos al plan de ejecución, el fichero se llenará rápidamente.
Independientemente del tamaño del fichero, SQL Server ‘vuelca’ al fichero de traza información en trozos de 128Kb, por lo que notareis que el tamaño del fichero va creciendo en múltiplos de 128.
- Ubicación donde se guardará el fichero de traza
— Please replace the text InsertFileNameHere, with an appropriate — filename prefixed by a path, e.g., c:MyFolderMyTrace. The .trc extension
— will be appended to the filename automatically. If you are writing from
— remote server to local drive, please use UNC path and make sure server has
— write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N’InsertFileNameHere’, @maxfilesize, NULL
Deberás reemplazar el texto ‘InsertFileNameHere’ por la ruta donde se guardará el fichero. Recuerda que la ruta debe ser una ruta del mismo servidor para evitar el trafico que red que comentamos anteriormente. El nombre del fichero no es necesario que tenga extensión .trc porque SQL Server automáticamente lo añade por nosotros.
Yo normalmente lo guardo en la carpeta c:perflogs que es la ruta donde suelo guardar los ficheros de traza del monitor de rendimiento, pero bueno, cada uno tiene sus preferencias. Por cierto, cuidado con intentar guardarlos en recursos utilizados por Clustering Services porque no te dejará…
- Columnas y Eventos a capturarLa siguiente sección muestra una serie de ejecuciones del procedimiento almacenado sp_trace_setevent, que lo que hace es definir en la traza las columnas y eventos que se quieren capturar. Toda esta información está disponible en los Libros En Pantalla (BOL), pero como utilizamos SQL Profiler para definir la traza, no necesitamos sabernoslo de memoria 🙂
… …
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
…
…
- Definición de FiltrosLa última sección incluye la definición de los filtros que se aplican; generalemente las trazas tendrán filtros del tipo:
- Aplicación que se desea auditar
- Nombre de usuario
- Lecturas, escrituras, duración, uso de CPU mayor que cierto valorPor ejemplo, el siguiente código excluye todos los eventos que vengan de la aplicación SQL Profiler:
— Set the Filters declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Server Profiler’
— Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
Una vez modificado el código TSQL de la traza, lo que queda es ejecutar la traza para que se ponga a capturar eventos. Para ello, lo único que tendremos que hacer es ejecutar el código TSQL del fichero de traza, y listo. Si nos devuelve un valor mayor que cero indicará que la traza se ha iniciado correctamente; en caso contrario, habrá que verificar la definición de la traza para ver si es correcta.
Cómo validar el estado de la traza
Para obtener información de las trazas, SQL Server expone una UDF que muestra información de todas las trazas en funcionamiento:
SELECT * FROM ::fn_trace_getinfo(default) Que devuelve los siguentes resultados:
traceid property value ———————————————————————–
1 1 2
1 2 C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGlog_553.trc
1 3 20
1 4 NULL
1 5 1
2 1 0
2 2 c:traza.trc
2 3 5
2 4 NULL
2 5 1
La columna TraceId muestra el identificador de la traza; fíjate que en el ejemplo hay dos trazas en marcha, la traza 1 que es la traza por defecto de SQL Sever 2005, y la traza 2, que es la que he creado yo usando el scritp anterior. Fíjate que la property 2 indica donde se almacena la traza que podrás usarlo como TIP para identificar cual es la traza que creaste. Otro valor interesante es la property 3 que indica el tamaño máximo del fichero (en mi caso lo dejé con 5MB). Para finalizar la property 5 indica el estado de la traza, en este caso 1 indica que se está ejecutando.
Para finalizar, si necesitamos cambiar el estado de la traza (por ejemplo pararla), utilizaremos el procedimiento almacenado de sistema sp_trace_setstatus:
— Parar la traza EXEC sp_trace_setstatus id_traza, 0
— Cerrar y borrar la traza
EXEC sp_trace_setstatus id_traza, 2
Donde id_traza es el identificador de la traza que hemos creado.
Conclusión
Hemos conseguido crear trazas de SQL Profiler sin necesidad de tener en marcha la herramienta de captura de trazas. Dependiendo del servicio que tenga que atender tu servidor de bases de datos, estarás más o menos necesitado de que tus scripts de auditoría tengan más o menos impacto en el rendimiento de tus servidores. El paso siguiente será consultar la información capturada desde SQL Server Profiler, o desde TSQL…
