Overview and XE Profiler
Using Profiler and the old tracing infrastructure has been deprecated since SQL Server 2012. This includes the server-side SQL Trace functionality. A frequent usage of Profiler has been do trace something live. Scenarios such as:
"I wonder what happens when I press this button"
"I want to see resource usage and performance for these SQL queries"
Extended Events (XE) is a great trace engine, and the GUI in SQL Server Management Studio is also pretty good, provided that
- You are looking at a saved trace
- You trace to a file instead of the ring buffer (SSMS just show you the raw XML for the Ring Buffer Target)
What I have been missing, though, is the ability to quickly and easily kick off the types of short term live traces I mentioned above. But now, as of SSMS 17.3 we have the XE Profiler. This is a Profiler-like experience built-in to SSMS.
Double-click any of the two above 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:
| XE Profile | Event session name |
| Standard | QuickSessionStandard |
| TSQL | QuickSessionTSQL |
Above corresponds to the Profiler templates with the same names.
Customizing XE Profiler
The really cool thing is that you can customize these sessions. I, for once, frequently want to see resource usage for queries. So 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 that Quick Session.
The columns to be shown
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 are shown.
- Right-click on the column header that you don't want to see and remove that column.
- To add a field as a column, you have two options:
- 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".
SQL server Management Studio 17.3 and later.
XE Profiler in SSMS work against SQL Server 2012 and later.
Trace definition: Resource usage for queries, per batch
Since I often want to capture performance and resource usage for SQL command, I drop and re-create the QuickSessionTSQL Event Session. And, again, if you don't like it, just delete it and SSMS will re-create the "factory default" for you.
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)
WHERE
([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
AND [sqlserver].[client_app_name]<>N'SQLServerCEIP')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)
WHERE
([sqlserver].[not_equal_i_sql_unicode_string]([batch_text],N'SELECT @@SPID;')
AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
AND [sqlserver].[client_app_name]<>N'SQLServerCEIP'))
WITH (MAX_MEMORY=8192 KB, MAX_DISPATCH_LATENCY=5 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=PER_CPU)