SQL Server: Búsqueda de problemas de rendimiento en lo más hondo
Hay varias tácticas para determinar qué está causando la mayoría de las veces espera en SQL Server, así que usted puede dirigir su ajuste del rendimiento.
Extractado deSQL Server DMV Starter Pack," publicado por Red Gate Books (2010).
Glenn Berry, Louis Davidson y Tim Ford
Vayamos hasta el nivel de sistema operativo. Buscamos en los subprocesos de trabajo que realizan las tareas requeridas por nuestras operaciones. Los objetos de administración dinámica (OGD) en esta categoría — todos que comienzan con el nombre sys.dm_os_—provide extremadamente detallan información acerca de la forma SQL Server interactúa con el sistema operativo y el hardware. Puede utilizar estos DMOs para obtener respuestas a todo tipo de preguntas de rendimiento:
- El wait_stats DMO te dice qué tipo de cosas las roscas SQL Server OS han estado esperando.
- Performance_counters dice que los valores de contador de rendimiento SQL Server y cómo ellos son decodificados.
- Ring_buffers, os_schedulers o wait_stats te alertará a una preocupación de la CPU.
- Sys_info le dará las características de la máquina sobre la cual SQL Server se está ejecutando.
- Sys_memory o process_memory te dirá cómo se está utilizando la memoria como un todo.
- Memory_cache_counters o buffer_descriptors decirle cómo la memoria caché está siendo utilizada.
Todas estas consultas trabajarcon con SQL Server 2005, 2008 y 2008 R2. También todos requieren permiso View Server State. Nota: Los objetos, colectivamente, deberían denominarse DMOs. Sin embargo, usando las OGD tiende a causar cierta confusión con el totalmente sin relación "objetos de administración distribuida," todavía es muy común para que los administradores de bases de datos se refieren a las OGD colectivamente como "Vistas de administración dinámica, o DMV".
SQL Server espera
Podría decirse que el DMV más significativo en la categoría sistema operativo es sys.dm_os_wait_stats. Cada vez que una sesión tiene que esperar por algún motivo antes de continuar el trabajo solicitado, SQL Server registra la longitud del tiempo esperado y el recurso que está esperando SQL Server . El sys.dm_os_wait_stats DMV expone estos esperen estadísticas agregadas a través de todas las identificaciones de sesión, para darle una revisión sumaria de donde espera el principal está en una instancia determinada.
Este mismo DMV también expone los contadores de rendimiento (PerfMon), que pueden suministrar medidas de uso de recursos específicos (tasas de transferencia de disco, cantidad de tiempo de CPU consumido y así sucesivamente). Al correlacionar las estadísticas espera con las mediciones de recursos, puede rápidamente localizar los recursos más controvertidos en su sistema y resaltar los cuellos de botella potenciales.
Esperas y colas
Uso de esperas y colas es una base excelente para el tuning de performance, como se explica en el white paperSQL Server 2005 espera y colas," por Tom Davidson. Esencialmente, cada solicitud de SQL Server se traduce en una serie de "trabajador tareas" iniciada. Un programador de SQL Server asigna cada tarea a un subproceso de trabajo. Normalmente hay un programador SQL OS por CPU, y sólo una sesión por programador puede ejecutar en cualquier momento.
Es del planificador repartir uniformemente la carga de trabajo en los subprocesos de trabajo disponibles. Si el subproceso de una sesión trabajo se ejecuta en el procesador, el estado de la sesión va a correr, como expuesto por la columna Estado de sys.dm_exec_requests DMV.
Si un subproceso está listo para ir, pero el programador para que se asigna actualmente tiene otra sesión ejecutando, será colocado en la cola "ejecutable". Esto simplemente significa que está en la cola para conseguir en el procesador. Esto se llama una espera de señal.
Señal de espera
El tiempo de espera de señal es expuesto por la columna signal_wait_time_ms. Esto se refiere únicamente a esperar el tiempo de CPU. Si una sesión está esperando otro recurso a ser disponibles, por ejemplo una página bloqueada, o si necesita una sesión corriente realizar I/O, luego pasa a la lista de espera. Esta es una espera de recursos y estado de la sesión de espera se registrarán como "suspendida". El motivo de la espera es registrado y expuesto en la columna wait_type de la sys.dm_os_wait_stats DMV.
El tiempo total gastado esperando es expuesto por la columna de wait_time_ms, así se puede calcular el tiempo de espera de recursos, como sigue:
Recursos espera = Total espera – espera señal (o (wait_time_ms) - (signal_wait_time_ms))
Señal esperas son inevitables en transacciones en línea (OLTP) sistemas, de procesamiento como éstos están constituidos por un gran número de transacciones cortas. La métrica clave, con respecto a la posible presión de CPU, es la espera de la señal como un porcentaje de las esperas totales.
Una alto porcentaje de señal es una señal de la presión de la CPU. "Alto" se refiere a menudo a más del 25 por ciento, pero depende de tu sistema. Valores mayores que 10 por ciento a 15 por ciento también pueden ser un signo preocupante. En general, espera las estadísticas son un medio eficaz para diagnosticar los tiempos de respuesta en su sistema. En términos muy simples, trabajo o te espera.
Si los tiempos de respuesta son lentos y no puede encontrar espera significativa, o principalmente espera señal, sabes que tienes que concentrarte en la CPU. Si encuentras el tiempo de respuesta se compone principalmente de tiempo esperando otros recursos (por ejemplo, red, E/S y así sucesivamente), entonces ya sabes que tienes que enfocar sus esfuerzos de adaptación de esos recursos.
Perfiles de rendimiento
Nuestro primer guión en la categoría sistema operativo utiliza el sys.dm_os_wait_stats DMV, que devuelve información sobre todas las esperas encontradas por hilos de ejecución. Puede utilizar esta vista agregada para diagnosticar problemas de rendimiento con el SQL Server global y con lotes y consultas específicas.
Esta simple consulta calcula señal espera y espera recursos como un porcentaje del tiempo de espera total, con el fin de diagnosticar la presión potencial de CPU:
-- Total waits are wait_time_ms (high signal waits indicate CPU pressure) SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] , CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]FROM sys.dm_os_wait_stats ;
Esto es útil para ayudar a confirmar la presión de la CPU. Porque espera señal es tiempo esperando una CPU al servicio de un hilo, si grabas señal total espera por encima de aproximadamente 10 a 15 por ciento, que es un buen indicador de la presión de la CPU. Estos esperen las estadísticas son acumulativos desde SQL Server última fue recomenzado, así que tienes que saber tu referencia valor para señal espera y ver la tendencia en el tiempo.
Puede borrar manualmente las estadísticas de espera, sin reiniciar el servidor, mediante la emisión de una consistencia de la base de datos comprobación comando SQLPERF (DBCC), como sigue:
-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;
Si la instancia del SQL Server ha estado funcionando por un buen rato y hacer un cambio significativo, como la adición de un nuevo índice importante, usted debe considerar despejando las viejas estadísticas de espera. De lo contrario, las estadísticas acumuladas espera viejo enmascarará cualquier impacto el cambio en los tiempos de espera.
Nuestro segundo script de ejemplo (ver figura 1) se muestra cómo utilizar el sys.dm_os_wait_stats DMV ayudará a determinar los recursos para que SQL Server está dedicando más tiempo esperando.
Figura 1 este script genera un informe sobre las causas principales de la espera.
-- Isolate top waits for server instance since last restart -- or statistics clear WITH Waits AS ( SELECT wait_type , wait_time_ms / 1000. AS wait_time_s , 100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct , ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN' ) ) SELECT W1.wait_type , CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s , CAST(W1.pct AS DECIMAL(12, 2)) AS pct , CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn , W1.wait_type , W1.wait_time_s , W1.pct HAVING SUM(W2.pct) - W1.pct < 95 ; -- percentage threshold
La secuencia de comandos en figura 1 le ayuda a localizar los cuellos de botella más grandes a nivel de instancia. Esto ayuda a enfocar sus esfuerzos de ajuste el tipo de problema particular. Por ejemplo, si la cima acumulativa espera tipos son disco I/O-relativos, entonces te gustaría investigar este asunto utilizando más relacionados con los discos DMV consultas y contadores PerfMon.
Contadores de rendimiento
El DMV que expone los contadores del monitor de rendimiento es sys.dm_os_performance_counters. Esto devuelve una fila por el contador de rendimiento mantenido por el servidor. Esto es un útil DMV, pero puede ser frustrante trabajar con.
Dependiendo del valor de cntr_type para un renglon, tendrás que cavar profundo para obtener información significativa de esta DMV. Es un reemplazo para la vieja sys.sysperfinfo de SQL Server 2000.
La secuencia de comandos en figura 2 te ayuda a investigar las condiciones inusuales llenando su registro de transacciones. Devuelve el modelo de recuperación, registro reutilización espera Descripción, tamaño del registro de transacciones, espacio del registro utilizado, porcentaje de registro utilizado, nivel de compatibilidad y página verificar opción para cada base de datos en la instancia actual del SQL Server .
Figura 2 Determine lo que se está llenando el registro de transacciones mediante este guión
-- Recovery model, log reuse wait description, log file size, -- log usage size and compatibility level for all databases on instance SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] , db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] , lu.cntr_value AS [Log Used (KB)] , CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] , db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]FROM sys.databases AS db INNER JOIN sys.dm_os_performance_counters AS lu ON db. name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls ON db. name = ls.instance_nameWHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’ AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;
La consulta en figura 2 es útil para la evaluación de un servidor de base de datos desconocidos. También es generalmente más útil desde una perspectiva de monitoreo. Por ejemplo, si espera su reutilización registro descripción es algo inusual como ACTIVE_TRANSACTION y su registro de transacciones es completo el 85 por ciento, entonces debería haber unas campanas de alarma sonando.
Usando este tipo de monitoreo es extremadamente útil para la identificación de las fuentes y causas de esperas y otros problemas de rendimiento. Este tipo de investigación puede ayudarle a dirigir mejor sus esfuerzos de tuning de la performance en el lugar correcto.
.png)
Glenn Berry trabaja como arquitecto de base de datos en NewsGator Technologies Inc. en Denver, Colorado. Es un SQL Server MVP y tiene toda una colección de las certificaciones de Microsoft, incluyendo MCITP, MCDBA, MCSE, MCSD, MCAD y MCTS, que demuestra que le gusta tomar pruebas.
.png)
Louis Davidson ha sido durante 16 años en la industria como una base de datos corporativa desarrollador y arquitecto. Ha sido un SQL Server Microsoft MVP durante seis años y ha escrito cuatro libros sobre diseño de base de datos. En la actualidad es el arquitecto de datos y a veces DBA durante la Christian Broadcasting Network, apoyando a las oficinas en Virginia Beach, Virginia y Nashville, Tenn.
.png)
Timothy Ford es un SQL Server MVP y ha estado trabajando con SQL Server durante más de 10 años. Es la primaria DBA y experto en materia para la plataforma de SQL Server para la salud de espectro. Ha sido escrito acerca de la tecnología desde el año 2007 para una variedad de sitios Web y mantiene su propio blog en thesqlagentman.com, cubriendo SQL como temas de desarrollo así como teletrabajo y profesional.
Aprenda más sobre "DMVSQL Server Starter Pack" en rojo-gate.com.