Extended Events Misperceptions: Profiler is Easier, Part 2

I wrote a short blog post about the misperception that Profiler was easier than Extended Events when it came to the core concept of “click, connect, BOOM, too much data”. Go read it if you like, but I don’t think it’s actually an effective argument for how much easier Extended Events is than Profiler. Here, we’re going to drill down on that concept in a real way.

Let’s start with a little clarification. I’m going to be a little lazy with my language. Trace is a scripted capture of events on a server. Profiler is a GUI for consuming a Trace, either live or from a file, and for creating Trace events. However, almost everyone refers to ‘Profiler’ when they mean either Trace or Profiler. I may do the same occasionally.

What I’m going to talk about this time is scripting.

The Test

I want to keep things simple so that 1) they’re easy for me and 2) it’s easy to explain what’s going on and 3) I don’t edge into new behaviors where extended events has a clear advantage (because none of the new behavior in SQL Server is supported through Trace). So, we’re going to create the simplest event capture with both tools. We’ll get batch completions. Store them to a file. Filter on the database name.

Profiler/Trace

I’m telling you right now, I can’t write T-SQL for Trace events. So, I’m going to do what I’ve always done and what I bet the majority of you do. I’m going to use the Profiler GUI to generate a Trace.

First, I have to open Profiler and get connected to a server. Since we have to actually fire off this Trace in order to capture the script, I’m doing this against a dev machine and not my production instance. Once that’s decided on, the first page is pretty straight forward:

I’ve given it a name and a place to save the files. So, let’s go get the event. That’s on the second tab:

Because I have “Show all columns” selected, when I pick SQLBatchCompleted, I get the associated columns automatically. If I had anything else clicked there, I’d see radically different behavior.

Next, I want to add a filter, so I click on the “Column filters…” button:

Here I find the DatabaseName column and I use the “Like” command to supply my string: ‘AdventureWorks’.

Click OK. Then click on run. The Trace is created on the server to which I am connected and the Profiler gui begins to capture data. So, I now hit the stop button. With that done, I can click on File, Export, Script Trace Definition, For SQL Server 2005-2019. I save the file and this is my final outcome:

/****************************************************/
/* Created by: SQL Server 2019 Profiler          */
/* Date: 01/21/2020  01:47:55 PM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 4, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 41, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 49, @on
exec sp_trace_setevent @TraceID, 12, 50, @on
exec sp_trace_setevent @TraceID, 12, 51, @on
exec sp_trace_setevent @TraceID, 12, 60, @on
exec sp_trace_setevent @TraceID, 12, 64, @on
exec sp_trace_setevent @TraceID, 12, 66, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

I’m sure this is familiar to most of you. I can edit this code directly, so, if, for example, I wanted to trim the columns being captured, I just have to go look up what each column value is and then find the associated number and remove that. I can also add other events to the script in the same fashion. Find the event id and find the associated columns.

However, if I don’t, in some way, edit this file, there’s no way for me to know exactly what it is I’ve created.

Oh, and don’t forget to go back and edit ‘InsertFileNameHere’ because the file I provided when I first created the event isn’t what’s being captured with the script unless I fix it. That’s OK because our plan is to run this on production anyway.

If I did any of this wrong, please let me know.

Extended Events

Never use the Wizard. It’s a bloody waste of time.

So, I’m going to do this right on the production instance. Open the New Session window and type in the Session name:

Got to the Events page. Type ‘sql_batch’ and you should see sql_batch_completed. We’ll just add that to the selected list:

I’m honestly not crazy about this next step, but we have “Configure” the events to filter them, so we click the Configure button and fill in the appropriate information. It’s not that different from the Column Filter window. I just find it a little weird to manipulate. However:

Finally, click to the Data Storage page and fill out that information:

Click OK and we’re most of the way there. Now, nothing is running. All I’ve done is add this Extended Events session to the list on the server:

Righ click the session and select “Script session as”, CREATE Session, Query Editor Window and we get the following:

CREATE EVENT SESSION [BatchComplete]
ON SERVER
    ADD EVENT sqlserver.sql_batch_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks'))
    ADD TARGET package0.event_file
    (SET filename = N'BatchCompleted')
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

The WITH clauses are not needed because they’re all the defaults. I could leave them off the script (I do later in a second example).

Now, look at that script. What’s it do?

Compare it to the script above from Profiler. What does the Profiler script do?

Which one do you think is easier to work with? The number of steps to create a script from the GUI are fewer with Extended Events (slightly). Also, it doesn’t involve creating and starting a Trace and then stopping it immediately in order to capture the script. I’ve always hated that.

Just to prove to myself that I could do it, before I set up the test, I typed this in T-SQL:

CREATE EVENT SESSION BatchComplete
ON SERVER
ADD EVENT sql_batch_completed
(WHERE sqlserver.database_name = 'AdventureWorks');

It worked. I’m 90% complete. I’d have to look up the syntax for adding the file. However, it’s that easy to get going with Extended Events in T-SQL.

Conclusion

Again, I get it. Familiarity equates to ease. However, any objective observer is going to look at the code we arrive at and tell you that, of course, Extended Events is easier. Want to know what you have to do in Profiler to add rpc_completed to the Trace? Me neither. Want to know how we could modify the Session for Extended Events:

ALTER EVENT SESSION BatchComplete
ON SERVER
ADD EVENT rpc_completed
(WHERE sqlserver.database_name = N'AdventureWorks')

Done.

I know which one is easier in my book.

Understand, Extended Events is much more than a simple replacement for Profiler/Trace. Extended Events is better and does more. Also, once you get into it and establish a new comfort, Extended Events is in fact easier.

---

I love teaching about this stuff. Watching the light go on inside people’s heads is something I live for. I’m putting on an all day seminar that covers Extended Events in detail as well as Query Store, Execution Plans and more at DevIntersection in April. Use the code FRITCHEY to save $50 on your registration:

DevIntersection: April 7-9

On the other hand, if you want to talk DevOps, I have an all day class at Bits:

SQLBits: April 1