SQL Server: Os atrasos do SQL Server desmistificados
Você pode usar os dados de controle para os tempos de espera de transações do SQL Server para localizar recursos superutilizados e eliminar gargalos.
Extraído do "SQL Server DMV Starter Pack," publicado pela Red Gate Books (2010).
Glenn Berry, Louis Davidson e Tim Ford
Sempre que uma sessão do SQL Server tem de esperar por alguma razão antes de continuar o trabalho solicitado, SQL Server registra o comprimento de tempo aguardado. Ele também registra o recurso para o qual a transação está esperando.
O gerenciamento dinâmico do DM os_wait_stats exibe expõe (DMV) estas aguardar estatísticas, agregadas em todas as sessões, para fornecer uma revisão sumária de onde as principais esperas são em qualquer determinada instância. Esta mesma DMV também expõe contadores de desempenho (PerfMon), que fornecem recursos específicos medições de uso (taxas de transferência de disco, tempo de CPU consumido e assim por diante).
Ao correlacionar estatísticas de espera com medições de recurso, pode rapidamente localizar os recursos mais "impugnados" em seu sistema e destacar possíveis gargalos.
SQL Server 2005 esperas e filas
O uso de "esperas e filas" como a base para uma metodologia de ajuste de desempenho é explicada em um excelente livro branco por Tom Davidson. Essencialmente, cada solicitação para o SQL Server irá resultar no início de uma série de "trabalhador tarefas."
Um SQL Servidor Agendador atribui cada tarefa a um segmento de trabalho. Normalmente existe um agendador de SQL OS por CPU e apenas uma sessão por Agendador executado a qualquer momento. É trabalho do Agendador para distribuir a carga de trabalho uniformemente entre threads de trabalho disponível.
Se segmento de trabalho da sessão está sendo executado no processador, a sessão de estado "executará," conforme expõe a coluna Status da DM exec_requests DMV. Se um thread está pronto para ir, mas ao qual ela está atribuída atualmente possui outra sessão em execução, em seguida, ele será colocado na fila "Runnable". Isso significa que ele está na fila para obter sobre o processador. Isto é referido como uma espera de sinal.
O tempo de espera de sinal é exposto pela coluna signal_wait_time_ms e é exclusivamente de CPU tempo de espera. Se uma sessão está aguardando que outro recurso se torne disponível para prosseguir, como uma página bloqueada, ou se precisa de uma sessão em execução executar i/O, em seguida, ele é movido para a lista de espera. Esta é uma espera de recurso e status de espera da sessão será registrada como "Suspenso".
A razão para o tempo de espera é gravada e exposta na coluna wait_type do DM os_wait_stats DMV. Espera a tempo total gastado é exposta pela coluna wait_time_ms, então você pode calcular o tempo de espera do recurso da seguinte forma:
As esperas de recurso = totais esperas — esperas de sinal = (wait_time_ms)-(signal_wait_time_ms)
As esperas de sinal são inevitáveis em sistemas (OLTP), que são compostos de um grande número de transações curtas de processamento de transações on-line. A métrica chave, ao potencial pressão da CPU, é o tempo de espera de sinal como uma porcentagem da espera total. Um sinal de porcentagem alta é um sinal de pressão excessiva da CPU. A literatura tende a citar "alta" como mais do que cerca de 25%, mas depende do seu sistema.
Em nossos sistemas, podemos tratar valores maiores do que 10 a 15 por cento como um sinal preocupante. Em geral, o uso de estatísticas de espera representa um meio muito eficaz de diagnosticar os tempos de resposta em seu sistema. Em termos simples, o trabalho ou você espera. Tempo de resposta é igual ao tempo de serviço, mais o tempo de espera.
Se tempos de resposta são lentos e você encontrará sem esperas significativas, principalmente as esperas de sinal, então você precisa se concentrar na CPU. Se você encontrar o tempo de resposta é composto principalmente por tempo gastado esperando por outros recursos (como a rede, e/S e assim por diante), então você sabe exatamente para onde concentrar os esforços de ajuste.
Tirar a adivinhação
Mario Broodbakker tem escrito uma excelente introdução série de artigos sobre como usar eventos de espera para diagnosticar problemas de desempenho. Nosso primeiro script na categoria OS usa o DM os_wait_stats DMV, que é definido como: Retorna informações sobre todas as esperas encontradas por threads executados. Você pode usar essa exibição agregada para diagnosticar problemas de desempenho com o SQL Server e também com lotes e consultas específicas.
Esta consulta calcula as esperas de sinal e esperas como uma porcentagem do tempo de espera global, a fim de diagnosticar possível pressão da 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 é útil para ajudar a confirmar a pressão da CPU. Porque sinal aguarda tempo aguardando uma CPU atender a um segmento, se você gravar esperas de sinal total que são mais ou menos maiores do que 10% a 15%, então este é um bom indicador de pressão da CPU.
Estas aguardar estatísticas são cumulativos desde última reinicialização do SQL Server, então você precisa conhecer a linha de base de valor para as esperas de sinal e assistir a tendência ao longo do tempo. Você manualmente pode limpar as estatísticas de espera, sem reiniciar o servidor, através do comando DBCC SQLPERF, da seguinte forma:
-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;
Se sua instância do SQL Server foi executado por um bom tempo, e você fizer uma alteração significativa como a adição de um novo índice, então você deve considerar a compensação o velho espera estatísticas. Caso contrário, as estatísticas de espera acumulado velho irão mascarar qualquer impacto sua mudança tem sobre os tempos de espera.
Nosso segundo exemplo de script usando o DM os_wait_stats DMV ajudará a determinar os recursos em que SQL Server está a gastar mais tempo em 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 irá ajudá-lo a localizar o maior gargalo no nível da instância. Isso ajuda a concentrar os esforços de ajuste em um determinado tipo de problema. Por exemplo, se o top cumulativo esperar tipos estão relacionados a I/O de disco e, em seguida, você iria querer investigar esta questão ainda mais usando consultas DMV relacionados a disco e contadores PerfMon.
Expor contadores de desempenho
A DMV que expõe os contadores PerfMon é DM os_performance_counters, que é descrito como: "Retorna uma linha para cada contador de desempenho mantido pelo servidor." Este é um DMV útil, mas pode ser frustrante usar. Dependendo do valor de cntr_type para uma determinada linha, você terá que passar por algumas oscilações interessantes para obter informações significativas dessa DMV. É uma substituição para o antigo sys.sysperfinfo do SQL Server 2000.
Este script ajuda você a investigar as condições incomuns encher o log de sua transação. Ele retorna o modelo de recuperação, registro reutilização espera descrição, tamanho do log de transação, espaço de log usada, percentagem de log usado, nível de compatibilidade página e verifique opção para cada banco de dados na instância do SQL Server atual:
-- 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 ajuda a avaliar um servidor de banco de dados desconhecido. Também é útil do ponto de vista de monitorização. Por exemplo, se sua reutilização de log espere descrição é algo incomum e seu log de transações é 85% completo, que deve desencadear um alarme.
.png)
Glenn Berry trabalha como arquiteto de banco de dados no NewsGator tecnologias em Denver, Colorado Ele é um MVP do SQL Server e tem uma coleção inteira de certificações da Microsoft, incluindo o MCITP, MCDBA, MCSE, MCSD, MCAD e MCTS, que prova que ele gosta de fazer testes.
.png)
Louis Davidson foi no setor de TI há 16 anos como arquiteto e desenvolvedor de banco de dados corporativo. Ele tem sido um MVP do Microsoft SQL Server para seis anos e já escreveu quatro livros sobre design de banco de dados. Atualmente ele é o arquiteto de dados e, por vezes, DBA para o Christian Broadcasting Network, escritórios de apoio em Virginia Beach, Virgínia e em Nashville, Tennessee.
.png)
Timothy Ford é um MVP do SQL Server e tem vindo a trabalhar com o SQL Server para mais de 10 anos. Ele é o principal DBA e especialista no assunto para a plataforma do SQL Server para a saúde do espectro. Ele tem sido escrito sobre tecnologia desde 2007 para uma variedade de sites da Web e mantém seu próprio blog em thesqlagentman.com, abrangendo SQL como tópicos de desenvolvimento bem como teletrabalho e profissional.
Saiba mais sobre o "SQL Server DMV Starter Pack" em red-gate.com/our-company/about/book-store.