Memorias de un DBA

Un blog dedicado a contribuir a la comunidad SQL Server en español

Solución alternativa a SQL Profiler

with one comment

Una de las herramientas más importantes para el monitoreo de base de datos SQL Server es el SQL Profiler, el cual permite visualizar en tiempo real la actividad en la base de datos. Esta herramienta es ampliamente utilizada por los desarrolladores de aplicaciones y los administradores de base de datos. Pero alguna vez se han preguntado cuál es el impacto en performance que tiene esta herramienta sobre el servidor de base de datos. Bueno a continuación intentaremos graficar un poco el problema y ver cuál es la alternativa al SQL Profiler sin impactar tan significativamente al servidor de base de datos.

El SQL Profiler es una herramienta de cliente para servidores de base de datos Microsoft SQL Server. Esta herramienta permite conectarse a un servidor de base de datos y capturar en tiempo real las sentencias SQL ejecutadas a la base de datos. Esta herramienta se puede configurar para que guarde esta información de tres modos diferentes:

  • El primero no tiene ningún respaldo en disco, solo se visualiza la información en la consola de la herramienta. A esta se le denomina “Normal Trace”, y está configurado por defecto.
  • El segundo permite grabar la información en el disco duro, en un archivo de traza, el cual puede ser leído posteriormente por el mismo SQL Profiler o por la función “ fn_trace_gettable”.
  • El tercero implica grabación directa en una “Trace Table” la cual es una tabla alojada en una base de datos SQL Server, no necesariamente la misma desde donde se hace la traza.

Además el SQL Profiler permite escoger los eventos que se desea monitorear, actualmente se cuenta con una gran cantidad de eventos que permiten monitorear varias cosas como: deadlocks, auditorias, logins fallidos, ejecución de procedimientos almacenados, ejecución de sentencias SQL adhoc, etc. Otra de las ventajas que nos da esta herramienta es la selección de las columnas que deseamos monitorear, cada evento puede guardar información en diferentes columnas tales como, “TextData”, “DatabaseId”, “ObjectName”,”Reads”, “Writes”, “CPU”, etc. Es decir con esta herramienta podemos filtrar nuestro monitoreo tanto de forma vertical como horizontal. Finalmente para completar la trilogía de opciones de filtro que nos ofrece el SQL Profiler, cabe mencionar que podemos filtrar por valores específicos de alguna de las columnas seleccionadas, es decir, podemos indicar que solo deseamos obtener la información de la base de datos “X”, y además que la duración sea mayor a diez segundos, etc. Juntando todos estos filtros podemos tener de manera más personalizada la información que deseamos recabar de la base de datos.

Pero todo esto tiene un costo de performance en el servidor de base de datos. Dependiendo de varios factores como los filtros escogidos, el tipo de traza escogido para guardar la información, y desde donde se esté ejecutando el cliente SQL Profiler, se pueden presentar diferentes problemas de performance. Mientras más información se recolecte en un rango de tiempo corto, mas sufrirá el servidor, debido a que cada operación generada en la base de datos deberá ser enviada al SQL Profiler. Si la traza se guarda sobre el modo “Normal Trace” entonces la información se guardara en la memoria RAM de la maquina donde se está ejecutando el cliente, lo cual evitara que otras aplicaciones puedan usar el espacio en este recurso tan escaso, además de que si en algún momento la aplicación (SQL Profiler) se colgara o se cerrara intempestivamente se perdería toda la información capturada. Si la traza se guarda en un archivo de disco, se tendrá una penalidad en las operaciones de entrada y salida del disco duro donde se guardara el archivo, ya que la escritura hacia este archivo será constante; se recomienda guardar este archivo en discos duros no muy activos y que no contengan otros archivos de base de datos en ellos para no interferir con la velocidad de escritura o lectura de estos otros archivos de base de datos. Ahora si la traza se guarda directamente en una tabla de base de datos, el servidor de base de datos receptor de la traza deberá trabajar muy fuerte para poder recibir toda la información enviada por el SQL Profiler, dependiendo de la cantidad de información recolectada esto puede significar un problema muy grande.

Luego si el SQL Profiler se ejecuta en una maquina de cliente, lo que va a sufrir es el trafico de red debido a que la información es capturada en tiempo real esta debe ser enviada desde el servidor hacia la maquina cliente, y mientras más información se capture, más se saturara el trafico de red. Finalmente uno de los problemas más grandes con la ejecución del SQL Profiler, desde mi punto de vista, es que este al ser una herramienta grafica debe ser ejecutada en la sesión de algún usuario de Windows, y si se requiere monitorear la base de datos por un periodo de tiempo largo, la sesión del usuario de Windows debe mantenerse activa durante todo ese tiempo, y si hubiera algún problema de red o de energía con la PC, la traza se perdería. Normalmente en un ambiente productivo no se tiene acceso a los servidores de manera física, entonces se podría pensar en usar un escritorio remoto hasta el servidor de base de datos y ahí lanzare el SQL Profiler, de esta manera no causare problemas de tráfico de red, porque lo ejecuto en la maquina local, y tampoco necesito tener una maquina cliente prendida todo el día dedicada a capturar la información. Pero el problema es que las sesiones de escritorio remoto también usan bastante memoria y si se hace esto en un ambiente de producción esta memoria no podrá ser usada por el SQL Server para responder a las llamadas que se le hacen, adicionalmente si es que en el peor de los casos, alguien por alguna razón, eliminara todas las sesiones de escritorio remoto, la traza se cortaría.

Ahora que ya se tiene establecido los problemas de performance que puede causar el SQL Profiler en un sistema ocupado y de producción, veremos que la alternativa a esto sería el “Server Side Trace”, el cual cumple la misma función que el SQL Profiler pero corre en el lado del servidor y no tiene ninguna interface grafica. El manejo de este proceso se hace a través de procedimientos almacenados. Utilizando esto eliminamos los problemas relacionados con la interfase grafica y con el trafico de red hacia maquinas cliente, además de que ya no se necesitarían sesiones de usuario de Windows activas para correr el proceso.

Para configurar el “Server Side Trace”, se puede utilizar el mismo SQL Profiler para poder generar el script necesario con todos los filtros y configuraciones escogidas de manera grafica. Para poder hacer esto se debe de abrir el SQL Profiler, y conectarse a un servidor SQL Server.

Seguidamente se debe escoger los filtros tanto de eventos como de columnas, para efectos de simplificar el ejemplo usare una plantilla propio de SQL Server, Finalmente se ejecuta el trace haciendo clic sobre el botón “Run”.

Ahora se para la traza con el botón “Stop” y se procede a obtener el script de definición de la traza a través de la siguiente secuencia:

Como se puede observar, se puede obtener la definición tanto para SQL Server 2000 como para 2005 en adelante. Una vez que se tiene esta definición, se obtendrá un script parecido al siguiente:


/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 07/19/2011  12:10:43 AM         */
/****************************************************/

— Create a Queue
DECLARE @rc INT
DECLARE
@TraceID INT
DECLARE
@maxfilesize bigint
SET @maxfilesize = 5

— Please replace the text InsertFileNameHere, with an appropriate
— filename prefixed by a path, e.g., c:\MyFolder\MyTrace. 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
IF (@rc != 0) GOTO error

— Client side File and Table cannot be scripted

— Set the events
DECLARE @on bit
SET @on = 1
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 3, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 35, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 45, 1, @on
EXEC sp_trace_setevent @TraceID, 45, 3, @on
EXEC sp_trace_setevent @TraceID, 45, 11, @on
EXEC sp_trace_setevent @TraceID, 45, 35, @on
EXEC sp_trace_setevent @TraceID, 45, 12, @on
EXEC sp_trace_setevent @TraceID, 45, 28, @on
EXEC sp_trace_setevent @TraceID, 45, 13, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 3, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 35, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on

— Set the Filters
DECLARE @intfilter INT
DECLARE
@bigintfilter bigint

— Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

— display trace id for future references
SELECT TraceID=@TraceID
GOTO finish

error:
SELECT ErrorCode=@rc

finish:
GO

Luego se deberá modificar algunos valores como el nombre del archivo en el que se guardara la traza. Seguidamente se puede proceder a ejecutar este script sobre un servidor y automáticamente se creara el “Server Side Trace” con las opciones configuradas. Se puede consultar los “Server Side Trace” que se encuentran corriendo en el servidor con la siguiente sentencia:

SELECT * FROM ::fn_trace_getinfo(NULL)

/*
traceid     property    value
———– ———– ————————————
1           1           2
1           2           C:\Program Files\…\Log\log_277.trc
1           3           20
1           4           NULL
1           5           1
*/

Ahora para poder pausar o parar por completo un trace se pueden usar los siguientes procedimientos almacenados, donde se necesita pasar como parámetro del “TraceId”, el cual se obtiene de la sentencia anterior.

EXEC sp_trace_setstatus @traceid = 2 , @status = 0 --Pausar
EXEC sp_trace_setstatus @traceid = 2 , @status = 2 --Detener

En conclusión, como se, puede observar los “Server Side Trace”, ofrecen muchas ventajas sobre el SQL Profiler, y se recomienda su uso cuando se quiere ejecutar una traza en un sistema de producción, para no afectar tanto la performance del mismo. Si lo que se desea es ejecutar una traza corta o en un servidor de base de datos no muy ocupado, se puede seguir utilizando el SQL Profiler, no valdría la pena darse el trabajo de crear un “Server Side Trace”. Se deben sopesar el uso de ambos para poder ver cual es mejor en qué situación en especifico.

Finalmente quisiera dejar un par de links con estudios realizados sobre una comparación más cuantitativa entre ambas alternativas para terminar de demostrar que los “Server Side Trace” son mejores en cuanto a performance de base de datos.

http://www.sqlmag.com/article/performance/sql-server-profiler-or-server-side-trace-

http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

Written by dbamemories

julio 19, 2011 a 1:23 pm

Una respuesta

Subscribe to comments with RSS.

  1. truly fantastic points here, just thank you

    kwiaty online

    marzo 31, 2013 at 10:55 am


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: