| Rodrigo's profileRodrigo's spacePhotosBlogLists |  Tools  Help |
|
|
April 21 Desmistificando o wait type CMEMTHREADNa última semana, tivemos alguns problemas quanto a um estranho comportamento em um dos nossos servidores SQL Server 2005, ele estava apresentando uma grande lentidão.
Sintoma
No momento que tentamos realizar um logon na console do SSMS, recebemos a mensagem de "Request Timed out".
Explicação
Geralmente este caso esta atrelado a um evento de situação de Lock/Deadlock em objetos do servidor ou escassez de memória no "connection pool", esta memória é dinâmica e pode ser alocada ou desalocada para uso em outras áreas como Procedure Cache, Buffer Pool e etc.
Troubleshooting
A primeira providência foi realizarmos uma conexão utilizando o DAC pelo SSMS, a partir desse busquei algumas informações nas dmv's para entender o que estava ocorrendo.
1 - Executei uma query na sys.dm_os_wait_stats - Verifiquei quais seriam os waits types mais comuns e com maior incidência de [wait_time_ms], lembrando que estes valores são cumulativos desde o start da instância SQL Server, porém caso um wait type comece a ocorrer com grande frequência, ele facilmente pode ser identificado na lista dos Top 10, ordenado DESC pelo [wait_time_ms], aqui já identifiquei o evento CMEMTHREAD aparecendo.
2 - Executei uma query na sys.dm_os_latch_stats - Verifiquei quais seriam os tipos de latch's mais comuns que estavam ocorrendo ordenando também pelo [wait_time_ms], existia alguns LATCHS internos na lista, porém um neste caso já chamou a atenção de primeira, BUFFER, este latch é exigido antes de ler ou modificar qualquer página no banco de dados, neste caso se o tempo de wait estiver alto demais, o que temos? Uma possível falta de memória ou alguém utilizando demais, o que talvez não deveria.
3 - Fiz uma query cruzando a sys.dm_exec_requests e sys.dm_exec_sessions, para identificar os processos em SUSPENDED e RUNNABLE, e com isso verificar qual o WAIT_TYPE destas sessões, com isso verifiquei que ocorria diversos CMEMTHREAD, com wait_time alto.
Select *From sys.dm_exec_requests As rinner join sys.dm_exec_sessions As s
on 4 - Segundo este KB: http://support.microsoft.com/kb/822101/ este wait_type indica que o SPID está aguardando para acessar a thread-safe memory, isto pode ocorrer quando vários spid's estão tentando inserir ou desalocar um objeto na memória, no geral este wait está associado a RECOMPILAÇÃO.
5 - Neste passo devemos identificar qual a query por SPID está causando este problema com maior incidência, para isso executamos novamente a mesma query do passo 3, porém fazemos um CROSS APPLY com sys.dm_exec_sql_text:
cross apply sys.dm_exec_sql_text(r.[sql_handle])6 - Após identificarmos qual a query, precisamos confirmar se realmente está ocorrendo a recompilação, como fazemos isso? Abrimos um SQL Profiler e coletamos o Evento de SP:Recompile. 7 - Podemos identificar se o plano está sendo re-utilizado com uma query na sys.dm_exec_cached_plans a partir do handle da query que resgatamos no passo 3 ou 5, com isso é possível verificarmos através da coluna [usecounts] se o plano está sendo re-utilizado com frequência e também o seu tamanho em [size_in_bytes], no meu caso havia um plano que estava grande com 270MB, desconfiei deste e parti para a solução. 8 - Com todos estes dados coletados, precisamos solucionar o problema. Muito bem iniciei investigando o [plano de execução X o plano em cache] eram os mesmos, também olhei se havia alguma coisa na query que estivesse causando a recompilação, não havia nada. Sim, a query não estava boa, porém não havia o que fazer naquele momento, apenas resolver este problema sem alterar nada. Como resgatar o plano de execução em cache? faça um CROSS APPLY utilizando a sys.dm_exec_query_plan(plan_handle) que você terá acesso a partir da coluna [plan_handle] da sys.dm_exec_cached_plans. A saída será um XML, salve-o como .sqlplan e abra no SSMS. 9 - Foi onde tracei dois planos 1 - Forçar uma recompilação total na procedure com SP_RECOMPILE 'proc name', assim poderia retirá-la do cache e inserí-la novamente e verificar se este comportamento volta a acontecer. Ou então 2 - gerar um plano XML e forçar este ser mantido no cache. Pois bem não foi preciso executar o segundo plano, com o primeiro funcionou perfeitamente, o SQL retirou o plano do cache e colocou novamente na primeira execução da procedure, e a partir daí o evento não voltou a acontecer. Causas prováveis: - Estatísticas desatualizadas; (porém não foi preciso atualizá-las) - Procedure migrada junto com o banco de dados em modo de compatibilidade 2000 (80), no momento da compilação causou alguma excessão. - Plano foi marcado para ser removido do procedure cache em momento inadequado, pelo seu tamanho ou pelo fato do [usecounts] não estar incrementando. Realmente a causa ficou complicada de identificarmos, ficamos de abrir um caso direto no time de desenvolvimento, porém o ideal seria coletarmos mais informações, coisa que não conseguimos, pelo fato de estar comprometendo o ambiente.
Bom espero que este artigo seja útil para todos. Abraços e até a próxima!
Rodrigo Fernandes de Souza January 08 SQL Server 2005 – Identificando Memory Pressure IIPessoal como já falamos em outra postagem, existem centenas de tipos de wait’s para descrever a situação pelo qual o banco está passando. Para identificarmos quais são os wait types, dê uma boa olhada na sys.dm_os_wait_stats. Pois bem, tivemos um evento muito interessante certa manhã ao chegarmos no trabalho e identificarmos que os nossos servidores onde estão nossos VLDB’s estavam apresentando o seguinte sintoma: - CPU: Normal - Memória: Normal (inclusive Cache Hit Ratio) - Disco: Normal - Paging File: Normal Porém estávamos com as consultas muito lentas, então resolvemos identificar algumas dessas e obviamente olharmos o plano de execução e tratar o gargalo, pois bem identificamos as consultas mas não conseguimos nem mesmo traçar o plano de execução dessas. Como todo o processo de troubleshooting traçamos uma linha de atuação e fomos eliminando as possibilidades, chegamos a conclusão que havia alguma coisa errada com o comportamento do SQL Server. Abrimos o Profiler e escolhemos alguns eventos, entre eles o de SP:Cache Miss, ao deixarmos o trace rodando durante algum tempo, observamos que exatamente as queries que estavam lentas, reportavam diversos eventos desse tipo. Esse evento nada mais é do que não ter encontrado o plano de execução no Procedure Cache. Muito bem vale lembrar que o SQL Server possui diversos Memory Components entre eles os mais conhecidos Procedure Cache e Data Cache (Buffer Pool), esses componentes não fazem parte da memória que alocamos com Min e Max Server Memory parameter, com este parâmetro delimitamos apenas o tamanho do Buffer Pool. Sendo assim fica a cargo do SQL Server gerenciar as outras áreas como o tamanho do Procedure Cache. sempre que precisar o SQL Server solicita memória para o Data Cache, mas muitas vezes a memória realmente está ocupada e não existem dirty pages para liberar. O plano de atuação para resolver esse problema basicamente foi identificar se existia alguma cláusula ou parâmetro nas stored procedures que pudessem causar recompilação, concluímos que não. A partir deste adotamos a estratégia de disponibilizarmos mais memória "fora" do Buffer Pool afim de que o SQL Server fizesse uso dessa área "liberada" para Procedure Cache, com isso diminuimos a quantidade de memória do parâmetro “Max Server Memory” e disponibilizamos memória para o Sistema Operacional e consequentemente para outros memory components do SQL Server, inclusive o Procedure Cache, a partir dessa alteração não houve mais incidentes reportando SP:Cache Miss. Com isso podemos concluir mais uma vez que identificar problemas do tipo Memory Pressure pode tornar-se complicado e demandar muito tempo, vale utilizar todas as ferramentas disponíveis. Até a próxima… Abraços, Rodrigo Fernandes December 02 SQL Server 2005 - Identificando Memory PressureCom o SQL Server 2005 temos mais de 201 tipos de de Waits, onde cada um desses descreve e demonstra um tipo de situação pelo qual o banco de dados está aguardando.
Neste "post", gostaria de descrever um deles, e exemplificar utilizando situações pelas quais passei.
- RESOURCE_SEMAPHORE
Este final de semana precisamos migrar algumas tabelas para um novo modelo utilizando particionamento de dados, tendo em vista que o banco de dados trata-se de um VLDB (Very Large Database), fizemos o planejamento e utilizamos pacotes SSIS para copiar os dados em paralelo para a nova estrutura, usando essa metodologia conseguimos carregar milhões de registros em questões de minutos. Esse é tema para outro "post".
Em duas dessas tabelas identificamos a seguinte situação:
Usamos as DMV's sys.dm_exec_requests e sys.dm_exec_sessions para verificarmos a sessão e o tipo de wait que está ocorrendo, exemplo:
Select *
From sys.dm_exec_requests
Where spid = @spid
Select *
From sys.dm_exec_sessions
Where spid = @spid
Para verficarmos a distribuição da memória, podemos utilizar a sys.dm_os_memory_clerks ou DBCC MEMORYSTATUS.
Ao executarmos diversas queries para obtermos os dados, vivenciamos RESOURCE_SEMAPHORE no SQL Server, pesquisamos e analisamos o que estava ocorrendo. Verificamos que esse evento ocorre quando existe "Memory Pressure", quando o SQL Server detecta essa situação ele entra em um estado de "dormência" até que tenha memória disponível para processar outras solicitações.
Esse evento ocorreu duas vezes conosco, em uma dessas situações identificamos que o problema tratava-se da query que estava sendo executada, essa estava realizando um "Hash Join", onde este consumia memória demais, foi então que decidimos inserir um "Hint" na query para utilizar um índice válido, pois o otimizador não estava utilizando-o, provavelmente por falta de estatísticas no banco, porém não tinhamos muito tempo para gerar as estatísticas novamente, essa tabela é grande demais.
O segundo evento ocorreu em outra tabela com uma consulta já otimizada, neste outro caso resolvemos o problema quebrando os "data flow tasks" para processar menor quantidade de registros por vez, pelo fato de termos milhões de registros por dia a query torna-se pesada para subir os dados para a memória.
Conclusão
Sempre analise o plano de execução das queries, verifique se o otimizador está utilizando o índice correto, quando necessário altere a lógica da sua query para torná-la otimizada e observe o comportamento do SGBD e do sistema operacional afim de identificar onde é o gargalo, eventos de "Memory Pressure" nem sempre são fáceis de serem identificados, por isso muita vezes é necessário uma investigação mais profunda caso a caso.
Referências
Abraços,
Rodrigo Fernandes
|
|
|
