RML Utilities is a free utility created by Microsoft and used by the SQL Server support team. It’s available for download and if you work with SQL Trace data at all I’d recommend giving it a look. I know, I know, Trace is deprecated in SQL Server 2012. But not everyone is running 2012 so until then, Trace files will still exist.
I first heard about RML Utilities years ago from Andrew Kelly ( b | t ) and have used it to analyze trace data from customers, which is really just a fraction of what it can do. Today I’ve been working with it to compare two trace files. Yes, that’s right…compare two trace files. Imagine you run a load test in your production environment, then run that same test in your development environment which might have different hardware or updated code. You can capture PerfMon metrics to compare Memory, CPU and I/O…but what about the performance of individual queries? You can get that from the DMVs, but it’s really nice if you can look at the information side by side. And I don’t mean two instances of Profiler open, I mean really compare the data. RML Utilities will do that for you!
But, before I could get RML Utilities to do that, I had to get it to process a trace file. This was my issue today. I had two VMs, one with 2008R2 and 2012 installed, one with only 2012 installed. The readtrace.exe file is used to process trace files, and the commands require the trace file (input), an output directory, a SQL Server instance and a database. I had everything set up, but my process would fail with this set of messages:
09/27/12 15:49:44.262 [0X00000AA4] Readtrace a SQL Server trace processing utility.
Version 9.01.0109 built for x64.
Copyright (c) Microsoft Corporation 1997-2008. All rights reserved.
…extra text removed…
09/27/12 15:49:44.263 [0X00000AA4] –IC:\PerfLogs\Trace\File_RML_2008R2.trc
09/27/12 15:49:44.263 [0X00000AA4] –oC:\SQLskills\RMLOutput
09/27/12 15:49:44.263 [0X00000AA4] –SWIN2008R2PS\SQL2012
09/27/12 15:49:44.263 [0X00000AA4] –dBaselineData
09/27/12 15:49:44.263 [0X00000AA4] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x00060101 and Defined: 0x00060101
09/27/12 15:49:44.263 [0X00000AA4] Attempting to cleanup existing RML files from previous execution
09/27/12 15:49:44.264 [0X00000AA4] Using extended RowsetFastload synchronization
09/27/12 15:49:44.264 [0X00000AA4] Establishing initial database connection:
09/27/12 15:49:44.264 [0X00000AA4] Server: WIN2008R2PS\SQL2012
09/27/12 15:49:44.264 [0X00000AA4] Database: BaselineData
09/27/12 15:49:44.265 [0X00000AA4] Authentication: Windows
09/27/12 15:49:44.266 [0X00000AA4] Unable to connect to the specified server.
09/27/12 15:49:44.266 [0X00000AA4] ERROR: Performance analysis failed to initialize. See previous errors and correct the problem before retrying.
09/27/12 15:49:44.266 [0X00000AA4] *******************************************************************************
* ReadTrace encountered one or more ERRORS. An error condition typically *
* stops processing early and the ReadTrace output may be unusable. *
* Review the log file for details. *
*******************************************************************************
09/27/12 15:49:44.266 [0X00000AA4] ***** ReadTrace exit code: –9
My first thought was that it was having an issue with SQL Server 2012. The documentation does not list SQL Server 2012 as a supported version. So I tried it on my local machine against a 2012 instance, and it worked. I reviewed the error message: “Unable to connect to the specified server.” This made no sense, so I verified that I could connect with sqlcmd. Then I tried it against my other VM, thinking maybe it was something with VMWare. That worked. Then I started comparing the output. From the successful process, I had the following:
09/27/12 15:59:07.349 [0X00000CD4] Readtrace a SQL Server trace processing utility.
Version 9.01.0109 built for x64.
Copyright (c) Microsoft Corporation 1997-2008. All rights reserved.
…extra text removed…
09/27/12 15:59:07.350 [0X00000CD4] –IC:\PerfLogs\Trace\File_RML_2008R2.trc
09/27/12 15:59:07.350 [0X00000CD4] –oC:\SQLskills\RMLOutput
09/27/12 15:59:07.350 [0X00000CD4] –SWIN2008R2-1\SQL2012
09/27/12 15:59:07.350 [0X00000CD4] –dBaselineData
09/27/12 15:59:07.350 [0X00000CD4] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x00060101 and Defined: 0x00060101
09/27/12 15:59:07.350 [0X00000CD4] Attempting to cleanup existing RML files from previous execution
09/27/12 15:59:07.351 [0X00000CD4] Using extended RowsetFastload synchronization
09/27/12 15:59:07.351 [0X00000CD4] Establishing initial database connection:
09/27/12 15:59:07.351 [0X00000CD4] Server: WIN2008R2-1\SQL2012
09/27/12 15:59:07.351 [0X00000CD4] Database: BaselineData
09/27/12 15:59:07.351 [0X00000CD4] Authentication: Windows
09/27/12 15:59:07.390 [0X00000CD4] Using SQL Client version 10
09/27/12 15:59:07.390 [0X00000CD4] Creating or clearing the performance database
09/27/12 15:59:07.892 [0X00000CD4] Processing file: C:\PerfLogs\Trace\File_RML_2008R2.trc (SQL 2008)
09/27/12 15:59:07.892 [0X00000CD4] Validating core events exist
09/27/12 15:59:07.892 [0X00000CD4] Validating necessary events exist for analysis
…more extra text removed…
My issue was that RML Utilities uses version 10 of the Client. On the VM I only had SQL 2012 installed, which is version 11. Once I downloaded and installed the 2008R2 Client (from here, you just need sqlncli_amd64.msi for an x64 machine), everything ran fine. Thus, even though SQL 2012 is not listed as a supported version for RML Utilities, it looks like it will run as long as you have version 10 of the Client installed. And as for comparing those files? That’s for another post!
13 thoughts on “RML Utilities and SQL Server 2012”
This explains the problems I was having running ReadTrace on my new machine with only SQL 2012 installed, and got the error ‘Unable to connect to the specified server’ even when specifying a remote 2008R2 instance. After installing client version 10 it works perfectly both against 2012 and 2008, thanks a lot for sharing!
Thks Erin, it’s solve my problem too…
…saved my day!, thanks! 🙂
Thank you! Great tips.
FYI
All local SQL 2012 server and client installed
added sqlncli_amd64.msi
added ReportViewer.exe
added RMLSetup_AMD64.msi
08/05/13 13:07:16.920 [0X0000254C] Establishing initial database connection:
08/05/13 13:07:16.921 [0X0000254C] Server: (local)
08/05/13 13:07:16.921 [0X0000254C] Database: xTrace
08/05/13 13:07:16.922 [0X0000254C] Authentication: Windows
08/05/13 13:07:17.104 [0X0000254C] Using SQL Client version 10
08/05/13 13:07:17.104 [0X0000254C] Creating or clearing the performance database
08/05/13 13:07:18.250 [0X0000254C] The major version number (11) in the trace fi
le header is not a supported file version.
08/05/13 13:07:18.250 [0X0000254C] At this time only Microsoft SQL Server 2000,
2005 and 2008 trace files are supported. The current trace version (11) is not s
upported.
08/05/13 13:07:18.251 [0X0000254C] ERROR: Read of file header for file C:\CE Tas
ks\Target 2013_08_02\8-2 Round 2 Trace #2.trc failed with operating system error
0x8007000D (The data is invalid)
08/05/13 13:07:18.262 [0X0000254C] *** ERROR: Attempt to initialize trace file r
eader failed with operating system error 0x8007000D (The data is invalid)
08/05/13 13:07:18.262 [0X0000254C] Reads completed – Global Error Status 0xfffff
ffe
08/05/13 13:07:18.263 [0X0000254C] Shutting down the worker thread message queue
s.
Hi-
Not sure if there’s a question in there – but the output states:
08/05/13 13:07:18.250 [0X0000254C] At this time only Microsoft SQL Server 2000,
2005 and 2008 trace files are supported. The current trace version (11) is not supported.
So you can use SQL 2012 to host the database for RML Utils, but you cannot process a SQL Server 2012 (version 11). At present RML Utils does not support processing of a trace file from SQL Server 2012.
Hope that helps!
Erin
Thank you guys!!!, I saved a lot of time following your posts.
Greetings Erin. We’ve worked together a couple years ago at the State of AZ (I actually just left there last week). Great to see you again, and great article! Your a rock star!
Chris Rose
Hi Chris!
Great to hear from you 🙂 I definitely remember working with you – you are a great DBA and made my job much easier! I hope that you’re doing well – drop me a line if you ever need anything.
Erin
check this out:
https://simplereadtracedemo.codeplex.com/SourceControl/latest#helpers/ConvertTraceTo2008.ps1
br,
/david
Hello everyone,
I have managed to get a BlackBox trace to work running on a 2012 SQL Server that loads into RML
(This stored proc defaults to a 15 minute trace or use #minutes [not-required] parameter.)
This is useful and loadable to get the valuable RML report…
Readtrace -E -S -I
(…However, when I provide all the Events & Columns RML Wants RML loading fails…)
——————————–
USE [master]
GO
/****** Object: StoredProcedure [dbo].[Black_Box_Trace] Script Date: 9/18/2015 3:40:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==============================================================================
* Stored Procedure: Black_Box_Trace
* ==============================================================================
* Description: Initiates black box trace on dataserver to \\server\backup dir…
* (Not an option 8 black box “last 5mb Flight Recorder”)
* IMPORTANT:
(View traces on server as follows)
SELECT * FROM ::fn_trace_getinfo(0)
(Kill trace on server as follows)
EXEC sp_trace_setstatus 2, 0
EXEC sp_trace_setstatus 2, 2
select @@Servername
(Load trace in RML Cmd Window with the following command)
ReadTrace -E -Ssqltest02\mssql2012 -I\\HSISQLVSERVER2\shares\Tracing\HSISQLVSERVER2_BB_Trace_20150918_1401_15min.trc
*
* Parameters: none
*
* Example Call: exec Black_Box_Trace 5
*
*===============================================================================
* Change History
* When Who Change Description
* ——– — —————————————————————–
* 201400417 JES Original Creation – SSRS Report SQL encapsulated into this proc
* 201400508 JES Alter to adapt for 15 minute job call that will load trace to
* table for Rapid P&T analysis .
* 20150918 JES Alter to add Columns needed for RML (added Comments as well).
*==============================================================================*/
alter proc [dbo].[Black_Box_Trace]
(
@minutes int = 15
)
as
begin
DECLARE @TraceID int,
@filetime char(80),
@tracefile nvarchar(256),
@str_manip varchar(20),
@bigintnum bigint,
@on bit,
@stop datetime
–,@minutes int
— set @minutes = 25
if @minutes not between 1 and 480 set @minutes = 15
set @stop = dateadd(mi, @minutes, getdate())
set @on = 1
set @bigintnum = 3072
select @str_manip = CONVERT(varchar(12), getdate(), 114)
select @str_manip = substring(@str_manip, 1, 2) + substring(@str_manip, 4, 2);
select @str_manip = @str_manip + ‘_’ + convert(varchar(3), @minutes) + ‘min’
–select @Str_manip
———————————————————————————————-
— Be aware that if you have a “Named Servers with \ in the name you should copy the proc from
— HSITEST\MSSQLSERVER2012…. In that version deals with the backslash
———————————————————————————————-
select @tracefile = N’\\’+@@servername +’\Shares\Tracing\’+replace(@@servername,’\’,’-‘)+’_BB_Trace_’
+ CONVERT(varchar(20),getdate(),112)+’_’+@str_manip;
select @tracefile –\\HSITEST\backups\HSITEST-MSSQLSERVER2012_BB_Trace_20150918_1327_15min
exec sp_trace_create @TraceID OUTPUT, @options = 2, @tracefile= @tracefile, @maxfilesize = @bigintnum, @stoptime=@stop;
— Set the events
— Event 17 – Existing Connections
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 17, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 14 – Connection Login
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 14, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 15 – Connection Logout
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 15, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 98 – Showplan Statistics Profile
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 98, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 12 –> SQL: BatchCompleted
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 12, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 13 –> SQL: BatchStarting
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 13, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 40 –> SP: StmtStarting
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 40, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 41 –> SP: StmtCompleted
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 41, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 42 –> SP: Starting
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 42, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 43 –> SP: Completed
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 43, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 11 –> RPC: Starting
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 11, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Event 10 –> RPC: Completed
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 1, @on — Column 1 –> TextData
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 2, @on — Column 1 –> BinaryData
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 3, @on — Column 3 –> DatabaseID
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 6, @on — Column 6 –> NTUserName
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 8, @on — Column 8 –> HostName
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 9, @on — Column 9 –> ClientProcessID
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 10, @on — Column 10 –> ApplicationName
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 11, @on — Column 11 –> LoginName
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 12, @on — Column 12 –> SPID
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 14, @on — Column 14 –> StartTime
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 15, @on — Column 15 –> EndTime
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 13, @on — Column 13 –> Duration
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 16, @on — Column 16 –> Reads
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 17, @on — Column 17 –> Writes
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 18, @on — Column 18 –> CPU
exec sp_trace_setevent @TraceID, 10, /*columnid*/ 51, @on — Column 51 –> EventSequence
— Set the Filters
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Server Profiler’
set @bigintnum = 3072
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintnum
— set @bigintfilter = 200
–sp_trace_setfilter (@TraceID, 14, 0, 4, N’200′)
–Turn on the trace!!!
exec sp_trace_setstatus @TraceID, 1
END
GO
Erin,
Did you ever write the article on comparing 2 traces? I’m having trouble reading 2016 XE files but as soon as I get that resolved I wanted to do a replay with OStress and do a comparison on the results from the 2 XE captures using RML Utilites.
Of yet I can find no references to how to compare traces much less compare two XE traces.
Appreciate all your work. Spend a lot of time with your and Jonathan’s Pluralsight courses.
Guy-
You can compare two .trc files in Read Trace, but I don’t know if they have added the capability of comparing two .xel files. I don’t believe they did. You can use the Database Experimentation Assistant (DEA) for comparison – but I believe you have to capture the files via DEA.
The documentation for RML Utilities is pretty good (it’s in C:\Program Files\Microsoft Corporation\RMLUtils\Help), to compare two files you end up processing each file into a different database, then in the UI you can select the two databases and it will do a comparison. I *think* you can also convert an .xel file to a .trc, but I haven’t tried that in a while.
E