We all know that Profiler and its background functionality SQL Trace has been deprecated since 2012. Right?
And we have all switched to using Extended Events (XE), a long time ago, right?
No, the reality is that many of us still uses the old tracing infrastructure. This can be for various reasons, like:
- Existing templates and stuff
- Ease of use
- Lack of functionality in the replacement
I have been gradually switching over to XE over the last few years. And I have been gradually becoming to like it more and more. It is a learning curve, for sure, but it is so superior and even the GUI is in my opinion more powerful if you are a) looking at a saved trace and b) use a trace file instead of the ring buffer. Just the ability to be able to sort, group and aggregate with a few clicks is for me great.
But enough about the general stuff, I’m actually going somewhere with this.
One functionally for which I haven’t been using XE much is to look at live data. I’m talking about scenarios such a “I wonder what happen when you press this button?”. Or, something I very frequently do is to look at the resource usage for SQL command from a trace instead of using commands such as SET STATISTICS IO ON. For these type of tasks, I often just fire up Profiler and use my own Profiler templates.
A very promising new functionality in SQL Server Manager Studio (SSMS) 17.3 is the new XE Profiler. This is a Profiler-like experience built-in to SSMS.
Just double-click any of the two entries and you have a live trace window. This is built on the SSMS XE “Watch Live Data” functionality. There’s actually no magic going on here. What happens is that SSMS creates a trace session if it doesn’t exist, starts that session and opens a live data window for that trace session. There’s no target for the trace, live data doesn’t require a target. The event sessions that will be created are named:
- Standard QuickSessionStandard
- TSQL QuickSessionTSQL
The above corresponds to the Profiler templates with the same names. The really cool thing is that you can customize these sessions. I, for once, frequently want to see resource usage for queries. I modify QuickSessionTSQL to grab the completed events instead of the started events. Don’t worry if you mess it up – just delete the trace session and let SSMS re-create it for you next time you open a that Quick Session!
Here’s an example
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'QuickSessionTSQL') DROP EVENT SESSION QuickSessionTSQL ON SERVER GO CREATE EVENT SESSION QuickSessionTSQL ON SERVER ADD EVENT sqlserver.rpc_completed( ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)), ADD EVENT sqlserver.sql_batch_completed( ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)) WITH (MAX_MEMORY=8192 KB, MAX_DISPATCH_LATENCY=5 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=PER_CPU)
When you Launch Session then by default it will show the columns that was relevant for the session definitions that shipped with SSMS. What you might want to do is to re-configure the columns that is shown.
To remove a column: Right-click on the column header that you don’t want to see and remove that column.
To add a column in the display: You can in the lower window, the “Details” section, right-click a field and “Show Column in Table”. Or you can in the column header in the top column header section right-click and “Choose Columns”.
I might over time build some more “alternate” templates for XE Profiler, which I will add here.