By: Rajendra Gupta | Comments | Related: > Extended Events
Problem
In previous tips SQL Server 2016 extended events detailed backup information, SQL Server restore steps with extended events and SQL Server 2016 Telemetry Extended Event Session we have seen how useful Extended Events are in SQL Server 2016 compared to Profiler. I want to do more with Extended Events, but the predefined templates in SQL Server Profiler make it so easy to start a trace. Are there any predefined templates for Extended Events like the ones in Profiler?
Solution
Extended Events are quite useful in tracking performance issues, workload analysis and more. Extended Events puts less overhead on your server compared to Profiler and Profiler may be deprecated in future releases of SQL Server, so now is the time to start learning more about Extended Events.
As you may know, in Profiler there are some useful predefined templates which makes it easy to run a trace.
These templates are quite easy to use for running a trace with Profiler. If we want to use Extended Events we need to manually create an Extended Events session with the events, but this has changed with SQL Server Management Studio (SSMS) release 13.0.15700.28.
As you know, SQL Server Management Studio is no longer part of the standard installation for SQL Server 2016 and it uses a web installer that can be downloaded from the internet. This is generally available and does not require a SQL Server license to install and use. With this change, it is very easy to maintain updates in SSMS and keep updated with new features. Recently Microsoft released SQL Server Management Studio 13.0.15700.28. With this release Microsoft has provided new Extended Events templates that match the functionality of SQL Server Profiler templates. It can be downloaded from this link: Download SQL Server Management Studio 16.3 release. It's quite easy to install and after installation we can check the version in SSMS by clicking on Help > About as shown below.
Now to check if the Extended Events templates are equivalent to Profiler, go to Management > Extended Events > Session > New Session. In the template section, we can see the template group named "Profiler Equivalents" as shown below.
Here is a breakdown of the templates provided for Extended Events:
- SP_Counts: This template matches the 'SP_Counts' template in Profiler. Captures stored procedure execution behavior over time.
- Standard: This template matches the 'Standard' template in Profiler. Generic starting point for creating a trace. Captures all stored procedures and Transact-SQL batches that are run. Use to monitor general database server activity.
- TSQL: This template matches the 'TSQL' template in Profiler. Captures all Transact-SQL statements that are submitted to SQL Server by clients and the time issued. Use to debug client applications.
- TSQL_SPs: This template matches the 'TSQL_SPs' template in Profiler. Captures detailed information about all executing stored procedures. Use to analyze the component steps of stored procedures. Add the sql_statement_recompile event if you suspect that procedures are being recompiled.
- TSQL_Duration: This template matches the 'TSQL_Duration' template in Profiler. Captures all Transact-SQL statements submitted to SQL Server by clients and their execution time (in microseconds). Use to identify slow queries.
- TSQL_Locks: This template matches the 'TSQL_Locks' template in Profiler. Captures all of the Transact-SQL statements that are submitted to SQL Server by clients along with exceptional lock events. Use to troubleshoot deadlocks, lock time-out, and lock escalation events.
- TSQL_Replay: This template matches the 'TSQL_Replay' template in Profiler. Use to perform iterative tuning, such as benchmark testing.
- Tuning: This template matches the 'Tuning' template in Profiler. Captures information about stored procedures and Transact-SQL batch execution.
Here is a comparison of Profiler versus Extended Events for each template to see which events are captured.
| Template Name | Profiler Event | Extended event |
|---|---|---|
| SP_Counts | SP:Starting | module_start |
| Standard | Audit Login Audit Logout ExistingConnection RPC:Completed SQL:BatchCompleted SQL:BatchStarting |
Login Logout existing connection rpc completed sql_batch_completed sql_batch_starting |
| TSQL | Audit Login Audit Logout ExistingConnection RPC:Starting SQL:BatchStarting |
Login Logout existing connection rpc_starting sql_batch_completed sql_batch_starting |
| TSQL_Duration | RPC:Completed SQL:BatchCompleted |
rpc_completed sql_batch_completed |
| TSQL_Grouped | Audit Login Audit Logout ExistingConnection RPC:Starting SQL:BatchStarting |
login logout existing connection rpc_starting sql_batch_completed sql_batch_starting |
| TSQL_Locks | Blocked Process Report SP:StmtCompleted SP:StmtStarting SQL:StmtCompleted SQL:StmtStarting Deadlock Graph Lock:Cancel Lock:Deadlock Lock:Deadlock Chain Lock:Escalation Lock:Timeout (timeout>0) |
blocked_process_reports sp_statement_completed sp_statement_starting sp_statement_completed sp_statement_starting xml_deadlock_report Lock:Cancel Lock:Deadlock Lock:Deadlock Chain Lock:Escalation Lock:Timeout (timeout>0) |
| TSQL_Replay | CursorClose CursorExecute CursorOpen CursorPrepare CursorUnprepare Audit Login Audit Logout Existing Connection RPC Output Parameter RPC:Completed RPC:Starting Exec Prepared SQL Prepare SQL SQL:BatchCompleted SQL:BatchStarting |
attention cursor_close cursor_execute cursor_open cursor_prepare cursor_unprepare exec_prepared_sql existing_connection login logout prepare_sql rpc_completed rpc_starting sql_batch_completed sql_batch_starting |
| Tuning | RPC:Completed SP:StmtCompleted SQL:BatchCompleted |
rpc_completed sp_statement_completed sp_batch_completed |
| TSQL_SPs | Audit Login Audit Logout ExistingConnection RPC:Starting SP:Completed SP:Starting SP:StmtStarting SQL:BatchStarting |
login logout existing_connection module_end module_start rpc_starting sp_statement_starting sp_batch_starting |
To check the equivalent Extended Event with the Profiler event we can use the below query. I compared the two events used in the TSQL_Duration template.
SELECT te.trace_event_id,tc.name AS CategoryName
,te.name AS TraceEventName
, xem.package_name, xem.xe_event_name
FROM sys.trace_xe_event_map xem
RIGHT OUTER JOIN sys.trace_events te
ON te.trace_event_id = xem.trace_event_id
INNER JOIN sys.trace_categories tc
ON te.category_id = tc.category_id
where te.name in
('RPC:Completed',
'SQL:BatchCompleted'
)
Sample Run to Compare Extended Events and Profiler Events
I created similar sessions for both Extended Events and Profiler for the T-SQL_Duration template.
To keep data to a minimum I applied a filter for spid 52 and then started the Profiler trace and the Extended Events session.
Profiler Output
Extended Event Session Output
By default the Profiler template shows limited information, but you can add additional columns. However the Extended Events captures a lot more information by default.
Extended Events are light weight, easy to manage and give multiple options to manage the output target data. With the help of the Profiler equivalent templates it becomes easy to configure sessions to collect data we are familiar with seeing in Profiler.
Take the time to explore these new templates for configuring Extended Events sessions. Also, try them out in first in your test environments prior to troubleshooting production issues.
Next Steps
- Download and explore SQL Server 2016
- Check out SQL Server 2016 Tips
- Read more about Extended events
- Read more about Extended events sessions
- Check out these resources:
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
