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

9 thoughts on “Extended Events Misperceptions: Profiler is Easier, Part 2

  • Good stuff, Grant. And thanks for showing me on twitter that we can multi-select XE events to set up filtering. I do really appreciate the comparison of scripted SQL trace vs XE here. The sp_trace_setevent calls have always been ugly. Keep hammering us on XE! We’ll all get there if you keep up these efforts. šŸ™‚

  • Chris

    I think the biggest misconception about extended events is the notion that preaching something as “BETTER” in big marketing letters makes it an instant replacement for use cases already met with profiler. I don’t need profiler to be better. My most common use case is showing devs where their code isn’t passing correct parameters. Profiler meets that requirement in seconds flat so I can get back to sql dev. I get it – MVPs are preaching that there’s a new, better wheel out there. I’m not shopping for a new wheel. My wheel works fine and any investment into changing it would be a waste of time and resources.

    The most effective way to increase extended events adoption would be to add a profiler mode to the xe interface that is indistinguishable from profiler’s gui – including results. Or quietly retrofit profiler to use xe under the hood.

    • Oh, you are not going to like next week’s post.

      So, they have a perfect match for Profiler built right into the SSMS GUI. Here’s last week’s post showing how that works and that, in fact, it’s faster & easier than Profiler, while providing exactly the same information, in every way, other than being in Profiler and using Trace, identical:https://www.scarydba.com/2020/01/20/extended-events-misperceptions-profiler-is-easier/

      So that’s there. No additional work necessary.

      I used to say, “if you’re comfortable in Profiler, stay there” at least for the things that you can do in Profiler. Since everyone I know is using functionality from 2012 or greater, the only way to monitor that functionality is through Extended Events. But, let’s talk just query tuning.

      Profiler and Trace put a much more substantial load on the system than does Extended Events for the exact same metrics. I’m now arguing (and it’s in the rant, uh, I mean blog post, next week), it’s irresponsible to continue to inflict potential harm (and sometimes very real harm) on systems when a viable, enhanced, better alternative exists.

      Oh man, this is one is going to be like stepping on a hornets nest I think.

      Your wheel is less efficient and more harmful. It’s a bad wheel. This isn’t about new and shiny. We’re talking technology that is 12 years old replacing technology that is 22 years old. You’re not running SQL Server 7 any more, right? Why are you still trying to use it’s tools?

      • Chris

        I don’t use Profiler for performance tuning. Equally ancient tools like execution plan diagrams and statistics io are specifically designed for that. I use profiler almost exclusively as a troubleshooting and debugging tool.

        With a few clicks in Profiler I can see whether a backend or frontend change just broke QA: capture rpc_completed and Exception events where Error0. Boom. Done. How can I get “exactly the same information” in XE that quickly while devs are standing over my shoulder? That Error field I’m filtering for in the trace is not even implemented for rpc_completed in XE. In addition, the exception_ring_buffer_recorded does not display the error message; the trace says “Divide by zero encountered” while the XE session says “NULL.” Not only can I not quickly set the XE session up, I can’t get the data in a form I can use. This is the simplest trace I run, and XE already has two issues with it.

        XE might be super neat awesome sauce for performance tuning, but I’ve found it to be awkward and ugly for troubleshooting and debugging. And maybe that’s why Microsoft dutifully ships Profiler with each new version of SSMS. Some voice in that MS boardroom said, “What if they’re not using profiler to performance tune on production?” Apparently that voice wasn’t compelling enough to stop them from installing it into the “Performance Tools” folder.

        • Well, take a look at the deprecation list. Nothing comes off the product now. Nothing. By design. Why? Azure. Because they upgrade people automatically in Azure, they have to maintain the entire code base, forever (or at least a very long time). If they started removing things then Azure would burn and the cash cow would die. It’s way more that than any love for Profiler.

          However, this is an excellent point. I always talk about how XE is way more than just about performance. I stick with that the most because that’s what most people point to. I’m on the road for the next week, but when I get back, I’m going to look into your issues, because, yes, I think XE is a better troubleshooting tool. If it can’t do something, I want to see that and see if there’s a way around it. I’ll get back to you when I can. Plus, this will make a good blog post, good or bad.

          • Chris

            All practical issues aside, I suspect the primary reason XE adoption has been so reluctant is much more fundamental: xml. Database is all fun and games until someone starts shredding xml, and it happens early in most XE demonstrations. Profiler is not THAT much simpler than XE, but I perceive Profiler as relational – the database framework that replaced the old hierarchical databases 50+ years ago. Working with hierarchical data in an evil robot language just feels wrong.

            Unlike XE, I adopted querystore nearly instantaneously and use it every day. Querystore feels relational – I can query its tables any way I like. Sure, plans are stored as xml, but hidden inside a relational structure and displayed graphically so relational thinkers don’t have to dirty their hands (or minds) shredding it.

            Note: After typing that I reread your article on XE’s relative ease-of-use and realized that shredding xml is conspicuously absent. Maybe you had a hunch that it’s the xml in XE that’s unnatural for many db folks?

          • Evidently we’re at a nesting limit, speaking of XML.

            So, no. I don’t hide the XML. However, depending on what you’re doing, you don’t need to touch the XML. I very seldom get into it. However, yes, it’s the one, giant, PITA about Extended Events.

        • Chris, can you do me a favor. Post or share the trace definition that you’re doing to capture this information. I’m trying to replicate it and I’m not sure I’m getting what you want. Send it over and I’ll see what I can do.

          grant -at- scarydba -dot- com

          That’s a good email address. Thanks!

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.