{"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":"","jetpack_post_was_ever_published":false},"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"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":276,"url":"https:\/\/fabriciolima.net\/blog\/2011\/01\/26\/querys-do-dia-a-dia-acompanhando-as-querys-mais-demoradas-do-banco-de-dados\/","url_meta":{"origin":294,"position":0},"title":"Querys do Dia a Dia – Acompanhando as querys mais demoradas do Banco de Dados","author":"Fabr\u00edcio Lima","date":"26 de janeiro de 2011","format":false,"excerpt":"Continuando minha s\u00e9rie de Querys que utilizo no meu dia a dia, hoje mostrarei uma query que rodo v\u00e1rias vezes durante o dia. Muitos de voc\u00eas j\u00e1 devem ter visto meu artigo Passo a passo para encontrar as querys mais demoradas do Banco de Dados,\u00a0onde mostro como criar um profile\u2026","rel":"","context":"Em "Querys do Dia a Dia"","block_context":{"text":"Querys do Dia a Dia","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/querys-do-dia-a-dia\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":293,"url":"https:\/\/fabriciolima.net\/blog\/2010\/06\/05\/passo-a-passo-para-encontrar-as-querys-mais-demoradas-do-banco-de-dados-parte-1\/","url_meta":{"origin":294,"position":1},"title":"Passo a passo para encontrar as querys mais demoradas do Banco de Dados – Parte 1","author":"Fabr\u00edcio Lima","date":"5 de junho de 2010","format":false,"excerpt":"Fala Pessoal, Quem n\u00e3o quer saber quais s\u00e3o as querys mais demoradas do seu ambiente de Banco de Dados? Em quais hor\u00e1rios essas querys rodam? Quem est\u00e1 executando? De onde est\u00e1 sendo rodada? Quais os recursos que essas querys consomem? Nesse artigo, demonstrarei como eu fa\u00e7o esse acompanhamento no meu\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"Resultado Query Trace","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/resultadoquerytrace5b65d.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/resultadoquerytrace5b65d.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/resultadoquerytrace5b65d.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/resultadoquerytrace5b65d.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":6982,"url":"https:\/\/fabriciolima.net\/blog\/2017\/05\/01\/criando-um-e-mail-de-checklist-diario-no-sql-server\/","url_meta":{"origin":294,"position":2},"title":"Criando um E-mail de CheckList Di\u00e1rio no SQL Server","author":"Fabr\u00edcio Lima","date":"1 de maio de 2017","format":false,"excerpt":"Update dia 22\/09\/2019: Veja nesse post uma nova vers\u00e3o para a cria\u00e7\u00e3o desse Checklist para seu BD SQL Server junto com outros 40 alertas: http:\/\/www.fabriciolima.net\/blog\/2019\/09\/22\/passo-a-passo-de-como-criar-40-alertas-para-monitorar-seu-sql-server\/ \u00a0 \u00a0 Ol\u00e1 Pessoal, No dia 07\/03\/2010 eu fiz meu primeiro post para esse blog: Apresenta\u00e7\u00e3o Contudo, foi no dia 24\/03\/2010 que eu comecei a\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"checklist","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2014\/01\/checklist.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":517,"url":"https:\/\/fabriciolima.net\/blog\/2011\/03\/09\/querys-do-dia-a-dia-como-visualizar-o-historico-de-um-job\/","url_meta":{"origin":294,"position":3},"title":"Querys do Dia a Dia – Como Visualizar o Hist\u00f3rico de um Job","author":"Fabr\u00edcio Lima","date":"9 de mar\u00e7o de 2011","format":false,"excerpt":"Querys do Dia a Dia - Como Visualizar o Hist\u00f3rico de um Job no SQL Server","rel":"","context":"Em "Querys do Dia a Dia"","block_context":{"text":"Querys do Dia a Dia","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/querys-do-dia-a-dia\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2011\/03\/jobs-300x253.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":268,"url":"https:\/\/fabriciolima.net\/blog\/2011\/01\/17\/querys-do-dia-a-dia-como-encontrar-um-texto-em-functions-views-e-procedures\/","url_meta":{"origin":294,"position":4},"title":"Querys do Dia a Dia – Como encontrar um texto em Functions, Views e Procedures","author":"Fabr\u00edcio Lima","date":"17 de janeiro de 2011","format":false,"excerpt":"Fala Pessoal, Antes de incluir\/excluir\/alterar um campo de uma tabela ou antes de incluir\/excluir\/alterar uma procedure, function ou view se faz necess\u00e1rio procurar no script do banco de dados se esses objetos que ser\u00e3o alterados s\u00e3o utilizados\u00a0por alguma fun\u00e7\u00e3o, view ou SP. Constantemente eu preciso realizar esse tipo de opera\u00e7\u00e3o.\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":11132,"url":"https:\/\/fabriciolima.net\/blog\/2019\/11\/20\/como-criar-mais-de-30-alertas-por-e-mail-no-sql-server-rds-da-amazon\/","url_meta":{"origin":294,"position":5},"title":"Como criar mais de 30 alertas por e-mail no SQL Server RDS da Amazon","author":"Fabr\u00edcio Lima","date":"20 de novembro de 2019","format":false,"excerpt":"Fala Pessoal, Para quem encontrou meu Blog s\u00f3 agora, desde setembro j\u00e1 tinha liberado scripts para criar 40 alertas no SQL Server: https:\/\/www.fabriciolima.net\/blog\/2019\/09\/22\/passo-a-passo-de-como-criar-40-alertas-para-monitorar-seu-sql-server\/ Tamb\u00e9m gravei um mini curso gr\u00e1tis explicando em detalhes como configurar esses 40 alertas: https:\/\/cursos.fabriciolima.net\/course?courseid=criando-40-alertas-para-monitorar-o-sql-server Contudo, esses alertas funcionam para voc\u00ea que administra uma inst\u00e2ncia \"normal\" do\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/fabriciolima.net\/blog\/wp-content\/uploads\/2019\/11\/Amazon-RDS-300x150.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"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}]}}