0

Como definir uma abordagem inicial para identificar problemas

O artigo descreve conceitos fundamentais sobre como traçar uma metodologia de resolução de problemas. Através de exemplos bem simples, são salientadas melhores práticas em relação a uma abordagem básica e alguns scripts que servem adequadamente para identificar problemas de desempenho.

O que eu tento oferecer neste artigo não é um conjunto de regras, prescrevendo a exatos passos ações a serem tomadas para resolver todos os problemas do SQL Server, mas sim uma abordagem básica e alguns scripts que servem adequadamente para solucionar problemas de desempenho.

Saber por onde começar é a parte mais difícil para resolver um problema. Uma das chaves para a solução de problemas não é só a análise de todas as variáveis de informação pertinentes, mas também descobrir o que eles estão dizendo juntos.

Como em qualquer análise,vamos dar uma olhada nas estatísticas de espera, a DMV (Dynamic Management View) sys.dm_os_wait_stats, para identificar qualquer recurso em espera, a nível do sistema operacional. Vamos dizer que identificamos altos valores de PAGEIOLATCH_SH em espera, o que indica que as sessões estão enfrentando atrasos na obtenção de um lock para o buffer de página. Isso acontece quando muitas sessões, ou talvez uma sessão em particular, está requisitando uma grande quantidade de páginas de dados que não estão disponíveis na área de buffer. O SQL Server deve alocar uma página de buffer para cada uma, e colocar um lock na página enquanto ele está recuperado do disco.

Uns dos primeiros itens que devemos verificar, ao solucionar problemas de desempenho em um SQL Server, são as estatísticas de espera, que são controlados pelos SQLOS durante as operações normais de qualquer servidor SQL.

Como exemplo disso, a qualquer momento que o SQL Server tem de esperar durante a execução de uma operação, o tempo de espera é monitorado pelos SQLOS. Esta informação é exposta, para cada instância do SQL Server instalado em um servidor, na DMV sys.dm_os_wait_statistics.

A causa e consequência dos diversos tipos de espera que o SQL Server está experimentando pode fornecer informações significativas sobre a causa dos problemas de desempenho, desde que você entenda exatamente o que as estatísticas de espera estão dizendo, e saber como correlacionar as informações de espera com as informações de resolução de problemas adicionais, tais como contadores de performance e outras DMVs.

Listagem 1. Script para identificar esperas do sistema

SELECT DISTINCT wt.wait_type

FROM sys.dm_os_waiting_tasks AS wt

JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id

WHERE s.is_user_process = 0
Como parte das operações normais de SQL Server, uma série de condições de espera existe que não são problemáticos por natureza, geralmente, esperas a nivel de sistema operacional. Estas condições geralmente podem ser consultados a partir do sys.dm_os_waiting_tasks DMV pela as sessões do sistema, como mostrado na Figura 1.

os_wait

Figura 1. Descobrindo sessões de espera do sistema.

Listagem 2. Script para verificar os maiores tempos de espera

 SELECT TOP 10 wait_type , max_wait_time_ms wait_time_ms , signal_wait_time_ms ,wait_time_ms – signal_wait_time_ms AS resource_wait_time_ms , 100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits , 100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits ,

100.0 * ( wait_time_ms – signal_wait_time_ms )/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits

FROM sys.dm_os_wait_stats

WHERE wait_time_ms > 0 — remove zero wait_time

AND wait_type NOT IN — filter out additional irrelevant waits

( ‘SLEEP_TASK’, ‘BROKER_TASK_STOP’, ‘BROKER_TO_FLUSH’,

‘SQLTRACE_BUFFER_FLUSH’,’CLR_AUTO_EVENT’, ‘CLR_MANUAL_EVENT’,

‘LAZYWRITER_SLEEP’, ‘SLEEP_SYSTEMTASK’, ‘SLEEP_BPOOL_FLUSH’,

‘BROKER_EVENTHANDLER’, ‘XE_DISPATCHER_WAIT’, ‘FT_IFTSHC_MUTEX’,

‘CHECKPOINT_QUEUE’, ‘FT_IFTS_SCHEDULER_IDLE_WAIT’,

‘BROKER_TRANSMITTER’, ‘FT_IFTSHC_MUTEX’, ‘KSOURCE_WAKEUP’,

‘LAZYWRITER_SLEEP’, ‘LOGMGR_QUEUE’, ‘ONDEMAND_TASK_QUEUE’,

‘REQUEST_FOR_DEADLOCK_SEARCH’, ‘XE_TIMER_EVENT’, ‘BAD_PAGE_PROCESS’,

‘DBMIRROR_EVENTS_QUEUE’, ‘BROKER_RECEIVE_WAITFOR’,

‘PREEMPTIVE_OS_GETPROCADDRESS’, ‘PREEMPTIVE_OS_AUTHENTICATIONOPS’,

‘WAITFOR’, ‘DISPATCHER_QUEUE_SEMAPHORE’, ‘XE_DISPATCHER_JOIN’,

‘RESOURCE_QUEUE’ )

ORDER BY wait_time_ms DESC

Em geral, ao examinar as estatísticas de espera, eu me concentro nos valores mais altos, de acordo com o campo wait_time_ms e aos eventos associados a esses tempos.

top_wait

Figura 2: Os 10 maiores eventos acumulativos de espera.

 Os eventos mais comuns são:

• CXPACKET

• SOS_SCHEDULER_YIELD

• THREADPOOL

• LCK_*

• PAGEIOLATCH_*, IO_COMPLETION, WRITELOG

• PAGELATCH_*

• LATCH_*
Estatísticas de arquivos virtuais.

Uma armadilha comum, na minha experiência, quando se utiliza estatísticas de espera como fonte primária para solução de problemas, é que a maioria dos servidores SQL Server irão apresentar alguns sinais que podem parecer gargalo de disco.

Infelizmente, as estatísticas de espera não vão dizer o que está causando o gargalo. É por isso que uma análise das estatísticas de arquivos virtuais, ao lado das estatísticas de espera, é quase sempre recomendada.

 

Listagem 3. Script para verificar as estatíticas de arquivos virtuais

 

SELECT DB_NAME(vfs.database_id) AS database_name ,

vfs.database_id , vfs.FILE_ID , io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,

io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_latency , io_stall / NULLIF(num_of_reads + num_of_writes, 0)

AS avg_total_latency , num_of_bytes_read / NULLIF(num_of_reads, 0) AS avg_bytes_per_read ,num_of_bytes_written / NULLIF(num_of_writes, 0) AS avg_bytes_per_write , vfs.io_stall , vfs.num_of_reads , vfs.num_of_bytes_read , vfs.io_stall_read_ms , vfs.num_of_writes , vfs.num_of_bytes_written , vfs.io_stall_write_ms , size_on_disk_bytes / 1024 / 1024 AS size_on_disk_mbytes , physical_name

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id

AND vfs.FILE_ID = mf.FILE_ID

ORDER BY avg_total_latency DESC

 

Contadores de performance

Muitos artigos, white papers e posts da Internet  fornece listas detalhadas dos contadores de desempenho importantes que devem ser monitorados para SQL Server, juntamente com as orientações gerais para valores aceitáveis ​​para estes contadores. No entanto, se você tentar coletar e analisar os valores para todos os contadores disponíveis, você vai verificar que isso é uma tarefa árdua.

Os contadores estão disponíveis a partir do SQL Server através da DMV  sys.dm_os_performance_counters e pode ser consultado por meio de uma query  T-SQL.

 

Listagem 4. Script para coleta de contadores de performance de SQL

 

DECLARE @CounterPrefix NVARCHAR(30)

SET @CounterPrefix = CASE WHEN @@SERVICENAME = ‘MSSQLSERVER’

THEN ‘SQLServer:’

ELSE ‘MSSQL$’ + @@SERVICENAME + ‘:’

END ;

SELECT CAST(1 AS INT) AS collection_instance ,

[OBJECT_NAME] ,

counter_name ,

instance_name ,

cntr_value ,

cntr_type ,

CURRENT_TIMESTAMP AS collection_time

INTO #perf_counters_init

FROM sys.dm_os_performance_counters

WHERE ( OBJECT_NAME = @CounterPrefix + ‘Access Methods’

AND counter_name = ‘Full Scans/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Access Methods’

AND counter_name = ‘Index Searches/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Buffer Manager’

AND counter_name = ‘Lazy Writes/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Buffer Manager’

AND counter_name = ‘Page life expectancy’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘General Statistics’

AND counter_name = ‘Processes Blocked’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘General Statistics’

AND counter_name = ‘User Connections’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Locks’

AND counter_name = ‘Lock Waits/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Locks’

AND counter_name = ‘Lock Wait Time (ms)’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘SQL Statistics’

AND counter_name = ‘SQL Re-Compilations/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Memory Manager’

AND counter_name = ‘Memory Grants Pending’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘SQL Statistics’

AND counter_name = ‘Batch Requests/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘SQL Statistics’

AND counter_name = ‘SQL Compilations/sec’

)

WAITFOR DELAY ’00:00:01′

SELECT CAST(2 AS INT) AS collection_instance ,

OBJECT_NAME ,

counter_name ,

instance_name ,

cntr_value ,

cntr_type ,

CURRENT_TIMESTAMP AS collection_time

INTO #perf_counters_second

FROM sys.dm_os_performance_counters

WHERE ( OBJECT_NAME = @CounterPrefix + ‘Access Methods’

AND counter_name = ‘Full Scans/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Access Methods’

AND counter_name = ‘Index Searches/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Buffer Manager’

AND counter_name = ‘Lazy Writes/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Buffer Manager’

AND counter_name = ‘Page life expectancy’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘General Statistics’

AND counter_name = ‘Processes Blocked’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘General Statistics’

AND counter_name = ‘User Connections’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Locks’

AND counter_name = ‘Lock Waits/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Locks’

AND counter_name = ‘Lock Wait Time (ms)’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘SQL Statistics’

AND counter_name = ‘SQL Re-Compilations/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘Memory Manager’

AND counter_name = ‘Memory Grants Pending’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘SQL Statistics’

AND counter_name = ‘Batch Requests/sec’

)

OR ( OBJECT_NAME = @CounterPrefix + ‘SQL Statistics’

AND counter_name = ‘SQL Compilations/sec’

)

SELECT i.OBJECT_NAME ,

i.counter_name ,

i.instance_name ,

CASE WHEN i.cntr_type = 272696576

THEN s.cntr_value – i.cntr_value

WHEN i.cntr_type = 65792 THEN s.cntr_value

END AS cntr_value

FROM #perf_counters_init AS i

JOIN #perf_counters_second AS s

ON i.collection_instance + 1 = s.collection_instance

AND i.OBJECT_NAME = s.OBJECT_NAME

AND i.counter_name = s.counter_name

AND i.instance_name = s.instance_name

ORDER BY OBJECT_NAME

— Cleanup tables

DROP TABLE #perf_counters_init

DROP TABLE #perf_counters_second

 
SQL Server execution statistics / Plan Cache Usage

 

SELECT TOP 10 execution_count , statement_start_offset AS stmt_start_offset , sql_handle ,

plan_handle , total_logical_reads / execution_count AS avg_logical_reads , total_logical_writes / execution_count AS avg_logical_writes , total_physical_reads / execution_count AS avg_physical_reads , t.text

FROM sys.dm_exec_query_stats AS s

CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t

ORDER BY avg_physical_reads DESC

 

Conclusão

Com base na informação coletadas utilizando esta metodologia, o diagnóstico mais avançado do problema identificado pode ser realizado, utilizando-se as informações contidas nesse artigo.

O ponto mais importante que eu quero salientar é que nenhuma parte isolada de informação do SQL Server deve ser usada para identificar qualquer problema específico. A arte de resolver um problema de desempenho em SQL Server é a arte de montar as várias peças do quebra-cabeça para que você tenha um entendimento completo do que está acontecendo no servidor. Se você se concentrar apenas no que está imediatamente a sua frente, você vai, na maioria dos casos, esquecer o item mais importante, que é a verdadeira causa raiz de um problema particular em SQL Server.