Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Profiler equivalent templates for Extended Events Sessions in SQL Server 2016


By:   |   Last Updated: 2016-10-03   |   Comments   |   Related Tips: More > 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.

SQL Server Profiler Templates

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.

SQL Server Management Studio Version

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.

SQL Server Extended Events Templates

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


Query to compare Profiler vs. Extended Events

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.

T-SQL_Duration Template in SQL Server Profiler and Extended Events

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

SQL Server Profiler Data for Session 52

Extended Event Session Output

SQL Server Extended Events Data for Session 52

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


Last Updated: 2016-10-03


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools