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

Share this: Twitter

Facebook

Reddit

LinkedIn

Tumblr

WhatsApp

Pocket

Email

