Seguimiento de la eficacia de MS SQL Server. Consejo practico

SQL Profiler es una herramienta de software que se utiliza para rastrear SQL Server. "Seguimiento": una sesión para recopilar información sobre el funcionamiento de SQL Server 2008

Propósito principal:

Los administradores utilizan SQL Profiler para:

· Análisis de la aplicación;

· Determinar la optimización de las solicitudes enviadas al servidor;

· Identificar los comandos Transact-SQL que generan un error;

· Recopilar información sobre la actividad del usuario durante un largo período de tiempo;

· Seguimiento del funcionamiento del servidor en tiempo real.

Nuevas oportunidades:

una. Creación de perfiles de Analysis Services

B. Perfilado de eventos de Integration Services

C. la capacidad de registrar lecturas de medidores desde el Monitor de rendimiento cuando se registra información de ejecución de comandos;

D. Se han agregado muchos eventos nuevos y fuentes de información a Profiler que pueden seleccionarse para registrarse en un archivo de seguimiento;

F. la capacidad de agrupar eventos en la ventana del generador de perfiles.

Trabajar con SQL Server Profiler

1. Inicie SQL Server Profiler - desde el menú Inicioà Programasà SQL Server 2008à Performance Toolsà SQL Server Profiler.

2. En la ventana que se abre, en el menú Archivo Seleccione Nuevo rastro y conectarse al servidor SQL Server 2008 que estaremos monitoreando.

3. Configure los parámetros de la sesión en la ventana. Propiedades de seguimiento, que se abre automáticamente antes de iniciar una sesión de seguimiento (consulte la Fig. 8.1).

Arroz. 8.1. Configuración de los parámetros de la sesión de seguimiento

una. En la pestaña General seleccionar de la lista Usa la plantilla la plantilla más adecuada. La plantilla se selecciona usando el menú Archivo à Plantillas en SQL Server Profiler. Inicialmente, tienes ocho plantillas a tu disposición:

1). Estándar (predeterminado)- una plantilla predeterminada que le permite realizar un seguimiento de todos los procedimientos almacenados y comandos Transact-SQL que se ejecutan para su ejecución;

2). SP_Counts- recopilación de información sobre los procedimientos almacenados y las funciones lanzadas para su ejecución, ordenadas por nombres;

3). TSQL- recopilación de información sobre todos los comandos Transact-SQL lanzados para su ejecución en el servidor, indicando el identificador de los procesos del usuario y la hora de inicio;

4). TSQL_Duration- es similar a la plantilla anterior, pero en lugar de información sobre el momento en que se lanzó el comando TSQL, se registra el tiempo que tardó en ejecutarse;

5). TSQL_Grouped- además de la información sobre el código de comando Transact-SQL y la hora de su lanzamiento, también se registra información sobre el nombre de la aplicación, la cuenta de usuario en el sistema operativo y la cuenta de usuario que se utilizó para conectarse;

6). TSQL_Replay- registrar la información más detallada sobre los comandos Transact-SQL ejecutados;

7). TSQL_SPs- además de registrar información sobre el inicio del procedimiento almacenado (SP: Iniciando), se registra información sobre la ejecución de cada uno de los comandos del procedimiento almacenado (SP: StmtStarting);

8). Afinación: se utiliza para recopilar la información requerida por el Asesor de optimización de la base de datos.

B. En la pestaña General si es necesario especificar la ubicación para almacenar la información de seguimiento:

una). La información de seguimiento se puede registrar en un archivo (5 MB de forma predeterminada):

· Parámetro Habilitar transferencia de archivos determina si el siguiente archivo se creará automáticamente al completar un archivo. El nombre del siguiente archivo será el mismo que el del anterior, pero se agregará un número a su nombre (1, 2, 3, etc.)

· Parámetro El servidor procesa datos de seguimiento se puede utilizar para aumentar la fiabilidad del registro de la información de seguimiento. Cuando se selecciona esta casilla de verificación, el servidor se encargará del procesamiento de la información de seguimiento.

2). La información de seguimiento se puede almacenar en una tabla de SQL Server. La tabla con el conjunto de columnas requerido se creará automáticamente.

3). Usando el parámetro Habilitar el tiempo de detención del seguimiento puede especificar la hora en la que el seguimiento se desactivará automáticamente.

C. En la pestaña Selección de eventos determinar los parámetros para recopilar información. En la tabla de esta pestaña, debe seleccionar los eventos requeridos (en filas) y la información (en columnas) que se registrará para ellos. Para mostrar todas las filas y columnas, debe marcar las casillas Mostrar todos los eventos y Mostrar todas las columnas.

una). usando el botón Filtros de columna(Filtros de columna) configura filtros para recopilar la información necesaria (realizar un seguimiento de las acciones realizadas en una base de datos específica, o una aplicación específica, o un usuario específico) - Me gusta o No como;

2). usando el botón Organizar columnas(Organizar columnas) personalice el orden de las columnas para mostrar o registrar en el generador de perfiles con la capacidad de agrupar datos: sección Grupo.

4. Después de especificar todos los parámetros de seguimiento, haga clic en el botón Correr(Ejecutar) (ver figura 8.2)

Arroz. 8.2. Visualización de información durante una sesión de seguimiento

La parte superior de la ventana muestra los eventos que ocurren en el servidor y la parte inferior proporciona información detallada sobre cada evento (por ejemplo, el código de comando SQL).

Funciones disponibles en la ventana de seguimiento:

1. Si la pestaña Organizar columnas en las propiedades de la plantilla ha seleccionado las columnas para agrupar, puede agrupar por estas columnas los registros en la ventana gráfica. Para este propósito en el menú Vista hay un comando Vista agrupada;

2. Si la lista Grupo solo se ha colocado una columna, entonces tiene la oportunidad de usar el modo de visualización Vista agregada(ver fig. 8.3). Este modo se habilita usando el comando Vista agregada del mismo menú Vista.

Arroz. 8.3. Modo de visualización Vista agregada

3. Puede abrir eventos guardados en archivos y tablas de seguimiento en el generador de perfiles. También es posible repetir las operaciones registradas usando el menú Repetición;

4. Puede vincular la información de seguimiento a los contadores de rendimiento del Monitor del sistema. Para esto:

Definir una sesión de seguimiento durante la cual se debe registrar la información de las columnas. Hora de inicio y Hora de finalización;

· Inicie una sesión de seguimiento con la grabación de información en un archivo o tabla. Simultáneamente con él, recopile un protocolo de lecturas del medidor en un archivo Monitor de rendimiento;

Abra la información recopilada del archivo de seguimiento en el generador de perfiles y luego use el comando Importar datos de rendimiento del menú Archivo.

¿Qué aprenderá de este artículo?

Propósito de la herramienta de seguimiento de SQL Profiler
Cómo rastrear el texto de la consulta al DBMS, al que se traduce la consulta 1C
Configuración del filtro de seguimiento
Cómo personalizar el Analizador de SQL

A menudo, surge una situación en el trabajo cuando una solicitud en 1C, por alguna razón, funciona lentamente, pero el análisis del texto de la solicitud no nos informa sobre ningún problema.

En este caso, debe estudiar este problema en un nivel inferior. Para hacer esto, necesitamos mirar el texto de la consulta SQL y el plan de consulta. Puede usar SQL Profiler para esto.

SQL Profiler - propósito

SQL Profiler es un programa incluido en MS SQL Server que está diseñado para ver todos los eventos que ocurren en SQL Server. En otras palabras, es necesario registrar un rastro.

¿En qué casos puede ser útil esta herramienta para un programador 1C? En primer lugar, puede obtener el texto de una consulta en SQL y ver su plan. Esto también se puede hacer en el diario de tecnología (TJ), pero el plan para solicitar en el TJ no es tan conveniente y requiere algunas habilidades y habilidades. Además, en el generador de perfiles, puede ver no solo un plan de ejecución de consulta textual, sino también gráfico, que es más conveniente.

El generador de perfiles también le permite averiguar:

solicita más de un cierto tiempo
consultas contra una tabla específica
esperando cerraduras
tiempos muertos
interbloqueos, etc.

Analizar consultas con SQL Profiler

Profiler se usa a menudo específicamente para analizar consultas. Y al mismo tiempo, es necesario analizar no todas las consultas ejecutables, sino cómo una determinada consulta 1C se traduce a SQL, y prestar atención a su plan de ejecución.

En particular, es necesario comprender por qué la solicitud es lenta. O, al crear una consulta grande y compleja, debe asegurarse de que la consulta SQL no contenga uniones de subconsultas.

Para rastrear la solicitud en el rastreo, realice los siguientes pasos:

1. Inicie SQL Profiler: Inicio - Todos los programas - Microsoft SQL Server 2008 R2 - Herramientas de productividad - SQLProfiler.

2. Cree una nueva traza: Archivo - Crear seguimiento(Ctrl + N).

3. Especifique el servidor DBMS en el que se encuentra nuestra base de datos y haga clic en Conectar:

Nada nos impide rastrear el servidor DBMS ubicado en cualquier otra computadora.

4. En la ventana que aparece Propiedades de seguimiento cambiar a marcador Seleccionar eventos:

Dado que necesitamos consultas y planes de consulta, necesitamos habilitar los eventos correspondientes. Para mostrar una lista completa de propiedades y eventos, active las banderas Mostrar todos los eventos y Mostrar todas las columnas... Ahora debe seleccionar solo los eventos que se muestran en la figura a continuación, el resto debe estar deshabilitado:

Descripción de estos eventos:

ShowplanStatisticsProfile - plan de texto de ejecución de consultas
ShowplanXMLStatisticsProfile - plan gráfico de ejecución de consultas
RPC: Completado: el texto de la solicitud, si se ejecuta como un procedimiento (si se ejecuta una solicitud 1C con parámetros)
SQL: BatchCompleted: el texto de la solicitud, si se ejecuta como una solicitud normal (si se ejecutó una solicitud 1C sin parámetros)

6. En esta etapa, debe configurar el filtro para los eventos seleccionados. Si el filtro no está instalado, veremos solicitudes para todas las bases de datos ubicadas en este servidor DBMS. Haga clic en el botón Filtros de columna para configurar el filtro por el nombre de la base de datos:

Ahora vemos en el seguimiento solo consultas a la base de datos "TestBase_8_2".

También puedes poner un filtro en otros campos, los más interesantes de ellos son:

Duración
TextData (generalmente el texto de la solicitud)
RowCounts (el número de filas devueltas por la consulta)

Digamos que necesitamos "capturar" todas las solicitudes a la tabla "_InfoRg4312" que duran más de 3 segundos en la base de datos "TestBase_8_2". Esto requiere:

a) Establecer filtro por base de datos (ver arriba)
b) Establecer filtro por duración (establecido en milisegundos):

c) Establecer filtro por texto de solicitud:

Para establecer un filtro por el texto de la solicitud, use una máscara. Si desea realizar un seguimiento de las consultas que acceden a varias tablas, se crean varios elementos en la sección "Apariencia". Las condiciones de los filtros superpuestos funcionan en conjunto.

7. Ahora comience a rastrear usando el botón Correr en la ventana Propiedades de seguimiento y observar los eventos que caen bajo los filtros instalados, cuya visualización ha sido configurada.

Los botones del panel de comando se utilizan para controlar el enrutamiento:

Propósito de los botones:

Borrador- borra la ventana de seguimiento
Comienzo- comienza a rastrear
Pausa- pausa el seguimiento, cuando hace clic en Inicio, el seguimiento se reanuda
Detener- deja de rastrear

8. La ventana de seguimiento consta de dos partes. En la parte superior se encuentran los eventos y sus propiedades, en la parte inferior - información según el tipo de eventos. En nuestro ejemplo, aquí se mostrará el texto de la solicitud o su plan.

9. Ejecutemos la solicitud en la consola de consultas 1C y veamos cómo se reflejará en el generador de perfiles:

Puede ver en el comportamiento de seguimiento que hubo varias solicitudes al final, y solo una de ellas es de interés para nosotros. El resto de solicitudes son solicitudes de servicio.

10. Las propiedades de los eventos permiten evaluar:

cuántos segundos tomó la solicitud (Duración)
cuántas lecturas lógicas hubo
cuántas filas devolvió la consulta como resultado (RowCounts), etc.

En nuestro caso, la solicitud tomó 2 milisegundos, realizó 4 lecturas lógicas y devolvió 1 fila.

11. Si observa uno de los eventos anteriores, puede ver el plan de consulta en forma gráfica:

Se puede ver en el plan que la búsqueda se realiza en el índice por precio, este plan no se puede llamar ideal, ya que el índice no cubre, los campos de código y nombre se obtienen usando KeyLookup, lo que toma el 50% del tiempo. .

Usando el menú contextual, el plan de consulta gráfico resultante puede guardarse en un archivo separado con la extensión * .SQLPlan y abrirse en el generador de perfiles en otra computadora o usando el programa SQL Sentry Plan Explorer, que es más avanzado.

12. Si subimos aún más, veremos el mismo plan de consulta, pero en forma de texto. Este es el plan que se muestra en TZ, MCC y otras herramientas de monitoreo de desempeño de 1C.

En el formato del generador de perfiles en sí, es decir, con la extensión * .trc
A formato xml
Haga una plantilla a partir de la traza (vea el siguiente elemento)
Guarde la traza resultante como una tabla de base de datos. Esta es una forma muy conveniente cuando, por ejemplo, necesita encontrar la consulta más lenta en un seguimiento o filtrar consultas por algún parámetro.

Luego seleccionamos la base de datos en el servidor especificado, especificamos el nombre de la tabla donde se guardará el seguimiento. Puede usar una tabla existente o darle un nuevo nombre, y luego esta tabla se creará automáticamente.

Ahora es posible crear consultas de cualquier complejidad en nuestra tabla: por ejemplo, para buscar las consultas más largas.

También debe recordar que la Duración se almacena en la tabla en millonésimas de segundo y, al mostrar el resultado, debe convertir el valor a milisegundos. También hay una columna RowNumber en la tabla que muestra el número de esta fila en el seguimiento.

14. Con el uso frecuente del generador de perfiles para analizar consultas, ajustar constantemente los eventos y filtros deseados le llevará mucho tiempo.

En este caso, las plantillas de seguimiento nos ayudarán, donde configuramos los filtros y el orden de las columnas que necesitamos, y luego simplemente usamos la plantilla existente al crear un nuevo seguimiento.

Para crear una plantilla, use el menú Archivo - Plantillas - Nueva plantilla:

En la primera pestaña, especifique el tipo de servidor, el nombre de la plantilla y, si es necesario, configure el indicador para usar esta plantilla de forma predeterminada.

En la segunda pestaña, seleccionamos los eventos requeridos y configuramos los filtros (como se muestra arriba).

Al crear una nueva traza, podemos especificar la plantilla requerida, y luego, en la segunda pestaña, todos los filtros y eventos se completarán automáticamente de acuerdo con la plantilla creada.

Burmistrov Andrey

Analizador de SQL Server 2005, seguimiento de consultas de aplicaciones, patrones de seguimiento, agrupación de información de consultas

Una de las herramientas más útiles para monitorear la actividad de los usuarios es perfilador (Perfilador). Con esta herramienta, puede averiguar qué comandos está ejecutando actualmente SQL Server. La necesidad de utilizar un generador de perfiles surge con mucha frecuencia. A continuación, se muestran algunas situaciones estándar en las que puede resultar muy difícil prescindir de él:

q desea analizar el funcionamiento de la aplicación y ver qué comandos ejecuta en el servidor. Esta información puede resultar útil:

· Comprender con qué tablas de la base de datos trabaja esta aplicación al realizar determinadas operaciones. Muy a menudo en una empresa existe la necesidad de crear informes en una forma que no es proporcionada por la aplicación, y los desarrolladores rara vez brindan información detallada sobre la estructura de la base de datos;

· Para saber qué tan óptimo desde el punto de vista de rendimiento las solicitudes son enviadas al servidor por la aplicación. En la práctica, cuando utiliza el generador de perfiles, a menudo puede identificar consultas completamente subóptimas, por ejemplo, cuando se realiza el filtrado o la clasificación de datos en el cliente;

· Comprender al ejecutar qué comando Transact -SQL de la aplicación en el servidor ocurre un error;

q para recopilar información sobre la actividad del usuario durante un largo período de tiempo (por ejemplo, puede recopilar todas las solicitudes que fueron enviadas al servidor por una determinada aplicación durante la jornada laboral). La información recopilada se puede analizar manualmente o pasar a Database Tuning Advisor para un análisis automatizado;

q monitorear el desempeño del servidor en tiempo real. Por ejemplo, si el servidor se ralentiza repentinamente, puede ver en la ventana del generador de perfiles qué comandos se están ejecutando actualmente en él.

En SQL Server 2005, el generador de perfiles tiene muchas cosas nuevas:

q Se introdujo la creación de perfiles de eventos de Integration Services. Ahora puede utilizar el generador de perfiles para realizar un seguimiento del progreso de los nuevos paquetes DTS;

q ahora es posible registrar las lecturas de los contadores del Monitor del sistema al escribir información sobre la ejecución de un comando;

q Se han agregado muchos eventos nuevos y fuentes de información al generador de perfiles que se pueden seleccionar para escribir en el archivo de seguimiento. La definición de qué escribir en el archivo de seguimiento ahora se puede guardar en formato XML;

q Ahora puede guardar los resultados de la traza en formato XML (también se guarda la capacidad de escribir en formatos ANSI, OEM, UNICODE);

q Incluso los planes de ejecución de los comandos Transact -SQL capturados por el generador de perfiles se pueden guardar en formato XML. Estos planes se pueden abrir en SQL Server Management Studio para un análisis más detallado;

q ahora es posible agrupar eventos directamente en la ventana del generador de perfiles. Utilizando la agrupación, por ejemplo, puede calcular muy fácilmente cuántas veces se ha ejecutado un comando Transact -SQL en particular en el servidor durante el día.

Trabajar con el generador de perfiles parece muy simple. Esta aplicación se puede iniciar desde el menú Comienzo| Programas| Microsoft SQL Server 2005 | Herramientas de rendimiento | Analizador de SQL Server ... Para comenzar, en la ventana del generador de perfiles que se abre en el menú Archivo(Archivo) debe seleccionar NuevoRastro(Traza nueva) y conéctese al servidor SQL Server 2005 que supervisará. El seguimiento se refiere a una sesión que recopila información sobre el funcionamiento de SQL Server 2005. Sin embargo, antes de comenzar a recopilar información, debe configurar los parámetros de esta sesión. Esta configuración se realiza en la ventana RastroPropiedades(Propiedades de seguimiento), que se abre automáticamente antes de iniciar una sesión de seguimiento (Fig. 11.1).

Arroz. 11.1. Configuración de los parámetros de la sesión de seguimiento

En la pestaña General(General) enumerado Usarelplantilla(Usar plantilla) puede elegir la plantilla más adecuada para recopilar información dentro de su sesión. En principio, puede ignorar la configuración de la plantilla, pero definir manualmente los parámetros para recopilar información (utilizando la pestaña adyacente EventosSelección(Selección de evento)). Sin embargo, especificar la plantilla correcta puede ahorrar tiempo y evitar errores. Por lo tanto, nos detendremos en las plantillas con más detalle.

La plantilla se guarda en un archivo especial con la extensión tdf rastrear la configuración de la sesión. Trabajar con plantillas (agregar nuevas, cambiar las existentes, importar y exportar informes a otros directorios) se realiza usando el menú Archivo| Plantillas(Archivo | Plantillas) en SQL Server Profiler. Inicialmente, tienes ocho plantillas a tu disposición:

q Estándar (defecto)- como su nombre lo indica, esta plantilla es adecuada para la mayoría de situaciones y, por lo tanto, está seleccionada de forma predeterminada. Le permite rastrear todos los procedimientos almacenados y comandos Transact -SQL que se ejecutan para su ejecución;

q SP _Recuentos- Se recopila información sobre los procedimientos almacenados y las funciones lanzadas para su ejecución. En este caso, la información en la ventana del generador de perfiles se ordena (en la terminología del generador de perfiles, agrupada) por los nombres de los procedimientos almacenados;

q TSQL- Se recopila información sobre todos los comandos Transact -SQL lanzados para su ejecución en el servidor. Además del código de comando, también se registra información sobre los identificadores de los procesos del usuario y la hora de inicio. Por lo general, esta plantilla se usa para monitorear los comandos enviados al servidor por una aplicación;

q TSQL _Duración- casi lo mismo que la plantilla anterior, pero en lugar de registrar información sobre el momento en que se ejecutó el comando Transact -SQL, se registra el tiempo que tardó en ejecutarse. Normalmente, esta plantilla se utiliza para supervisar manualmente el rendimiento del servidor;

q TSQL _Agrupados- además de la información sobre el código de comando Transact -SQL y la hora de su lanzamiento, se registra información sobre el nombre de la aplicación, la cuenta de usuario en el sistema operativo y el nombre de usuario que se utilizó para conectarse. En este caso, los registros se agrupan por inicio de sesión. Normalmente, este patrón se utiliza en situaciones en las que desea realizar un seguimiento de la actividad de una aplicación específica;

q TSQL _Repetición- Se registrará la información más detallada sobre los comandos Transact -SQL ejecutados. Entonces, esta información se puede utilizar para reproducir la carga en el servidor con la máxima precisión. Por lo general, esta plantilla se usa para escribir un conjunto de comandos que luego se usarán para probar varias configuraciones del servidor desde el punto de vista del rendimiento;

q TSQL _SP- además de registrar información sobre el inicio de todo el procedimiento almacenado (evento SP: Comenzando), esta opción de seguimiento también registra información sobre la ejecución de cada comando en este procedimiento almacenado (evento SP: StmtStarting). Este patrón se utiliza normalmente para supervisar el funcionamiento de procedimientos almacenados complejos;

q Afinación- Esta plantilla está diseñada para registrar la información más adecuada para la transferencia del Asesor de optimización de la base de datos. Cómo trabajar con esta herramienta automatizada de análisis y optimización del rendimiento se discutirá en secta. 11.5.5.

Como ya se mencionó, no es en absoluto necesario limitarse solo a un conjunto de plantillas listas para usar. Puede utilizar sus propios parámetros de sesión de seguimiento configurándolos en la pestaña EventosSelección... En la tabla de esta pestaña, debe seleccionar los eventos requeridos (en filas) y la información (en columnas) que se registrará para ellos. Tenga en cuenta que solo una pequeña parte de las filas y columnas disponibles son visibles de forma predeterminada. Para habilitar la visualización de todas las filas y columnas, debe seleccionar las casillas de verificación ShowTodoEventos(Mostrar todos los eventos) y ShowTodoColumnas(Mostrar todas las columnas).

A menudo sucede que necesita rastrear solo las acciones realizadas en una base de datos específica, o una aplicación específica, o un usuario específico, o seleccionar todas estas condiciones al mismo tiempo. Los filtros para recopilar información se pueden configurar haciendo clic en el botón ColumnaFiltros(Filtros de columna) en una pestaña EventosSelección... Para cada columna, puede configurar el registro de solo ciertos valores ( Me gusta) o prohibir el registro de ciertos valores ( No como). De forma predeterminada, se configura un solo filtro. No como para columna Nombre de la aplicación... Hace que se ignoren todos los eventos de SQL Server Profiler, es decir, todos los eventos relacionados con el proceso de recopilación de información de seguimiento en sí. Es mejor no quitar este filtro, porque de lo contrario puede haber una retroalimentación positiva con un registro interminable de información.

Con un botón más OrganizarColumnas(Organizar columnas) que se encuentra en la pestaña EventosSelección, puede personalizar el orden de las columnas para mostrarlas o ingresarlas en el generador de perfiles. Presta atención a la sección Grupo(Grupo) en esta lista. Para aquellas columnas que se coloquen en él, la agrupación se realizará automáticamente. Si coloca solo una columna en esta sección, al visualizar tendrá la oportunidad de usar un modo muy conveniente AgregadoVista(Vista agregada) (cuando la información se agrupa automáticamente, por ejemplo, por base de datos, por aplicación, nombre de usuario, etc., y las entradas para la base de datos, la aplicación o el usuario deseados se pueden expandir y contraer).

Una vez que haya seleccionado la plantilla deseada o configurado su propio conjunto de eventos para el registro, solo tiene que volver a la pestaña General y configure algunos parámetros adicionales para la sesión de seguimiento.

La información de seguimiento se puede registrar en un archivo. Este archivo se puede utilizar en diferentes situaciones:

q se puede pasar como fuente de información al Asesor de optimización de la base de datos;

q se puede "reproducir" repetidamente en el generador de perfiles repitiendo todos los comandos grabados, por ejemplo, para evaluar el rendimiento en diferentes configuraciones del servidor;

q se puede presentar a los desarrolladores para respaldar sus afirmaciones sobre la aplicación.

Observemos algunos puntos relacionados con el registro de una sesión de seguimiento en un archivo:

q El tamaño de archivo predeterminado de 5 MB es muy pequeño. Al perfilar un servidor de producción, este tamaño se escribe en minutos. Es cierto, de forma predeterminada, la casilla de verificación está marcada PermitirArchivodese la vuelta(Habilite el cambio de archivos), es decir, después de completar un archivo, se creará automáticamente un segundo archivo, cuyo nombre se agregará con el número 1, luego 2, etc., pero no siempre es conveniente trabajar con un gran Número de archivos. Si está recopilando información para la transmisión del Asistente para la optimización de la base de datos, es mejor establecer el límite de tamaño del archivo en 1 GB (utilizando el parámetro ColocarmáximoArchivoTalla Ficha (Personalizar tamaño máximo de archivo) General). La grabación de seguimiento en un archivo se realiza con mayor frecuencia desde la estación de trabajo del administrador, por lo que se requerirá espacio en disco en la estación de trabajo y no en el servidor;

parámetro q Servidorprocesosrastrodatos(El servidor procesa los datos de seguimiento) se puede utilizar para aumentar la confiabilidad del registro de la información de seguimiento. De forma predeterminada, SQL Server Profiler maneja el procesamiento de datos de seguimiento en la computadora en la que se está ejecutando (no necesariamente en el servidor). Si selecciona esta casilla de verificación, el servidor se encargará del procesamiento de la información de seguimiento. Esto asegura que se recopilará toda la información de seguimiento (si la casilla de verificación no está marcada en momentos de carga máxima del servidor, es posible que se omita parte de la información), pero aumentará la carga en el servidor.

Otra opción para escribir información de seguimiento es escribir en una tabla de SQL Server. La tabla con el conjunto de columnas requerido se creará automáticamente. Solo puede personalizar el número máximo de entradas en esta tabla. Tenga en cuenta que en esta pestaña, el número máximo de registros se indica en miles.

El último parámetro de la pestaña General- PermitirRastrodetenerhora(Incluya la hora de finalización del seguimiento). Puede especificar la hora en la que el seguimiento se desactivará automáticamente. Por lo general, tiene sentido desactivar el seguimiento antes de iniciar cualquier operación de servicio que no le interese desde el punto de vista del registro (copia de seguridad, carga masiva de datos, procesamiento de cubos OLAP, etc.).

Una vez configurados todos los parámetros de seguimiento, puede hacer clic en el botón Correr Pestaña (Ejecutar) General y comience a rastrear (Fig. 11.2).

Arroz. 11.2. Visualización de información durante una sesión de seguimiento

El trabajo en el visor de información de seguimiento es bastante obvio: la parte superior muestra los eventos que ocurren en el servidor y la parte inferior proporciona información detallada sobre ellos (por ejemplo, el código de los comandos SQL). Observemos algunas de las posibilidades disponibles en esta ventana:

q si la pestaña OrganizarColumnas en las propiedades de la plantilla, seleccionó las columnas que desea agrupar, luego puede agrupar por estas columnas los registros en el visor. Para este propósito en el menú Vista Comando (Ver) proporcionado AgrupadosVista(Vista agrupada);

q si en la misma pestaña en las propiedades de la plantilla a la lista Grupo solo se ha colocado una columna, entonces puede usar un modo de visualización aún más conveniente AgregadoVista(figura 11.3). Este modo se habilita usando el comando AgregadoVista del mismo menú Vista y le permite convertir los valores de la columna de su elección en nodos de árbol que puede contraer y expandir. Además, el número de eventos se calcula automáticamente para cada uno de estos nodos.

Arroz. 11.3. Modo de visualización AgregadoVista

q En el generador de perfiles, puede mostrar no solo los eventos que se acaban de capturar, sino también los archivos guardados y las tablas de seguimiento. Alternativamente, puede abrir scripts regulares de SQL Server con comandos Transact -SQL. La información de estos archivos o tablas se puede utilizar para repetir las operaciones registradas. Los comandos de menú están destinados a este fin. Repetición(Repetir);

q SQL Server 2005 Profiler presenta una nueva función para asociar la información de seguimiento con contadores de rendimiento en el Monitor de rendimiento. Para aprovechar esta oportunidad, necesita:

Definir una sesión de seguimiento durante la cual se debe registrar la información de las columnas. Hora de inicio y Hora de finalización;

· Inicie una sesión de seguimiento con la grabación de información en un archivo o tabla. Recopile simultáneamente las lecturas del medidor del Monitor de rendimiento en un archivo;

Abra la información recopilada del archivo de seguimiento en el generador de perfiles y luego use el comando ImportarRendimientoDatos(Importar datos de rendimiento) en el menú Archivo.

SQL Server 2005 proporciona un marcador de posición para el generador de perfiles. Estos son procedimientos almacenados de seguimiento. Su funcionalidad es casi idéntica a la del perfilador. Por ejemplo, también puede seleccionar eventos para rastrearlos y escribirlos en un archivo de texto. La principal diferencia es que todos los ajustes deberán realizarse desde el código Transact -SQL.

Los procedimientos almacenados de rastreo son más difíciles y menos convenientes para trabajar que con un generador de perfiles, y no proporcionan características adicionales. Por lo tanto, no los consideraremos en detalle. Aquí hay solo una lista de dichos procedimientos almacenados con una breve descripción:

q sp_trace_create- le permite configurar los parámetros de la sesión de seguimiento;

q sp_trace_setevent- le permite seleccionar los eventos necesarios para la sesión de seguimiento creada;

q sp_trace_setfilter- le permite configurar un filtro para recopilar información de seguimiento;

q sp_trace_setstatus- le permite iniciar un seguimiento, detenerlo o eliminar un seguimiento creado por un procedimiento almacenado sp_trace_create definición de sesión actual;

q sp_trace_generateevent- le permite generar un evento personalizado que será interceptado durante el rastreo.

En ediciones anteriores (consulte ComputerPress # 1, 3-5, 7, 9'2006), discutimos problemas relacionados con la transición a SQL Server 2005 por migración o actualización, así como los principales escenarios para usar SQL Server 2005. Este artículo se centrará en varios métodos de optimización de consultas y posibles enfoques para resolver problemas asociados con la búsqueda de problemas de rendimiento.

SQL Server proporciona servicios que se ejecutan en un entorno dinámico y en constante cambio. Por lo tanto, el monitoreo regular del funcionamiento del servidor le permite identificar problemas en la etapa de su aparición y tomar medidas rápidamente para eliminarlos. Con la acumulación de estadísticas, aparece una comprensión de las principales tendencias en el funcionamiento del servidor. La recopilación de datos con regularidad, incluso cuando el servidor está funcionando sin problemas, creará una línea de base de rendimiento del servidor que puede servir como punto de referencia para mediciones adicionales del rendimiento del servidor.

Una vez que tenga un punto de referencia de rendimiento de referencia, obtendrá la capacidad de analizar el rendimiento de las consultas en una variedad de escenarios. Se recomienda que las mediciones se tomen al menos antes, durante y después de que se hayan realizado los diversos procedimientos de optimización para asegurar que los pasos tomados sean exitosos. Además, las mediciones de rendimiento estándar regulares (semanales, mensuales, trimestrales, etc.) proporcionarán una imagen más clara del rendimiento del servidor.

La línea de base se puede utilizar para determinar las horas pico y no pico del servidor, el tiempo de respuesta de las solicitudes y los paquetes de comandos, el tiempo que se tarda en realizar una copia de seguridad y restaurar los datos, etc.

Rendimiento de la consulta

El rendimiento de las solicitudes debe considerarse desde dos puntos de vista: los recursos utilizados para cumplir con una solicitud en particular (los recursos incluyen objetos a los que accedemos durante la ejecución de una solicitud, objetos bloqueados, etc.) y el tiempo dedicado a ejecutar la solicitud. , - cuanto menos tiempo lleve completar una solicitud, menor será la probabilidad de que bloqueemos otras solicitudes y transacciones durante el proceso de solicitud.

SQL Server 2005 incluye dos herramientas principales para medir el rendimiento de las consultas: Monitor de rendimiento y SQL Server Profiler. Además, las construcciones T-SQL del grupo SET STATISTICS (SET STATISTICS IO, SET STATISTICS PROFILE y SET STATISTICS TIME) y las vistas de administración dinámica se pueden utilizar para obtener información sobre la ejecución de consultas. A continuación, analizaremos más de cerca el uso de Performance Monitor y SQL Server Profiler.

Usar el Monitor de rendimiento

La utilidad Monitor de rendimiento se utiliza para analizar el rendimiento de los recursos de hardware y software, incluida la memoria, la utilización de la red, el tiempo del procesador y la información relacionada con el funcionamiento de SQL Server y otros productos de software: Microsoft Message Queue Server (MSMQ), Microsoft .NET Framework y Microsoft Exchange Server. En concreto, puede utilizar el Monitor de rendimiento para supervisar los recursos de SQL Server, como bloqueos y transacciones.

Para agregar los contadores que nos interesan, debe realizar los siguientes pasos:

En el menú Comienzo selecciona un equipo Correr y en el cuadro de diálogo Correr presentar perfmon... Esto inicia Microsoft Performance Management Console (MMC), que muestra una vista gráfica de varios contadores de rendimiento.
En la ventana Monitor del sistema haga clic con el botón derecho en el gráfico y seleccione el comando Agregar contadores.
En el cuadro de diálogo Agregar contadores elegir el equipo y objeto de nuestro interés para su seguimiento. Los objetos específicos de SQL Server tienen el prefijo correspondiente.
Para seleccionar contadores, debe seleccionar la opción Todos los contadores, o uno o más contadores de la lista desplegable (Fig. 1).

Después de eso, debe seleccionar las instancias de la base de datos o habilitar la opción Todas las instancias.
Al hacer clic en los botones Agregar y Cerrar, se completa la adición de contadores.

Después de agregar los contadores que nos interesan, podemos ver sus valores en forma de gráfico o guardarlos en un archivo de seguimiento (Fig. 2).

En este último caso, es necesario expandir el elemento Registros y alertas de rendimiento y haga clic en el comando Nueva configuración de registro... En el cuadro de diálogo Nueva configuración de registro debe especificar el nombre del protocolo y hacer clic en el botón Aceptar. Más adelante en el cuadro de diálogo CounterLogName elegimos el equipo Agregar contadores... Agregar los contadores que nos interesan es lo mismo que se describió anteriormente. Tenga en cuenta que una gran cantidad de contadores puede afectar el rendimiento del sistema. Cuando termine de agregar contadores, haga clic en Cerca.

En la pestaña General En el capítulo Datos de muestra cada puede configurar la frecuencia de muestreo (captura de datos). Se recomienda comenzar con una frecuencia promedio, por ejemplo cada 5 minutos, y luego, si es necesario, disminuirla o aumentarla. Tenga en cuenta que cuanto más corto sea el intervalo de muestreo, más recursos del sistema y del disco se necesitarán. Recuerde que los intervalos más cortos que el período de cuantificación del contador también pueden resultar en una visualización incorrecta de los datos. En la pestaña Archivos de registro puede configurar las propiedades del archivo de registro, y en Calendario Se establece el programa de seguimiento. Al presionar el botón OK, se crea un protocolo al comienzo de la recopilación de datos (Fig. 3).

Para finalizar el registro manualmente, debe hacer clic con el botón derecho en el elemento Registros de contador y elige un equipo Detener.

Para ver los datos recopilados en el archivo de registro en el Monitor de rendimiento, seleccione el elemento Monitor del sistema, en la barra de tareas - el comando Ver datos de registro, y en el cuadro de diálogo Propiedades del monitor del sistema en la pestaña Fuente especifique el nombre del archivo de registro.

Los contadores del Monitor de rendimiento más utilizados, a partir de los cuales puede utilizar la información para identificar problemas de rendimiento, se dividen en las siguientes categorías:

acceso a datos: un objeto se utiliza para auditar los métodos de acceso a datos utilizados por el propio SQL Server SQLServer: métodos de acceso;
Estadísticas: un objeto se utiliza para monitorear la compilación y recompilación de consultas. SQLServer: Estadísticas SQL... Proporciona información sobre la rapidez y eficacia con la que SQL Server procesa las consultas;
transacciones: los contadores relacionados con los objetos se utilizan para determinar el número de transacciones por segundo SQLServer: Bases de datos y SQLServer: Transacciones;
bloqueos: el objeto se utiliza para auditar los bloqueos de SQL Server colocados en tipos de recursos específicos SQLServer: bloqueos(Tabla 1).

Tabla 1. Contadores de SQL Server Profiler

Nombre del contador	Descripción

	Cuenta escaneos de rango para índices por segundo
	Cuenta el número de escaneos completos completados en el último segundo
Búsquedas de índice / seg	Cuenta el número de búsquedas de índices en el último segundo
Escaladas de bloqueo de tabla / seg	Cuenta el número de cerraduras de una mesa.
Mesas de trabajo creadas / seg	Cuenta el número de hojas de trabajo creadas en el último segundo

Solicitudes por lotes / seg	Cuenta el número de paquetes de comandos Transact-SQL por segundo. Una gran cantidad de paquetes significa un buen rendimiento
Compilaciones SQL / seg	Cuenta el número de compilaciones de consultas por segundo. El valor de este contador debería volverse casi constante después de que el usuario haya realizado acciones básicas.
Re-compilaciones SQL / seg	Cuenta el número de solicitudes recompiladas por segundo

Objeto SQLServer: Bases de datos. Contador de transacciones / seg	Cuenta el número de transacciones iniciadas en la base de datos en el último segundo
Objeto SQLServer: Transacciones. Contador del tiempo de ejecución de transacción más largo	Calcula el número de segundos desde el inicio de una transacción que ha estado activa por más tiempo que cualquier otra transacción actual. Si este contador muestra una transacción muy larga, utilice el procedimiento almacenado del sistema sys.dm_tran_active_transactions () para obtener información sobre esta transacción.
Objeto SQLServer: Transacciones. Actualizar contador de tasa de conflicto	Calcula el porcentaje de transacciones que utilizan el aislamiento de instantáneas para resolver los conflictos que ocurren al actualizar los datos en el último segundo.

Tiempo medio de espera (ms)	Calcula el tiempo medio de espera para cada solicitud de bloqueo que provocó la espera.
Bloquear solicitudes / seg	Cuenta el número de bloqueos y conversiones de bloqueos por segundo
Tiempo de espera de bloqueo (ms)	Calcula la espera acumulada de bloqueos en el último segundo
	Cuenta la cantidad de solicitudes de bloqueo por segundo que resultaron en una espera

La segunda utilidad que puede utilizar para medir el rendimiento de las consultas es SQL Server Profiler. A continuación, veremos las principales formas de usarlo.

Usando SQL Server Profiler

SQL Server Profiler mide el rendimiento de consultas individuales y consultas que forman parte de procedimientos almacenados y paquetes de comandos Transact-SQL. Se puede utilizar para recopilar información de rendimiento, incluido el tiempo necesario para ejecutar un comando individual, el tiempo de bloqueo del comando y el plan de ejecución.

Para crear un nuevo perfil, debe seguir una serie de los siguientes pasos:

Ejecute SQL Server Profiler ( Microsoft SQL Server 2005 => Herramientas de rendimiento => SQL Server Profiler).
En el menú Archivo selecciona un equipo Nuevo rastro.
En el cuadro de diálogo Conectar al servidor seleccione el servidor requerido y haga clic en el botón Conectar.
En el cuadro de diálogo Propiedades de seguimiento necesitas establecer el nombre del perfil (Nombre de seguimiento), y en la lista Usa la plantilla elija una de las plantillas disponibles o Blanco si no se utiliza ninguna plantilla.
Para guardar los resultados de la elaboración de perfiles, debe seleccionar la opción Guardar en archivo para escribir datos en un archivo y especificar el tamaño máximo del archivo (el valor predeterminado es 5 MB), opcionalmente puede habilitar la opción Habilitar rollover de archivos para crear automáticamente un nuevo archivo cuando se alcanza el tamaño de perfil especificado. La segunda posibilidad es almacenar datos en una tabla de base de datos. (Guardar en la mesa)- opción Establecer filas máximas le permite establecer el número máximo de registros.
Para establecer la hora de finalización del seguimiento, puede utilizar la opción Habilitar el tiempo de parada de seguimiento(figura 4).

Para definir eventos y columnas de datos para un perfil, debe realizar los siguientes pasos:

En el cuadro de diálogo Propiedades de seguimiento ir a la pestaña Selección de eventos.
Agregue o elimine eventos del perfil usando la tabla de clases de eventos.
Para obtener una lista de todos los eventos disponibles, habilite la opción Mostrar todos los eventos(figura 5).

Mesa La Tabla 2 enumera los eventos de SQL Server Profiler más utilizados. Al igual que los contadores de rendimiento, los eventos de SQL Server Profiler se dividen en varias categorías, algunas de las cuales son de interés para nuestros propósitos.

Tabla 2. Eventos del generador de perfiles de SQL Server

Nombre del evento	Descripción

	Este evento ocurre cuando se completa una llamada a procedimiento remoto.
	Este evento se desencadena cuando el procedimiento almacenado ha terminado de ejecutarse.
SP: StmtCompleted	Este evento ocurre cuando uno de los comandos Transact-SQL dentro de un procedimiento almacenado ha terminado de ejecutarse.

SQL: StmtCompleted	Este evento se desencadena cuando se completa el comando Transact-SQL.
SQL: BatchCompleted	Este evento ocurre cuando un lote de comandos Transact-SQL ha terminado de ejecutarse.

	Este evento se activa cuando una transacción adquiere un bloqueo en algún recurso
	Este evento ocurre cuando una transacción libera un recurso previamente bloqueado.
	Este evento se activa cuando se excede el tiempo de espera para adquirir un bloqueo, ya que el recurso requerido ya está bloqueado por otra transacción.

Cómo determinar si hay bloqueos

Cada vez que una transacción utiliza un recurso (tabla, página, índice, etc.), se le establece un bloqueo. Si otra transacción intenta acceder a este recurso y el tipo de bloqueo es incompatible con el bloqueo ya retenido, se crea un nuevo bloqueo.

Se pueden utilizar los siguientes métodos para determinar la presencia de bloqueos:

utilice la utilidad SQL Server Management Studio Activity Monitor, que muestra información sobre procesos, bloqueos a nivel de proceso y a nivel de objeto. Para acceder al Monitor de actividad en SQL Server Management Studio, seleccione el elemento Gestión, y en él - Monitor de actividad y haga doble clic en ese elemento. El Monitor de actividad le permite ver:

Objetos bloqueados por proceso: use el ID de proceso para identificar la solicitud que causó el bloqueo ID de proceso del servidor (SPID) En la pagina Información del proceso,

Procesos que provocan que otros procesos esperen: utilice la columna para identificar dichos procesos. Bloqueado por En la pagina Información del proceso(figura 6);

use SQL Server Profiler para obtener un informe de procesos bloqueados: esta lista muestra información sobre los procesos que se han bloqueado durante más tiempo del especificado O

Este artículo contiene varios materiales publicados en la lista de correo "MS SQL Server es un asunto delicado ..." y está dedicado a los problemas de monitoreo de la efectividad de MS SQL Server, métodos para descubrir las causas de los problemas de hardware y su resolución. Además, se le ofrecerán varias recomendaciones y advertencias útiles y prácticas.

Si su servidor de base de datos utiliza E / S de forma intensiva, puede cambiar la configuración del límite de bloqueo de página de E / S del sistema operativo, lo que puede aumentar la tasa efectiva de lectura / escritura del sistema operativo en el disco duro.
Primero, compare las E / S para la carga típica de su servidor. Luego, en regedit.exe abre la clave:

HKLM \ SYSTEM \ CurrentControlSet \ Control \ SessionManager \ MemoryManagement \ IoPageLockLimit

El objetivo de sus acciones es la selección paso a paso de los valores de esta clave para obtener el valor más óptimo en términos de cambios en los resultados de la evaluación comparativa.
En esta clave, el sistema operativo lee la cantidad máxima de bytes que puede usar para operaciones de E / S. El valor predeterminado es 0, que corresponde a 512 KB. Aumente este valor en pasos, agregando 512 KB cada vez (por ejemplo: "512", "1024", etc.) y compare su sistema después de cada cambio. Tiene sentido aumentar este parámetro solo mientras observe un aumento en el rendimiento de las operaciones de E / S, que puede manifestarse en una disminución en el tiempo dedicado a las operaciones de disco estándar. Cuando ya no vea una mejora significativa, regrese al Editor del Registro y elimine el último incremento.

Advertencia: Hay un límite en el tamaño máximo del valor de esta clave. Si tiene 16 MB de RAM, no configure IoPageLockLimit más de 2048 bytes; para 32 MB de RAM, no supere los 4096 bytes, etc.

Nota IMPORTANTE:

Las operaciones enumeradas por Sergey, si se utilizan de forma incorrecta o errónea, pueden provocar el colapso de su sistema. Por lo tanto, cuide la disponibilidad de las copias de seguridad de la base de datos y del sistema con anticipación, guarde la configuración de NT que funcione y simule los posibles cambios en el sitio de prueba. Nunca realice más de un cambio de configuración a la vez.