{"id":294,"date":"2010-06-05T09:34:40","date_gmt":"2010-06-05T12:34:40","guid":{"rendered":"http:\/\/fabriciodba.wordpress.com\/2010\/06\/05\/passo-a-passo-para-encontrar-as-querys-mais-demoradas-do-banco-de-dados-parte-2"},"modified":"2016-09-11T18:23:48","modified_gmt":"2016-09-11T21:23:48","slug":"passo-a-passo-para-encontrar-as-querys-mais-demoradas-do-banco-de-dados-parte-2","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2010\/06\/05\/passo-a-passo-para-encontrar-as-querys-mais-demoradas-do-banco-de-dados-parte-2\/","title":{"rendered":"Passo a passo para encontrar as querys mais demoradas do Banco de Dados – Parte 2"},"content":{"rendered":"
\n

Continuando o artigo Passo a passo para encontrar as querys mais demoradas do Banco de Dados – Parte 1<\/a>.<\/p>\n

Agora que o trace<\/em> j\u00e1 foi testado e vimos que ele realmente funciona, criaremos um job<\/em> para enviar o trace<\/em> para a tabela de log<\/em> e automatizar todo o processo.<\/p>\n

Voc\u00ea deve criar um Job<\/em> no Management Studio<\/em> com o nome \u201cDBA – Trace Querys Demoradas\u201d<\/em> e esse job<\/em> deve possuir 3 steps<\/em>.<\/p>\n

STEP<\/em> 1<\/strong> –<\/strong> No primeiro Step<\/em> devemos parar o trace momentaneamente para enviar todo o seu resultado para a tabela de log. Nesse step<\/em>, voc\u00ea deve selecionar a database<\/em> em que vc criou a tabela que armazenar\u00e1 o trace<\/em> e incluir a query<\/em> abaixo no step<\/em>.<\/p>\n

\r\nDECLARE @Trace_Id INT\r\n\r\nSELECT @Trace_Id = [TraceId]\r\nFROM fn_trace_getinfo(0)\r\nWHERE CAST([value] AS VARCHAR(50)) = 'C:\\Trace\\Querys_Demoradas.trc'\r\n\r\nEXEC [dbo].[sp_trace_setstatus]  @traceid = @Trace_Id,  @status = 0 -- Interrompe o rastreamento especificado.\r\n\r\nEXEC [dbo].[sp_trace_setstatus]  @traceid = @Trace_Id,  @status = 2 -- Fecha o rastreamento especificado e exclui sua defini\u00e7\u00e3o do servidor.\r\n\r\nINSERT INTO [dbo].[Traces] (\t[Textdata], [NTUserName], [HostName], [ApplicationName], [LoginName], [SPID], [Duration], [Starttime],\r\n\t\t\t\t\t\t\t\t[EndTime], [Reads], [writes], [CPU], [Servername], [DatabaseName], [rowcounts], [SessionLoginName] )\r\nSELECT\t[Textdata], [NTUserName], [HostName], [ApplicationName], [LoginName], [SPID], CAST([Duration] \/ 1000\/ 1000.00 AS NUMERIC(15, 2)) [Duration], [Starttime],\r\n\t\t[EndTime], [Reads], [writes], [CPU], [Servername], [DatabaseName], [rowcounts], [SessionLoginName]\r\nFROM :: fn_trace_gettable('C:\\Trace\\Querys_Demoradas.trc', default)\r\nWHERE [Duration] IS NOT NULL\r\nORDER BY [Starttime]\r\n<\/pre>\n
Segue abaixo a figura de como fica esse step<\/em>:<\/p>\n
\"Step1_thumb1\"<\/a><\/p>\n
STEP<\/em> 2 –<\/strong> Agora que os dados do trace<\/em> j\u00e1 foram armazenados na tabela, deve-se excluir o arquivo de trace<\/em> para que um novo seja criado. Isso pode ser realizado executando o comando \u201cdel C:\\Trace\\Querys_Demoradas.trc \/Q<\/strong><\/em>\u201d como pode ser visto na figura abaixo:<\/p>\n
\"Step2_thumb1\"<\/a><\/p>\n
STEP 3 –<\/strong> Esse passo deve apenas recriar o trace<\/em>. Similar ao step 1<\/em>, voc\u00ea deve selecionar a database<\/em> em que vc criou a procedure e rodar a query<\/em> abaixo:<\/p>\n
\r\nEXEC [dbo].[stpCreate_Trace]\r\n<\/pre>\n
No meu ambiente, esse job<\/em> roda de hora em hora no meu principal servidor de produ\u00e7\u00e3o, mas isso depende da sua necessidade. Eu tamb\u00e9m excluo os registros com mais de 60 dias, para que a tabela de log<\/em> n\u00e3o fique muito grande, ja que n\u00e3o analiso esses registros mais antigos.<\/p>\n
Agora que nosso procedimento ja est\u00e1 em produ\u00e7\u00e3o, \u00e9 possivel atacar as querys<\/em> mais demoradas.<\/p>\n
No meu ambiente, identifico as procedures<\/em> que mais retornam no trace<\/em> e realizo um agrupamento, como feito abaixo, para facilitar minha visualiza\u00e7\u00e3o di\u00e1ria. Para um exemplo, vou criar mais uma procedure<\/em> de teste e execut\u00e1-las algumas vezes.<\/p>\n
\r\nCREATE PROCEDURE [dbo].[dbo].[stpTeste_Trace2]\r\nAS\r\nBEGIN\r\n    WAITFOR DELAY '00:00:04'\r\nEND\r\nGO\r\nEXEC [dbo].[stpTeste_Trace1] -- Procedure ja criada\r\nGO\r\nEXEC [dbo].[stpTeste_Trace1]\r\nGO\r\nEXEC [dbo].[stpTeste_Trace2]\r\nGO\r\nEXEC [dbo].[stpTeste_Trace2]\r\nGO\r\nEXEC [dbo].[stpTeste_Trace2]\r\nGO\r\nEXEC [dbo].[stpTeste_Trace2]\r\nGO\r\n<\/pre>\n
Depois de executadas, rodarei o Job<\/em> para importar o arquivo de trace<\/em> para a tabela de log<\/em>.<\/p>\n
\r\nEXEC [msdb]..[sp_start_job] @job_name = 'DBA - Trace Querys Demoradas'\r\n<\/pre>\n
Com a query<\/em> abaixo, podemos visualizar os dados armazenados no trace<\/em> agrupados por procedure<\/em>.<\/p>\n
\r\nDECLARE @Dt_Inicial DATETIME, @Dt_Final DATETIME\r\n\r\nSELECT\t@Dt_Inicial = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME), \r\n\t\t@Dt_Final = CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)\r\n\r\nSELECT\tCASE    \r\n\t\t\tWHEN [TextData] LIKE '%stpTeste_Trace1%' THEN 'stpTeste_Trace1'\r\n\t\t\tWHEN [TextData] LIKE '%stpTeste_Trace2%' THEN 'stpTeste_Trace2'\r\n\t\t\tELSE 'Outros' \r\n\t\tEND [Nm_Objeto],\r\n\t\tCOUNT(*) [QTD], \r\n\t\tSUM([Duration]) [Total], \r\n\t\tAVG([Duration]) [Media], \r\n\t\tMIN([Duration]) [Menor], \r\n\t\tMAX([Duration]) [Maior],\r\n\t\tSUM([Reads]) [Reads], \r\n\t\tSUM([writes]) [Writes], \r\n\t\tSUM([cpu]) [CPU]\r\nFROM [dbo].[Traces] WITH(NOLOCK)\r\nWHERE [Starttime] >= @Dt_Inicial AND [Starttime] < @Dt_Final -- Periodo a ser analizado\r\nGROUP BY\tCASE    \r\n\t\t\t\tWHEN [TextData] LIKE '%stpTeste_Trace1%' THEN 'stpTeste_Trace1'\r\n\t\t\t\tWHEN [TextData] LIKE '%stpTeste_Trace2%' THEN 'stpTeste_Trace2'\r\n\t\t\t\tELSE 'Outros' \r\n\t\t\tEND\r\nORDER BY [Total] DESC\r\n<\/pre>\n
Essa query<\/em> retorna o seguinte resultado:<\/p>\n
\"ResultadoQuerysAgrupadas_thumb1\"<\/a><\/p>\n
Onde QTD<\/em> \u00e9 a quantidade de vezes que a procedure demorou mais de 3 segundos, Total<\/em> \u00e9 o tempo total dessas execu\u00e7\u00f5es, Media<\/em> \u00e9 o tempo m\u00e9dio dessas execu\u00e7\u00f5es, Menor<\/em> e Maior<\/em> s\u00e3o os menores e maiores tempos respectivamente, e Reads<\/em>, Writes<\/em> e CPU<\/em> \u00e9 a soma dos recursos que as execu\u00e7\u00f5es dessas procedures gastaram.<\/p>\n
Para quem viu o primeiro artigo que escrevi Criando um CheckList Autom\u00e1tico do Banco de Dados<\/a> , eu recebo essa informa\u00e7\u00e3o em minha planilha de CheckList<\/em> di\u00e1rio com um filtro para pegar o trace<\/em> de 08:00am as 18:00pm do dia anterior. Desta forma, analiso quais s\u00e3o as procedures<\/em> que mais demoram no meu ambiente para melhor\u00e1-las.<\/p>\n
Como j\u00e1 disse anteriormente, tenho esse procedimento implantado no meu principal servidor de produ\u00e7\u00e3o e ele n\u00e3o consome muitos recursos do servidor. Logo vale apena implement\u00e1-lo levando em conta que a informa\u00e7\u00e3o que voc\u00ea tem \u00e9 muito valiosa. \u00c9 poss\u00edvel identificar por exemplo, a partir de qual hor\u00e1rio os procedimentos do seu ambiente come\u00e7aram a apresentar uma lentid\u00e3o ou que tem algu\u00e9m da sua equipe de desenvolvimento rodando alguma query<\/em> pesada no seu servidor de produ\u00e7\u00e3o no meio do dia ou ainda que no momento que executam a procedure<\/em> A, ela faz com que muitas outras procedures<\/em> fiquem lentas. Dentre outros benef\u00edcios.<\/p>\n
Sugiro fortemente que quem n\u00e3o possui uma rotina parecida com essa, tente implement\u00e1-la assim que poss\u00edvel.<\/p>\n
 
\nGostou dessa dica?<\/b><\/p>\n
Cadastre seu e-mail para receber novos Posts e curta minha P\u00e1gina no Facebook<\/a> para receber Dicas de Leituras e Eventos sobre SQL Server.<\/p>\n
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<\/a>.<\/p>\n
Abra\u00e7os,<\/p>\n
Fabr\u00edcio Lima<\/p>\n
MCITP \u2013 Database Administrator<\/p>\n
Consultor e Instrutor SQL Server<\/p>\n
Trabalha com SQL Server desde 2006\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
Continuando o artigo Passo a passo para encontrar as querys mais demoradas do Banco de Dados – Parte 1. Agora que o trace j\u00e1 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\u00ea deve criar um Job no […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[3,280],"tags":[18,23,26,65,63,48,66,31,64,49,33,50,34,35,67,40],"class_list":["post-294","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-virtual-pass-br","tag-checklist","tag-dba","tag-excel","tag-lentidao","tag-performance","tag-planilha","tag-problema","tag-profile","tag-query-lenta","tag-sql","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-sql-server-2008-r2","tag-sql-server-passo-a-passo","tag-tuning"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/294","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/comments?post=294"}],"version-history":[{"count":0,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/294\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=294"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}