THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

SSMS 17.3 has Profiler built-in

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:

  • Familiarity

  • 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.

XE Profiler

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) 
GO

 

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”.

 Choose Columns

 Choose COlumns 2

 

 

I might over time build some more "alternate" templates for XE Profiler, which I will add here.

Published Tuesday, October 10, 2017 10:50 AM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

pmbAustin said:

I don't see this node... I just updated to 17.3 from 17.2, connected to SQL Server, and don't see this node anywhere. Is there something I have to do to enable it?

October 11, 2017 10:28 AM
 

TiborKaraszi said:

Verify the version of SSMS (Help, About). Also, perhaps you are connected to an old version of SQL server?

October 12, 2017 12:32 AM
 

Phippsey said:

Cool stuff. Certainly helps DBAs transition to XE. XE is not that scary ... really.

October 15, 2017 1:10 PM
 

John said:

Extended events are horrific.  Let's be real here.

October 18, 2017 1:55 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement