Solucionando Problemas de
Desempenho no Microsoft SQL
Server 2005
Alexandre Lopes
SQL Server Specialist
[email protected]
Agenda
• Apresentação de ferramentas e técnicas
para detecção, encontrar causas e resolução
para problemas de desempenho no SQL
Server 2005, focando em:
– CPU
– Memória
– I/O
– TempDB
• Lentidão na execução de Queries
Detecção de Problemas
Estratégia Geral
• Bottleneck (“gargalo”) – maior fator que afeta
a performance.
• Por onde começar ? Defina o problema
– Qual o seu “baseline”?
– Aconteceu alguma alteração no sistema?
• Esteja atento aos limites do seu sistema
– “Rodar” próximo a capacidade máxima X Uso ineficiente
dos recursos.
Detecção de Problemas
Estratégia Geral
•
Questionamentos mais frequentes:
1. Existe algum outro recurso do sistema que será
afetado?
2. Quais são os possíveis passos para resolver o
problema?
3. Foi realizada alguma alteração que possa ter
criado o problema ?
Detecção de Problemas: CPU
Sinais de problema:
• “De forma inesperada, o uso da CPU ficou
excessivamente alto. E agora?”
• Ferramentas e métricas:
– System Monitor: Processor object,
% Processor Time counter > 80%
– Task Manager  Performance  CPU Usage
– SQL Server: sys.dm_os_schedulers DMV,
runnable_tasks_count – identificar se o valor está alto
– SQL Server: sys.dm_exec_query_stats DMV, estatisticas
do cache corrente: total_worker_time, execution_count
Detecção de Problemas: CPU
Causas Potenciais:
• Compilação e/ou recompilação excessiva:
– Objetivo: identificar e reduzir
• Plano de Query ineficiente:
– Objetivo: Coletar informações para escrever queries com
planos mais eficientes
• Paralelismo “Intra-query”
– Objetivo: Identificar query executando com paralelismo e
torna-la mais eficiente
Detecção de Problemas: CPU
Compilação/Recompilação excessiva
• [Re]compilação faz uso intensivo de CPU. Plano de
Query não pode ser reutilizado.
– Evite utilizar a declaração WITH RECOMPILE.
• PerfMon: SQL Server: Objeto SQL Statistics
– Batch Requests/sec; SQL Compilations/sec; SQL ReCompilations/sec; baixa taxa de recompilação nas
requisições
• SQL Trace: SP:Recompile, SQL:StmtRecompile
• DMV:
– Sys.dm_exec_query_stats
• plan_generation_num e execution_count
Detecção de Problemas: CPU
Compilação/Recompilação excessiva
• Soluções:
–
–
–
–
–
–
Considere utilizar tabelas temporárias e/ou variavel Table
Atualização de Estatísticas Automaticas em OFF ou ON
Use nome de objetos qualificados (dbo.TableA X TableA)
Não misture comandos DDL/DML
Use DTA (Database Engine Tuning Advisor)
Considere a real necessidade de utilizar WITH RECOMPILE
em stored procedure
Detecção de Problemas: CPU
Plano de Query ineficiente
• DMVs:
– sys.dm_exec_query_stats, sys.dm_exec_sql_text
– procurar por queries que fazem uso intensivo de CPU
– sys.dm_exec_cached_plans
– procurar por operadores que fazem uso da CPU
Detecção de Problemas: CPU
Plano de Query ineficiente
• Soluções:
–
–
–
–
Use DTA para checar as recomendações de indices
Use de forma restritiva a clausula WHERE
Mantenha as estatisticas atualizadas
Procure por queries que não foram construidas seguindo
as boas práticas de desenvolvimento
– Considere usar “Query hints”:
• OPTIMIZE FOR – valor de parametro particular para otimização
• FORCE ORDER – preserva o ordem dos joins
• USE PLAN – força o pano de Query
Detecção de Problemas: CPU
Paralelismo Intra-query
• Queries usando paralelismo tem um custo alto para
a CPU
• DMVs:
– sys.dm_exec_requests, sys.dm_os_tasks,
sys.dm_exec_sessions, sys.dm_exec_sql_text,
sys.dm_exec_cached_plan
– sys.dm_exec_query_stats for total_worker_time >
total_elapsed_time
Detecção de Problemas: CPU
Paralelismo Intra-query
• Soluções similares para o plano de Query eficiente:
–
–
–
–
Use DTA
Mantenha as estatísticas atualizadas
Procure por estatísticas desatualizadas
Avalie se a query pode ser reescrita de forma mais
eficiente utilizando Transact-SQL
demonstração
Detecção de problemas: CPU
Detecção de Problemas: Memória
Sinais de Problema:
• Erros explícitos relacionados a memória (ex: “out of
memory”, “timeout” enquanto aguarda por recursos
de memória livres)
• Valor baixo de “buffer cache hit ratio”
• Utilização incomum e excessiva de I/O
• De uma forma geral, o sistema apresenta um
comportamento lento
• Objetivo: analisar consumo de memória
Detecção de Problemas: Memória
Erros relacionados:
• 701 - There is insufficient system memory to run this query
• 802 - There is insufficient memory available in the buffer pool
• 8628 - A time out occurred while waiting to optimize the
query. Rerun the query
• 8645 - A time out occurred while waiting for memory
resources to execute the query. Rerun the query.
• 8651 - Could not perform the requested operation because
the minimum query memory is not available. Decrease the
configured value for the 'min memory per query' server
configuration option
Detecção de Problemas: Memória
Detecção e Análise – Parte I
• Task Manager:
– Mem usage, virtual machine size
– Physical Memory, Commit charge (uso do PageFile)
• PerfMon:
– Process object: Working set, Private bytes
– Memory object: Available KBytes, System Cache
Resident Bytes, Committed bytes, Commit Limit
– SQL Server: Buffer Manager object
• Buffer cache hit ratio, Page life expectancy, Checkpoint
pages/sec, Lazy writes/sec
Detecção de Problemas: Memória
Detecção e Análise – Parte II
• DMVs:
–
–
–
–
–
sys.dm_os_memory_clerks
sys.dm_os_memory_cache_clock_hands
sys.dm_os_memory_cache_counters
sys.dm_os_ring_buffers
sys.dm_os_virtual_address_dump
• DBCC MEMORYSTATUS
– Buffer distribution; buffer counts; global memory objects;
query memory objects; gateways
Detecção de Problemas: Memória
Passos para a resolução:
• Verifique parâmetros de configuração de memória no
servidor (configurações inconsistentes)
– Min memory per query; min/max server memory; awe enabled
– Lock pages em memória privilegiada
• Realize sucessivas coletas de informações utilizando
DMVs/DBCC MEMORYSTATUS e dos contadores de
performance do PerfMon (compare com o seu “baseline”)
• Confira a carga de trabalho (número de queries/sessions)
• Entenda a razão do aumento de consumo de memória e
tente sempre eliminá-la (muitas vezes pode não ser
possível)
demonstração
Detecção de Problemas: Memória
Detecção de Problemas: I/O
Informações Gerais:
• 3 maiores consumidores de atividades de I/O:
– Movimentação de páginas do banco de dados da
memória para o disco e vice-versa
– Operações dos arquivos de Logs
– Operações no banco de dados TempDB
• Sinais de problema: tempo de resposta baixo,
mensagens com erros de “timeout”, o sistema de
I/O operando na sua capacidade máxima
• Objetivo: identificar “gargalos” no I/O
Detecção de Problemas: I/O
Fase de Detecção:
• PerfMon: Physical Disk object
–
–
–
–
–
–
–
–
–
% Disk Time > 50%
Avg. Disk Queue Length > 2
Avg. Disc sec/Read OU Avg. Disc sec/Write > 10-20 ms
Avg. Disk Reads/sec OU Avg. Disk Writes/sec > 85% da capacidade do
disco
Ajustes para RAID:
Raid 0: I/Os per disk = (reads + writes) / number of disks
Raid 1: I/Os per disk = [reads + (2 * writes)] / 2
Raid 5: I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10: I/Os per disk = [reads + (2 * writes)] / number of disks
• DMVs
– sys.dm_os_wait_stats for wait_type like ‘PAGEIOLATCH%’
– sys.dm_io_pendion_io_requests with sys.dm_io_virtual_file_stats
– sys.dm_exec_query_stats: *_reads, *_writes columns
Detecção de Problemas: I/O
Análise e Resolução:
• Certifique de que está usando ótimos planos de
queries
– Possibilidade de reescrever no caso de plano de queries
ineficientes
• Alto I/O pode indicar “gargalo” na memória
• Confira a quantidade de memória e considere
adicionar mais memória
• Aumente a largura de banda do I/O
– Discos rápidos, controladoras com mais cache
• Esteja atento a capacidade do seu sistema !
demonstração
Detecção de Problemas: I/O
Detecção de Problemas: TempDB
Informações Gerais:
• Uso do TempDB:
– Armazenamento de tabelas temporárias
– SQL Server utiliza para criar objetos internos
– Tem o seu conteúdo eliminado quando o serviço do SQL
Server é parado (recriado novamente após inicializar o
serviço)
• Problemas:
– Procedimentos sendo realizados fora do TempDB
– “Gargalos” nas “system tables” devido as excessivas
operações de DDL
• Objetivo: monitorar uso excessivo de DDL, procurar
e, se possível, eliminar “procedimentos intrusos” no
TempDB
Detecção de Problemas: TempDB
Monitoração de espaço:
• DMVs:
– sys.dm_db_file_space_usage (usuários, objetos internos
e espaço utilizado)
– sys.dm_tran_active_snapshot_database_transactions
(transações que rodam lentamente  maior espaço)
– sys.dm_db_session_space_usage
– sys.dm_db_task_space_usage
• PerfMon:
– SQL Server: Transactions object
• Version Generation/Cleanup rates
Detecção de Problemas: TempDB
Resolução:
•
Faça um plano de capacidade para o TempDB
–
–
•
Contabilize os procedimentos que usam o TempDB
Reserve espaço suficiente para o TempDB
Objetos “User”: identifique e elimine usuários
desnecessários no TempDB
Cuidados com o tamanho do TempDB:
•
–
•
Elimine longas transações sempre que possível
Excessivos DDL:
–
–
Considere quando criar tabelas temporárias (locais e/ou globais)
Considere os planos de query plans que criam diversos objetos
internos e verifique se estão escritos de forma eficiente ou se será
preciso reescreve-los
demonstração
Detecção de Problemas: TempDB
Lentidão na Execução de Queries
Informações Gerais:
• Origens do problema:
– Pode estar aguardando por locks (travas) lógicas
(procedimento normal)
– Recursos de hardware dimensionados de forma incorreta
– Problemas relacionados a software:
•
•
•
•
Aplicação foi projetada sem critérios
Planos de Queries ruins
Indices ausentes
Configuração imprópria do servidor
• Objetivo: identificar blocos longos; objetos que estão em
estado de espera; analizar filas de espera
Lentidão na Execução de Queries
Detecção:
• DMVs:
–
–
–
–
–
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
sys.dm_tran_locks
sys.db_index_operational_stats
sys.dm_index_usage_stats (efficient index usage;
identifying “dead” indexes)
• SQL Trace/Profiler: (para “blocks” longos)
– Errors and Warnings: Blocked process report (with
sp_configure ‘blocked process threshold’)
Lentidão na Execução de Queries
Resolução do problema:
• Após realizada a análise dos dados coletados,
questione-se:
– O desenho da aplicação está eficiente e as queries são
executadas de forma correta e sem concorrências
graves?
– Existem indices não utilizados ou tabelas sem índices
que utilizam “table scans” desnecessariamente?
– O servidor está configurado apropriadamente?
– Existe algum recurso que possui “gargalo”?
Demonstração
Cenário: Lentidão na execução de
Queries.
Conclusão
• Defina o problema de forma clara e
objetiva, será um diferencial para o
sucesso.
• Tenha uma visão do problema e conheça
a forma de trabalho para resolve-lo.
• Use as linhas gerais apresentadas para
inicializar uma análise detalhada do
problema.
Links Úteis
•
Microsoft Technet Brasil
http://www.microsoft.com/brasil/technet
•
Microsoft TechCenter SQL Server
http://www.microsoft.com/brasil/technet/prodtechnol/sql/default.mspx
•
DMVs (Dynamic Management Views)
http://go.microsoft.com/fwlink/?LinkId=44375
•
Recompilação e Plano de Caching
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
•
Wite Paper ‘Troubleshooting Performance Problems’
http://www.microsoft.com/technet/prodtechnol/sql/2005/default.mspx
Seu potencial. Nossa inspiração.
© 2006 Microsoft Corporation. Todos os direitos reservados.
O propósito desta apresentação é apenas informativa. Microsoft não faz nenhuma garantia expressa ou implícita nesta apresentação.
MR