Views: 4.698 views
Hello everybody!
How are you ?
In this post I will demonstrate to you how to identify and collect information from time consuming queries using Trace (SQL Server Profiler). This is very useful to assist you in performance tuning analysis, making it easy to identify routines that have high response time, both procedure execution and ad-hoc queries.
In everyday life, I use this feature a lot to help me identify timeouts in applications (Query Duration = maximum timeout time configured in the app) and possible queries candidates to perform a performance tuning job.
The flow of this routine works as follows:
- Checks if the trace is already active.
- If trace is active, deactivate trace and close the file
- Creates the query history table (if none exists).
- Read data from trace file and insert into history table
- Activates the xp_cmdshell feature dynamically (if not enabled)
- Delete trace file
- Disables the xp_cmdshell feature dynamically (if not previously enabled)
- Create the trace again
- Enable newly created trace
The idea is to create a Job that runs every X minutes that runs this entire process, cleaning the trace file and inserting the data collected in the history table so that DBA's can consult the data read from the trace file.
If you want to know a solution that uses Extended Events (XE) instead of Trace, which is a more modern and intuitive technology, read my article SQL Server - How to identify and collect time consuming query information using Extended Events (XE).
Collection routine source code
|
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:
In my routine, I set it to collect any query that runs for more than 7 seconds. Feel free to change and apply filters as needed.
That's it folks!
A hug and see you next.
Is giving error while executing this code
Operating system error running SP_TRACE_CREATE. Error = 0x80070005 (Access is denied.).
Msg 19062, Level 16, State 1, Procedure sys.sp_trace_create, Line 1 [Batch Start Line 0]
Unable to create a trace file.
when i try to execute this trace collection code it gives this error
Operating system error running SP_TRACE_CREATE. Error = 0x80070005 (Access is denied.).
Msg 19062, Level 16, State 1, Procedure sys.sp_trace_create, Line 1 [Batch Start Line 0]
Unable to create a trace file.
the created folder c: \ Querys_Demoradas is with full permission and yet gives this error, what could be happening?
Running this collection code is giving this error
Operating system error running SP_TRACE_CREATE. Error = 0x80070005 (Access Denied.).
Msg 19062, Level 16, State 1, Procedure sys.sp_trace_create, Line 1 [Batch Start Line 0]
Unable to create a trace file.
windows folder has full access for sql user