Post Views 5,254 views
Hey Guys!
Como vocês estão ?
Neste post eu vou demonstrar a vocês como identificar e coletar informações de consultas demoradas utilizando Trace (SQL Server Profiler). Isso é muito útil para lhe auxiliar em análises de performance tuning, facilitando a identificação de rotinas que estejam com tempo de resposta alto, tanto execução de procedures quanto queries ad-hoc.
No dia a dia, utilizo muito esse recurso para me ajudar a identificar timeouts em aplicações (Duração da query = tempo máximo de timeout configurado na app) e possíveis consultas candidatas a realizar um trabalho de performance tuning.
O fluxo dessa rotina funciona da seguinte forma:
- Verifica se o trace já está ativo.
- Caso o trace esteja ativo, desativa o trace e fecha o arquivo
- Cria a tabela de histórico das consultas (caso não exista).
- Lê os dados do arquivo de trace e insere na tabela de histórico
- Ativa o recurso xp_cmdshell dinamicamente (caso não esteja ativado)
- Apaga o arquivo de trace
- Desativa o recurso xp_cmdshell dinamicamente (caso não estava ativado antes)
- Cria novamente o trace
- Ativa o trace recém criado
A ideia é que seja criado um Job que é executado a cada X minutos que execute todo esse processo, limpando o arquivo de trace e inserindo os dados coletados na tabela de histórico para que os DBA’s possam consultar os dados lidos do arquivo de trace.
Caso você queira conhecer uma solução que utilize Extended Events (XE) ao invés do Trace, que é uma tecnologia mais moderna e intuitiva, dê uma lida no meu artigo SQL Server – Como identificar e coletar informações de consultas demoradas utilizando Extended Events (XE).
Código-fonte da rotina de coleta
|
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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 |
-------------------------------------------------------- -- Armazena os resultados do Trace na tabela -------------------------------------------------------- DECLARE @Trace_Id INT, @Path VARCHAR(MAX) SELECT @Trace_Id = id, @Path = [path] FROM sys.traces WHERE [path] LIKE '%Query_Demorada.trc' IF (@Trace_Id IS NOT NULL) BEGIN -- Interrompe o rastreamento especificado. EXEC sys.sp_trace_setstatus @Trace_Id = @Trace_Id, @status = 0 -- Fecha o rastreamento especificado e exclui sua definição do servidor. EXEC sys.sp_trace_setstatus @Trace_Id = @Trace_Id, @status = 2 IF (OBJECT_ID('dbo.Historico_Query_Demorada') IS NULL) BEGIN CREATE TABLE [dbo].[Historico_Query_Demorada] ( [TextData] [text] NULL, [NTUserName] [varchar] (128) NULL, [HostName] [varchar] (128) NULL, [ApplicationName] [varchar] (128) NULL, [LoginName] [varchar] (128) NULL, [SPID] [int] NULL, [Duration] [numeric] (15, 2) NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, [Reads] [int] NULL, [Writes] [int] NULL, [CPU] [int] NULL, [ServerName] [varchar] (128) NULL, [DataBaseName] [varchar] (128) NULL, [RowCounts] [int] NULL, [SessionLoginName] [varchar] (128) NULL ) WITH ( DATA_COMPRESSION = PAGE ) CREATE CLUSTERED INDEX [SK01_Traces] ON [dbo].[Historico_Query_Demorada] ([StartTime]) WITH (FILLFACTOR=80, STATISTICS_NORECOMPUTE=ON, DATA_COMPRESSION = PAGE) ON [PRIMARY] END INSERT INTO dbo.Historico_Query_Demorada ( 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(@Path, DEFAULT) WHERE Duration IS NOT NULL AND Reads < 100000000 ORDER BY StartTime; -------------------------------------------------------- -- Apaga o arquivo de trace -------------------------------------------------------- DECLARE @Fl_Xp_CmdShell_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'xp_cmdshell') IF (@Fl_Xp_CmdShell_Ativado = 0) BEGIN EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE; END DECLARE @Cmd VARCHAR(4000) = 'del ' + @Path + ' /Q' EXEC sys.xp_cmdshell @Cmd IF (@Fl_Xp_CmdShell_Ativado = 0) BEGIN EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE; EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE; END END -------------------------------------------------------- -- Ativa o trace novamenmte -------------------------------------------------------- DECLARE @resource INT, @maxfilesize BIGINT = 50, @on BIT = 1, -- Habilitado @bigintfilter BIGINT = (1000000 * 7) -- 7 segundos -- Criação do trace SET @Trace_Id = NULL EXEC @resource = sys.sp_trace_create @Trace_Id OUTPUT, 0, N'C:\Traces\Query_Demorada', @maxfilesize, NULL IF (@resource = 0) BEGIN EXEC sys.sp_trace_setevent @Trace_Id, 10, 1, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 6, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 8, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 10, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 11, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 12, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 13, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 14, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 15, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 16, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 17, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 18, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 26, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 35, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 40, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 48, @on EXEC sys.sp_trace_setevent @Trace_Id, 10, 64, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 1, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 6, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 8, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 10, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 11, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 12, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 13, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 14, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 15, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 16, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 17, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 18, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 26, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 35, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 40, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 48, @on EXEC sys.sp_trace_setevent @Trace_Id, 12, 64, @on -- Aqui é onde filtramos o tempo da query que irá cair no trace EXEC sys.sp_trace_setfilter @Trace_Id, 13, 0, 4, @bigintfilter -- O 4 significa >= @bigintfilter -- Ativa o trace EXEC sys.sp_trace_setstatus @Trace_Id, 1 END |
Result:
Na minha rotina, eu configurei para coletar qualquer query que execute por mais de 7 segundos. Fique à vontade para alterar e aplicar filtros conforme sua necessidade.
And that's it, folks!
Um abraço e até a próxima.
Portuguese 
English
Está dando erro ao executar este código
Erro do sistema operacional ao executar SP_TRACE_CREATE. Erro = 0x80070005(Acesso negado.).
Msg 19062, Nível 16, Estado 1, Procedimento sys.sp_trace_create, Linha 1 [Linha de Início do Lote 0]
Impossível criar um arquivo de rastreamento.
quando tento executar este código de coleta do trace dá este erro
Erro do sistema operacional ao executar SP_TRACE_CREATE. Erro = 0x80070005(Acesso negado.).
Msg 19062, Nível 16, Estado 1, Procedimento sys.sp_trace_create, Linha 1 [Linha de Início do Lote 0]
Impossível criar um arquivo de rastreamento.
a pasta criada c:\Querys_Demoradas está com permissão total e mesmo assim dá este erro, o que pode estar acontecendo ?
Ao executar este código de coleta esta dando este erro
Erro do sistema operacional ao executar SP_TRACE_CREATE. Erro = 0x80070005(Acesso Negado.).
Msg 19062, Nível 16, Estado 1, Procedimento sys.sp_trace_create, Linha 1 [Linha de Início do Lote 0]
Impossível criar um arquivo de rastreamento.
a pasta no windows esta´com acesso total, para usuário do sql