The XEvent Profiler is a SQL Server Management Studio (SSMS) feature that displays a live viewer window of extended events. XEvent Profiler is directly integrated into SSMS and is built on top of the scalable Extended Events technology in the SQL engine.
What is xevent profiler in SQL Server?
Although SQL Server Extended Events feature was introduced the first time in SQL Server 2008, as a lightweight performance monitoring feature. XEvent Profiler was launched with SQL Server Management Studio 17.3 which provides a quick and customizable live view of the Extended Events.
What version of SSMS does Xe profiler support?
XEvent Profiler should work with any SQL Server version that has the Extended Events feature. Although, admittedly, I only tested it on 2016 and 2017. The important part is that you will need SSMS v17.3 or higher, download it here and upgrade now. In SSMS’s object explorer there is now an XE Profiler node, at the bottom [1].
What are xevents in SQL Server?
Extended Events (aka - XEvents) were launched in SQL Server 2008 as a replacement for a trace. With each new SQL Server version, there are many enhancements to the Extended Events. Microsoft planned for the depreciation of SQL Profiler to be replaced with Extended Events which provides a customizable definitions, views and events.
How to use xevent template in SSMS?
We can also use these templates from the New Session Wizard under the Extended Events node in SSMS. Just right click on the session and select New Session Wizard. To use XEvent profiler, double-click on the TSQL XEvent Profiler template. For example, if I click on the QuickSessionStandard session, it quickly opens up the live data window.
See more
How to access XEvent Profiler?
To access the XEvent Profiler, connect to the SQL Server instance using SQL Server Management Studio v17.3.
What is XEvents in SQL Server?
Extended Events (aka - XEvents) were launched in SQL Server 2008 as a replacement for a trace. With each new SQL Server version, there are many enhancements to the Extended Events. Microsoft planned for the depreciation of SQL Profiler to be replaced with Extended Events which provides a customizable definitions, views and events. The problem was that Extended Events lacks a quick view capability that is available in SQL Profiler, therefore many DBAs and developers still prefer using SQL Profiler over Extended Events.
What is SQL Profiler?
When troubleshooting SQL Server, we have always used SQL Profiler which is a graphical tool to capture events occurring in your SQL Server instance by running a SQL Server trace. We could then easily identify long running queries, monitor T-SQL statements, identify slow running queries, analyze the SQL workload, etc. SQL Profiler is has been in use since SQL Server 7.0.
Why are extended events important?
Extended Events play an important role for troubleshooting SQL Server issues. They were introduced in SQL Server 2008, however there was not a GUI interface available to make it easy to use Extended Events and therefore DBAs had to write complex T-SQL statements to gather the information they needed. In SQL Server 2012, SQL Server Management Studio (SSMS) included a GUI component to work with Extended Events and viewing event-related data. Now in SQL Server Management Studio v17.3, XEvent Profiler has been introduced and we will explore how to use XEvent Profiler in this tip.
What is QuickSessionStandard?
QuickSessionStandard is created as a replacement for the ‘Standard’ template in Profiler. It contains generic Extended Events, so it can be a starting point. It captures all stored procedures and Transact-SQL batches that are being executed. We can use it to monitor general database server activity.
How to script out XEvent?
To script out the XEvent session, expand Management > Extended Events > Sessions and right click on the session and select Script Session as > CREATE To > New Query Editor Window.
How to find predefined session templates?
Once a session is configured based one of the templates, we can find the predefined session templates by expanding the Management node to check all Sessions under the Extended Events node as shown below:
How to use XEvent Profiler?
There are two options for XEvent Profiler: Standard and TSQL. To use either one just double-click on the name. Behind the scenes, an internally defined event session is created (if it doesn’t already exist) and started, and the Live Data Viewer immediately opens with focus. Note that after you start the session, it will also appear under Management | Extended Events | Sessions. Assuming you have activity against the server, you should start to have entries show up in the viewer in five seconds or less.
Is TSQL event session lightweight?
Understand that both the Standard and TSQL event sessions are designed to be lightweight. The statement_completed events will fire more frequently than the batch and rpc events, thus there can be more overhead when these events are part of an event session. When using statement events, I highly recommend including additional predicates. As a reminder, the rpc and batch events are only filtering out system queries – there is no other predicate. In general, I also recommend additional predicates for these events, especially for a high-volume workload.
How to use XEvent profiler?
To use XEvent profiler, double-click on the TSQL XEvent Profiler template. For example, if I click on the QuickSessionStandard session, it quickly opens up the live data window.
How to script out XEvent?
To script out the XEvent session, expand Management > Extended Events > Sessions and right click on the session and select Script Session as > CREATE To > New Query Editor Window.
What is SQL Server Extended Events?
Although SQL Server Extended Events feature was introduced the first time in SQL Server 2008, as a lightweight performance monitoring feature. XEvent Profiler was launched with SQL Server Management Studio 17.3 which provides a quick and customizable live view of the Extended Events. One of the main advantages of XEvent Profiler is that it is directly integrated into SSMS and is built on top of the Extended Events technology, where SQL Profiler was not integrated with SSMS and had its own interface.
How to find predefined session templates?
Once a session is configured based one of the templates, we can find the predefined session templates by expanding the Management node to check all Sessions under the Extended Events node as shown below:
What is QuickSessionStandard?
QuickSessionStandard is created as a replacement for the ‘Standard’ template in Profiler. It contains generic Extended Events, so it can be a starting point. It captures all stored procedures and Transact-SQL batches that are being executed. We can use it to monitor general database server activity.
What is XEvent Profiler?
XEvent Profiler is a new feature of SQL Server Management Studio v17.3. I applaud the SQL Server Tools Team for this feature. I feel like it will go a long way towards putting the legacy SQL Profiler to bed.
Where is the XE profiler in SSMS?
In SSMS’s object explorer there is now an XE Profiler node, at the bottom [1]. After it is used on an instance for the first time, the Extended Events sessions are found under Management > Extended Events > Sessions [2]. Unlike typical XE sessions [3], there are no targets defined. Instead, they will use a hidden stream target which instantiates when the Watch Live Data feature starts.
Is TSQL quick session lighter?
The TSQL quick session is even lighter.
Does XEvent Profiler work with SQL Server?
How to use XEvent Profiler. XEvent Profiler is an SSMS feature, not a SQL Server feature. XEvent Profiler should work with any SQL Server version that has the Extended Events feature. Although, admittedly, I only tested it on 2016 and 2017. The important part is that you will need SSMS v17.3 or higher, download it here and upgrade now.
Where is XE profiler in SQL Server?
You can find XE Profiler in the object explorer in SSMS under each of your SQL instances – just below SQL Server Agent:
How to select columns in XE?
In XE it’s much easier. I just right click on one of the column headers in the above view and select “Choose Columns”: