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