Passo a passo para encontrar as querys mais demoradas do Banco de Dados – Parte 2
Continuando o artigo Passo a passo para encontrar as querys mais demoradas do Banco de Dados – Parte 1.
Agora que o trace já foi testado e vimos que ele realmente funciona, criaremos um job para enviar o trace para a tabela de log e automatizar todo o processo.
Você deve criar um Job no Management Studio com o nome “DBA – Trace Querys Demoradas” e esse job deve possuir 3 steps.
STEP 1 – No primeiro Step devemos parar o trace momentaneamente para enviar todo o seu resultado para a tabela de log. Nesse step, você deve selecionar a database em que vc criou a tabela que armazenará o trace e incluir a query abaixo no step.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @Trace_Id INT SELECT @Trace_Id = [TraceId] FROM fn_trace_getinfo(0) WHERE CAST([value] AS VARCHAR(50)) = 'C:\Trace\Querys_Demoradas.trc' EXEC [dbo].[sp_trace_setstatus] @traceid = @Trace_Id, @status = 0 -- Interrompe o rastreamento especificado. EXEC [dbo].[sp_trace_setstatus] @traceid = @Trace_Id, @status = 2 -- Fecha o rastreamento especificado e exclui sua definição do servidor. INSERT INTO [dbo].[Traces] ( [Textdata], [NTUserName], [HostName], [ApplicationName], [LoginName], [SPID], [Duration], [Starttime], [EndTime], [Reads], [writes], [CPU], [Servername], [DatabaseName], [rowcounts], [SessionLoginName] ) SELECT [Textdata], [NTUserName], [HostName], [ApplicationName], [LoginName], [SPID], CAST([Duration] / 1000/ 1000.00 AS NUMERIC(15, 2)) [Duration], [Starttime], [EndTime], [Reads], [writes], [CPU], [Servername], [DatabaseName], [rowcounts], [SessionLoginName] FROM :: fn_trace_gettable('C:\Trace\Querys_Demoradas.trc', default) WHERE [Duration] IS NOT NULL ORDER BY [Starttime] |
Segue abaixo a figura de como fica esse step:
STEP 2 – Agora que os dados do trace já foram armazenados na tabela, deve-se excluir o arquivo de trace para que um novo seja criado. Isso pode ser realizado executando o comando “del C:\Trace\Querys_Demoradas.trc /Q” como pode ser visto na figura abaixo:
STEP 3 – Esse passo deve apenas recriar o trace. Similar ao step 1, você deve selecionar a database em que vc criou a procedure e rodar a query abaixo:
|
1 |
EXEC [dbo].[stpCreate_Trace] |
No meu ambiente, esse job roda de hora em hora no meu principal servidor de produção, mas isso depende da sua necessidade. Eu também excluo os registros com mais de 60 dias, para que a tabela de log não fique muito grande, ja que não analiso esses registros mais antigos.
Agora que nosso procedimento ja está em produção, é possivel atacar as querys mais demoradas.
No meu ambiente, identifico as procedures que mais retornam no trace e realizo um agrupamento, como feito abaixo, para facilitar minha visualização diária. Para um exemplo, vou criar mais uma procedure de teste e executá-las algumas vezes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE PROCEDURE [dbo].[dbo].[stpTeste_Trace2] AS BEGIN WAITFOR DELAY '00:00:04' END GO EXEC [dbo].[stpTeste_Trace1] -- Procedure ja criada GO EXEC [dbo].[stpTeste_Trace1] GO EXEC [dbo].[stpTeste_Trace2] GO EXEC [dbo].[stpTeste_Trace2] GO EXEC [dbo].[stpTeste_Trace2] GO EXEC [dbo].[stpTeste_Trace2] GO |
Depois de executadas, rodarei o Job para importar o arquivo de trace para a tabela de log.
|
1 |
EXEC [msdb]..[sp_start_job] @job_name = 'DBA - Trace Querys Demoradas' |
Com a query abaixo, podemos visualizar os dados armazenados no trace agrupados por procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DECLARE @Dt_Inicial DATETIME, @Dt_Final DATETIME SELECT @Dt_Inicial = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME), @Dt_Final = CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME) SELECT CASE WHEN [TextData] LIKE '%stpTeste_Trace1%' THEN 'stpTeste_Trace1' WHEN [TextData] LIKE '%stpTeste_Trace2%' THEN 'stpTeste_Trace2' ELSE 'Outros' END [Nm_Objeto], COUNT(*) [QTD], SUM([Duration]) [Total], AVG([Duration]) [Media], MIN([Duration]) [Menor], MAX([Duration]) [Maior], SUM([Reads]) [Reads], SUM([writes]) [Writes], SUM([cpu]) [CPU] FROM [dbo].[Traces] WITH(NOLOCK) WHERE [Starttime] >= @Dt_Inicial AND [Starttime] < @Dt_Final -- Periodo a ser analizado GROUP BY CASE WHEN [TextData] LIKE '%stpTeste_Trace1%' THEN 'stpTeste_Trace1' WHEN [TextData] LIKE '%stpTeste_Trace2%' THEN 'stpTeste_Trace2' ELSE 'Outros' END ORDER BY [Total] DESC |
Essa query retorna o seguinte resultado:
Onde QTD é a quantidade de vezes que a procedure demorou mais de 3 segundos, Total é o tempo total dessas execuções, Media é o tempo médio dessas execuções, Menor e Maior são os menores e maiores tempos respectivamente, e Reads, Writes e CPU é a soma dos recursos que as execuções dessas procedures gastaram.
Para quem viu o primeiro artigo que escrevi Criando um CheckList Automático do Banco de Dados , eu recebo essa informação em minha planilha de CheckList diário com um filtro para pegar o trace de 08:00am as 18:00pm do dia anterior. Desta forma, analiso quais são as procedures que mais demoram no meu ambiente para melhorá-las.
Como já disse anteriormente, tenho esse procedimento implantado no meu principal servidor de produção e ele não consome muitos recursos do servidor. Logo vale apena implementá-lo levando em conta que a informação que você tem é muito valiosa. É possível identificar por exemplo, a partir de qual horário os procedimentos do seu ambiente começaram a apresentar uma lentidão ou que tem alguém da sua equipe de desenvolvimento rodando alguma query pesada no seu servidor de produção no meio do dia ou ainda que no momento que executam a procedure A, ela faz com que muitas outras procedures fiquem lentas. Dentre outros benefícios.
Sugiro fortemente que quem não possui uma rotina parecida com essa, tente implementá-la assim que possível.
Gostou dessa dica?
Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.
Aprenda a criar essa e outras rotinas para administrar melhor seu SQL Server no meu Treinamento de Tarefas do Dia a Dia de um DBA.
Abraços,
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Fabrício,Mais uma vez um ótimo texto. Gostei bastante da segunda parte do seu projeto. Seria interessante voce citar como esses processos ficaram quando colocados em produção, talvez medindo com os processos sendo executados e sem eles para quem estiver lendo perceba que não honera o seu ambiente.Como dito anteriormente, cada caso deve ser analisado com cuidado.Abraços,Alexandre Lopes
Fala Alexandre, obrigado pela dica.
Boa tarde Fabricio,
Rapaz, quero lhe parabenizar pelo seu esforço em colocar essas dicas e orientações de como organizar e otimizar bases de SQL Server. Éste e outros posts serão muito útil para mim aqui na empresa onde eu trabalho e administro uma base pequena (quase 6GB) e que está muito sobrecarregada.
Preciso identificar algumas queries que estão lentas no sistema interno nosso e já peguei os scripts que você disponibilizou e irei executá-los em breve aqui e depois lhe digo se consegui ou não.
Só tenho uma pergunta, antes de tudo: esses scripts que você disponibilizou, daria para executá-los no SQL Server 2000? A nossa base é do SQL Server 2000, pois estamos com um projeto em mente de migrar do 2000 para o 2005 pelo menos, mas teremos de montar um projeto e convencer o nosso Superintendente de que, migrando para uma plataforma mais moderna, teremos ganhos de custos e de tempo, como também de performance, dentreo outras vantagens, mas isso teremos de montar com calma.
Gostaria de entrar em contato contigo, se possível, por e-mail ou qualquer outro meio, para que possamos trocar idéias. Qualquer coisa, pode mandar e-mail para mim, beleza amigo?
Muito obrigado pela sua atenção em ler este post gigante.
Valeu José.
Infelizmente muitos scripts não rodam no SQL 2000 pois não existiam as “SALVADORAS DMV’s”. É fato que o 2005 é muito melhor que o sql 2000.
Pode me mandar um e-mail. Talvez eu demore um pouco para responder devido ao tempo ser curto, mas um dia respondo. rsrs
Qualquer dúvida mais urgente, o forum do technet tem MVP’s que sempre respondem bem rápido as perguntas.
Abraço
O script não funcionou mesmo. Tenho que ajustar algumas coisas do trace que ele não reconhece. Seria interessante executar esses teus scripts, mas vou dar uma analisada e ver se consigo alinhar ele as minhas necessidades.
Te mandarei um e-mail essa semana e conversamos, beleza?
Você mora em que lugar, vitória ou vila velha? Abraços!
Fabrício,
Primeiramente gostaria de agradecer por esta grande colaboração.
Estou com apenas uma dúvida…
Como defino o @bigintfilter conforme minhas necessidades? como calcular?
Exemplo: tenho uma base que desejo fazer trace de querys que demoram mais de 10min (é uma base só de importação de dados via LinkedServer). Como alterar este valor do @bigintfilter?
Deixa.. já encontrei: 1 minuto = 60000 milisegundos. O resto é só calcular 🙂
Estou com um problema. Quando tentei parar o trace, estou tendo erro.
Query:
Declare @Trace_Id int
SELECT @Trace_Id = TraceId
FROM fn_trace_getinfo(0)
where cast(value as varchar(50)) = ‘C:\Trace\Querys_Demoradas.trc’
exec sp_trace_setstatus @traceid = @Trace_Id, @status = 0 — Interrompe o rastreamento especificado.
exec sp_trace_setstatus @traceid = @Trace_Id, @status = 2
Erro:
Msg 214, Level 16, State 3, Procedure sp_trace_setstatus, Line 1
Procedure expects parameter ‘@traceid’ of type ‘int’.
Não entendi o erro uma vez que a variavel esta devidamente declarada como int.
OBS: acabei apagando os registros da tabela e não consigo apagar os registros do trace pq esta em uso.
Fala Ricardo.
Você rodando essa query, vem algum resultado?
SELECT *
FROM fn_trace_getinfo(0)
Abraços
Fabricio muito interessante seu artigo, o que ocorre comigo, é o mesmo erro que ocorreu com o Ricardo, e executando o comando que voce passou me retorna os traces criados, porém em nenhum momento retorna algum trace com o nome que criamos usando seu exemplo, desta forma o step 1 não funciona.
Muito interessante teu artigo, parabéns pelo empenho e pela parceria compartilhando teus conhecimentos com todos!
Sucesso pra voce!
Valeu Giovani, rodando a query abaixo é retornado algum resultado?
SELECT *
FROM fn_trace_getinfo(0)
Abraços
O meu aconteceu o mesmo problema.
Rodei este select e retorna os dados abaixo
traceid property value
———– ———– —————————————————————————————————————————————————————————————————————————————————————-
1 1 2
1 2 C:\Program Files\Microsoft SQL Server\MSSQL10_50.BD01\MSSQL\Log\log_119.trc
1 3 20
1 4 NULL
1 5 1
(5 row(s) affected)
Ola Fabricio, primeiramente queria lhe agradecer por uma contribuição tão valoroza. O meu problema é em relação aos steps, não consigo identificar as condições impostas no mesmo pela má qualidade das screens, é possivel disponibilzar um link para download? ou me enviar por email mesmo?
Obrigado.
Fala Everton.
As queries utilizadas nos steps estão no post. Só muda o type de um step para o outro(t-sql ou Cmdshell). Se tiver uma dúvida específica pode falar aqui pois estou conseguindo ver as figuras (não com perfeição).
Não tenho mais as figuras e tenho que descobrir como pega já que esse post foi importado automaticamente do meu blog antigo.
Abraços.
Olá Fabrício.
Sua solução está ajudando bastante, pois tenho acesso restrito a IDE do profiler e confesso não entender ainda muito bem cada um dos indicadores, mas o parâmetro de 3 segundos já me ajuda bastante.
Após criar a tabela e a procedure todos os eventos do servidor passaram a ser logados automaticamente. O que dispara isso e o faz rodar em background? se eu quiser parar esse monitoramento é só apagar a tabela e a procedure ?
Obrigado
Douglas
Olá Fabrício .
Sua solução está ajudando bastante, pois tenho acesso restrito a IDE do profiler e confesso não entender ainda muito bem cada um dos indicadores, mas o parâmetro de 3 segundos já me ajuda bastante.
Após criar a tabela e a procedure todos os eventos do servidor passaram a ser logados automaticamente. O que dispara isso e o faz rodar em background? se eu quiser parar esse monitoramento é só apagar a tabela e a procedure ?
Obrigado
Douglas
Olá Fabrício!
Parabéns pelos artigos publicados. São excelentes e muito bem explicados!
Agora estou me envolvendo mais com banco de dados na empresa e seus artigos estão me ajudando muito!
Elaine
Obrigado Elaine.
Boa tarde Fabrício,
muito bom o seu post. Estou com um problema no momento de criar o primeiro passo.
Está me dando o seguinte erro:
Mensagem 214, Nível 16, Estado 3, Procedimento sp_trace_setstatus, Linha 1
Procedure expects parameter ‘@traceid’ of type ‘int’.
O que posso fazer para corrigir?
Obrigado.
Daniel, esta dificuldade ocorre quando executa o STEP1 e ocorre um erro no STEP2, pois o comando “del C:\Trace\Querys_Demoradas.trc /Q”, tem que estar sem as aspas (“) para executar.
Para resolver exclua o arquivo criado em C:\Trace\Querys_Demoradas.trc, execute o comando
exec dbo.stpCreate_Trace, corrija o STEP2 sem as aspas.
Execute a job para ter certeza do seu funcionamento.
Fabricio, muito bom o post e muito obrigado por este blog.
Obrigado pela contribuição nos comentários Eduardo.
Olá Frabrício.
Primeiramente gostaria de parabenizá-lo pelo seu blog, muita informação útil e de qualidade inquestionável!
Estou seguindo as dicas deste post mas batendo na trave em algo simples: Quando o SQL Server cria o arquivo de Trace na pasta, ele faz sem respeitar as permissões que eu coloquei na mesma, fazendo com que na próxima execução do job o segundo passo (deletar o arquivo com o trace) falhe, pois o arquivo foi criado sem a permissão (que deveria ter sido herdada da pasta) para a conta que executa este passo, a NT Service\SQLSERVERAGENT.
O problema é que só tenho a opção de usar esta conta quando configuro o segundo step do job…
Tem alguma ideia de como eu poderia contornar esta situação.
Desde já agradeço, abraço.
Faz um teste. Cria uma pasta eLibera permissão para everyone nela. Se funcionar, só colocar o user do agente com acesso.