SQL Server: Desmitificación de los retrasos en SQL Server
Puede usar los datos de seguimiento de los tiempos de espera en las transacciones con SQL Server para descubrir los recursos sobreutilizados y eliminar los cuellos de botella.
Extraído de "SQL Server DMV Starter Pack", publicado por libros de puerta roja (2010).
Glenn Berry, Louis Davidson y Tim Ford
Cada vez que una sesión de SQL Server tiene que esperar por algún motivo antes de continúan con los trabajos solicitados, SQL Server registra la longitud de tiempo esperado. También registra el recurso que está esperando la transacción.
La gestión dinámica de sys.dm_os_wait_stats ver expone (DMV) estos esperar estadísticas, agregadas en todas las sesiones, para proporcionar una revisión resumida de donde espera principal es en cualquier caso dado. Este mismo DMV también expone a los contadores de rendimiento (PerfMon), que proporcionan recursos específicos de mediciones de uso (las velocidades de transferencia de disco, la cantidad de tiempo de CPU consumido y así sucesivamente).
Al correlacionar las estadísticas espera con mediciones de recursos, puede rápidamente localizar los recursos más "impugnados" en su sistema y resaltar posibles cuellos de botella.
Colas y esperas SQL Server 2005
El uso de "espera y colas" como la base de una metodología de optimización de la performance se explica en un excelente libro blanco por Tom Davidson. Esencialmente, cada solicitud al servidor SQL Server tendrá como resultado el inicio de una serie de "trabajador tareas."
Un programador de tareas de SQL Server asigna a cada tarea a un subproceso de trabajo. Normalmente es un programador SQL OS por CPU y solamente una sesión por el programador que se ejecuta en cualquier momento. Es trabajo del programador para distribuir la carga uniformemente entre los subprocesos disponibles.
Si el subproceso de trabajo de la sesión se ejecuta en el procesador, la sesión Estado va a "ejecutar," expuesto por la columna de estado de sys.dm_exec_requests DMV. Si un subproceso está listo para ir, pero el programador al que está asignado actualmente tiene otra sesión ejecutando, entonces se colocará en la cola de "Runnable". Esto significa que está en la cola del procesador. Esto se conoce como una señal de espera.
El tiempo de espera de la señal está expuesto por la columna de signal_wait_time_ms y es tiempo de espera de CPU únicamente. Si una sesión está esperando otro recurso esté disponible para proceder, como una página bloqueada, o si necesita una sesión de ejecución para realizar operaciones de E/s, se mueve a la lista de espera. Se trata de una espera de recursos y estado de la sesión de espera se registrarán como "Suspendida".
El motivo de la espera es grabado y expuesto en la columna wait_type de la sys.dm_os_wait_stats DMV. El tiempo total empleado espera está expuesta por la columna wait_time_ms, por lo que se puede calcular el tiempo de espera de recursos como sigue:
Recursos espera = Total espera: espera de señal = (wait_time_ms)-(signal_wait_time_ms)
Señal esperas son inevitables en transacciones en línea (OLTP) sistemas, que están compuestos por un gran número de transacciones cortas de procesamiento. La métrica clave, con respecto a la posible presión de la CPU, es la espera de la señal como un porcentaje del espera total. Una señal de alto porcentaje es un signo de presión excesiva de la CPU. La literatura tiende a citar "alto" como más de un 25%, pero depende de tu sistema.
En nuestros sistemas, tratamos los valores superiores a 10 por ciento a 15 por ciento como un signo preocupante. En general, el uso de las estadísticas de espera representa un medio muy eficaz de diagnóstico de tiempos de respuesta en el sistema. En términos simples, trabajo o te esperas. Tiempo de respuesta es igual tiempo de servicio y el tiempo de espera.
Si los tiempos de respuesta son lentos y encuentra espera significativo, ni esperas de señal principalmente, necesita concentrarse en CPU. Si encuentras tiempo de respuesta se compone principalmente de tiempo esperando otros recursos (como la red, I/O, etc.), entonces usted sabe exactamente donde concentrar sus esfuerzos de tuning.
Sacar las suposiciones
Mario Broodbakker ha escrito una excelente introducción serie de artículos sobre el uso de eventos espera para diagnosticar problemas de rendimiento. Nuestro primer guión en la categoría de OS utiliza la sys.dm_os_wait_stats DMV, que se define como: Devuelve información acerca de todas las esperas tropezado los subprocesos que se ejecutan. Puede utilizar esta vista agregada para diagnosticar problemas de rendimiento con SQL Server y también con lotes y consultas específicas.
Esta consulta calcula esperas de señal y recursos espera como un porcentaje del tiempo de espera total, a fin de diagnosticar posibles presiones de CPU:
-- Total waits are wait_time_ms (high signal waits indicates 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 ;
Esta consulta 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 graba espera señal total que es aproximadamente más de 10% a 15%, entonces este es un buen indicador de presión de la CPU.
Estos esperar Estadisticas son acumulativos desde última que SQL Server se reinició, por lo que necesita saber su referencia de valor para la espera de la señal y ver la tendencia en el tiempo. Puede borrar manualmente las estadísticas de la espera, sin necesidad de reiniciar el servidor, emitiendo un comando DBCC SQLPERF, como sigue:
-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;
Si la instancia de SQL Server ha estado funcionando durante bastante tiempo, y realizar un cambio significativo como añadir un nuevo índice, entonces debe considerar la posibilidad de borrar el viejo espera Estadisticas. De lo contrario, los viejo Estadisticas espera acumulado serán enmascarar cualquier impacto el cambio en los tiempos de espera.
Nuestro segundo script de ejemplo utilizando el sys.dm_os_wait_stats DMV ayudará a determinar los recursos que SQL Server está dedicando más tiempo de 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
Este script le ayudará a localizar el cuello de botella más grande a nivel de instancia. Esto le permite concentrar sus esfuerzos de tuning en un determinado tipo de problema. Por ejemplo, si la parte superior acumulativa espera tipos son disco / O-relacionadas y, a continuación, desea investigar esta cuestión aún más mediante las consultas relacionadas con el disco DMV y contadores PerfMon.
Exponer los contadores de rendimiento
El DMV que expone los contadores PerfMon es sys.dm_os_performance_counters, que se describe como: "Devuelve una fila por contador de rendimiento mantenido por el servidor." Se trata de un útil DMV, pero puede ser frustrante utilizar. Dependiendo del valor de cntr_type para una fila determinada, tendrás que pasar por algunos aciertan interesante para obtener información significativa de este DMV. Es un reemplazo para el viejo sys.sysperfinfo de SQL Server 2000.
Este script le ayuda a investigar condiciones inusuales llenando su registro de transacciones. Devuelve el modelo de recuperación, descripción del registro de reutilización espera, tamaño del registro de transacciones, espacio de registro utilizado, porcentaje de registro utilizado, nivel de compatibilidad y página comprobar opción para cada base de datos en la instancia actual de SQL Server:
-- 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)%' ;
Esta consulta permite evaluar un servidor de base de datos no están familiarizados. También es útil desde una perspectiva de supervisión. Por ejemplo, si espera su reutilización de registro descripción es algo inusual y su registro de transacciones es del 85 por ciento completo, que debe activar una alarma.
.png)
Glenn Berry funciona como un arquitecto de base de datos en NewsGator Technologies en Denver, Colorado Es un MVP de SQL Server y tiene una colección completa de certificaciones de Microsoft, incluyendo MCITP, MCDBA, MCSE, MCSD, MCAD y MCTS, que demuestra que a él le gusta tomar exámenes.
.png)
Louis Davidson ha sido en la industria de TI durante 16 años como desarrollador de base de datos corporativa y arquitecto. Ha sido un MVP de Microsoft SQL Server durante seis años y ha escrito cuatro libros sobre el diseño de base de datos. Actualmente es el arquitecto de datos y a veces DBA para la Christian Broadcasting Network, apoyar a las oficinas en Virginia Beach, Virginia y Nashville, Tenn.
.png)
Timothy Ford es un MVP de SQL Server y ha estado trabajando con SQL Server para más de 10 años. Es el principal DBA y experto en materia para la plataforma de SQL Server para la salud de espectro. Él ha escrito acerca de la tecnología desde 2007 para una variedad de sitios Web y mantiene su propio blog en thesqlagentman.com, cubriendo SQL como teletrabajo y profesional así como temas de desarrollo.
Conozca más acerca de "SQL Server DMV Starter Pack" en red-gate.com/our-company/about/book-store.