XE Profiler – the new feature available in SSMS v17.3

SSMS v17.3At the end of September during Microsoft Ignite event Microsoft announced new SQL Server 2017. On this conference, Bob Ward (b|t) a Principal Architect for the Microsoft Database Systems Group delivered a great session titled: Experience Microsoft SQL Server 2017: The fast and the furious. In 70 minutes he led us into the amazing world of SQL Server 2017 features. During this presentation he used a preview version of SQL Server Management Studio v17.3 and did a quick demo of XE Profiler – one of the new features (youtube).

Few days ago I had a great opportunity to play with SSMS v17.3 (build 14.0.17199.0) and I want to describe you how the XE Profiler works. 

XE Profiler

XE Profiler - Object ExplorerIn Object Explorer we have new XE Profiler position which contains two session templates: Standard and TSQL. Double clicking on one of them will create XEvent session, start it, and open a Live Data view in SQL Server Management Studio. That allows us to immediately start new session and watch SQL Server workload without loosing any time for configuration. That is a great shortcut especially if we need quickly troubleshoot customer’s issue.

XE Profiler - Live Data viewYou need to be careful because in this version of SSMS closing the Live Data view tab doesn’t stop nor delete created session. If XEvent session already exists it doesn’t create new one.

Two new Extended Event sessions

XE profiler - Object Explorer - XE sessionAs for now, by default we got two predefined options: Standard and TSQL. There is no possibility to add extra ones by the user. These options are hard-coded in ObjectExplorer.dll. For each one the dedicated XEvent session is created accordingly:

  • QuickSessionStandard
  • QuickSessionTSQL

QuickSessionStandard is created as replacement for the ‘Standard’ template in Profiler. It’s generic starting point for creating a trace. Captures all stored procedures and Transact-SQL batches that are run. Use it to monitor general database server activity.

It collects following events:

  • attention
  • exisiting_connection
  • login
  • logout
  • rpc_completed
  • sql_batch_completed
  • sql_batch_starting

QuickSessionTSQL is created as replacement for the ‘TSQL’ template in Profiler. It captures all Transact-SQL statements that are submitted to SQL Server by clients and the time issued. Use it to debug client applications.

It collects following events:

  • exisiting_connection
  • login
  • logout
  • rpc_starting
  • sql_batch_starting

These XEvent sessions are created without any target.

XE profiler - session - no targetDefinitions of both sessions are deployed as regular XEvent session templates. We can find them together with other templates:

XE profiler - template files

That means we can also use them from the New Session Wizard:

XE profiler - new session wizard

Default columns in Live Data view

XE Profiler functionality also provides us one more feature. Usually when we open Live Data view by default we get just two columns: name and timestamp.

XE profiler - default columnsHowever when we open this view for QuickSessionStandard or QuickSessionTSQL we get something new. For these sessions, by default, we got columns similar to these from SQL Profiler.

XE profiler - TSQL columnsXE profiler - Standard columns

Session name matters

In this preview version of SQL Server Management Studio v17.3 many things depends on hard-coded event names. What happens when we create our own custom XEvent session with one of these names?

  • Depending on the name we will get extra columns, even when there is no data for them.
  • Using XE Profiler we will not override already created sessions. It will open Live Data view for existing session with according name.
Summary

XE Profiler looks promising and can be really a great feature. We can use it with no issues on any version of SQL Server which supports extended events – not only with newest SQL Server 2017. I tested it with SQL Server 2014 and it was working well. Currently, lack of configuration of new templates, and logic based on hard-coded names is the biggest concern and discomfort for the user. However Microsoft didn’t officially release yet this version of SQL Server Management Studio, so it’s hard to say what will be the final feature functionality.

[EDIT] Actually SSMS v17.3 (build 14.0.17199.0) was officially released today (2017-10-10).

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

3 thoughts on “XE Profiler – the new feature available in SSMS v17.3”

  1. Looks like a promising new feature – Our dba has locked down the server so need to ask him to change permissions as it falls over without sysadmin permissions.
    Error message: Create session named ‘QuickSessionTSQL’ (Error) Messages User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247) – Tried searching for any information on which permissions are required for this feature to work for non-system admins but not found anything yet – any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *