| 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 April 14 Calculando espaço livre por FilegroupPessoal, boa noite!
Resolvi mais uma vez compartilhar um pouco mais sobre o meu dia a dia com SQL Server, como já disse trabalho com um VLDB, e para gerenciá-lo, muitas vezes precisamos nos utilizarmos de técnicas como a "dividir para conquistar", sim existe um algoritmo com esse nome e sim estou falando de particionamento e filegroups.
Hoje infelizmente ainda sofremos com as dificuldades do dia a dia de um DBA, pois estamos com deficiência em ferramentas de monitoração reativa e principalmente pró-ativa no meu cliente isso não é diferente, estão implementando o MOM apenas agora, imaginem apenas um dos nossos databases possui exatos 11TB e falo de OLTP, trabalhamos hoje com um volume total de dados em torno de 40TB apenas em OLTP e temos desafios e dificuldades de bancos de alguns GBs.
Bom todos sabemos que os filegroups em conjunto com particionamento nos ajudam em diversas atividades, exemplo: backup, switch de dados históricos, distribuição de I/O e etc.
Pois bem, hoje o cliente veio me solicitar um SCRIPT para monitorar o Free Space dos Filegroups com seus respectivos arquivos individuais, pois bem a primeira coisa que pensei foi, bom no SSMS existe isso, vou capturar a query e trabalhar em cima disso e foi o realmente eu fiz.
A query abaixo foi adaptada e hoje nós passamos a utilizá-la em um processo batch diário de coleta do espaço por filegroup e também podemos disponibilizar um relatório em SSRS para monitoração ad-hoc.
if exists (select * from sys.objects where name = 'usp_FGCalc') drop proc usp_FGCalc; go--Usar o novo database e calcular o espao dos FGs CREATE PROC usp_FGCalc (@dbname as nvarchar(128))AS /* Funcionalidade: Calcular o espao livre em cada arquivo de um Filegroup, funcionalidade baseada nas queries do SSMS. Adapatado por: Rodrigo Fernandes de Souza Empresa: Microsoft Brasil Compatibilidade: SQL Server 2005/2008 Data: 13/04/2009 */ SET NOCOUNT ON;DECLARE @cmd nvarchar(4000), @dbid int;SET @dbid = DB_ID(@dbname)if @dbid is nullbegin RAISERROR('Database does not exists!', 16, 1); RETURN;end CREATE TABLE #FGSpace (FileId int, FileGroup int,TotalExtents int, UsedExtents int,Name sysname, FileName nchar(520)) SET @cmd = 'USE ' + @dbname + CHAR(10) + CHAR(13) + 'Insert #FGSpace EXEC (''DBCC SHOWFILESTATS() WITH NO_INFOMSGS'')';EXEC sp_executesql @cmd; Select mf .name As [LogicalName],fg .name As [FGName],mf .physical_name As [FileName],mf .size * CONVERT(float, 8) As [Current_FileSize], CAST (CASE mf.[type] WHEN 2 THEN 0 --Retira os FGs de FileStream ELSE fgs.UsedExtents * CONVERT(float, 64) --Calcula o tamanho ocupado no arquivo END As float) As [UsedSpace], CAST (CASE mf.[type] WHEN 2 THEN 0 --Retira os FGs de FileStream ELSE 100 -(((fgs.UsedExtents * CONVERT(float, 64)) * 100) / (mf.size * CONVERT(float, 8))) --Calcula o FreeSpace END As int) As [FreeSpace],mf .[file_id] As [Id] From sys.filegroups As fg INNER JOIN sys.master_files As mf ON (mf.type = 2 Or mf.type = 0 And mf.database_id = @dbid And mf.drop_lsn IS NULL And mf.data_space_id = fg.data_space_id) LEFT OUTER JOIN#FgSpace As fgs ON fgs.Fileid = mf.[file_id] ORDER BY[Id] AscQuem está familiarizado com SQL Server desde a versão 2000 no mínimo, deve lembra da função DBCC SHOWFILESTATS, esse comando não documentando basicamente nos retorna o espaço alocado e utilizado em extents no arquivo. Lembrando um Extent, corresponde a 8 pages ou seja 8192 * 8 = 64K. Com isso é possível já utilizarmos as dmvs novas sys.filegroups e sys.master_files para calcularmos o espaço utilizado e a taxa de espaço livre. Pessoal, sei que esse script já pode ser conhecido por alguns, porém vale a pena utilizá-lo, onde você tem casos com muitos filegroups em conjunto com particionamento onde não exista uma ferramenta adequada para monitoração e notificação. Espero que seja útil a todos! Grande Abraço, 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 10 SQL Server 2005 - VLDB Best PracticesBoa noite, estamos aqui novamente, porém desta vez não vou escrever nada específico.
Neste "post" vou sugerir uma leitura muito importante para quem precisa administrar VLDB's, essa lista de boas práticas foi elaborada pelo CAT (Customer Advisory Team), esse grupo de profissionais atua apenas em projetos desafiadores de grande potencial.
Apesar dessa lista estar direcionada para Relational Data Warehouse, alguns dos conceitos podem e devem ser utilizados em diversas situações.
Espero que todos aproveitem o conteúdo.
Grande Abraço e até a próxima!
Att,
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
|
|
|
