At 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.
In 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.
You 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
As 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 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:
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:
These XEvent sessions are created without any target.
Definitions of both sessions are deployed as regular XEvent session templates. We can find them together with other templates:
That means we can also use them from the 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.
However 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.
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.
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).
4 thoughts on “XE Profiler – the new feature available in SSMS v17.3”
Way cool! Some extremely valid points! I appreciate you writing this article plus the rest of the website is very good.
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?
You need to have rights to create XEvent session. According to https://docs.microsoft.com/en-us/sql/t-sql/statements/create-event-session-transact-sql you need to have ALTER ANY EVENT SESSION permission.
Seems it’s not enough. To use XE Profiler you need to have VIEW SERVER STATE permission as well.