SQL Server Extended Events vs Profiler
By Tom Nonmacher
In the world of SQL Server, the decision between using SQL Server Profiler and SQL Server Extended Events is a critical one. While both tools provide profound insights into your database's performance, they differ significantly in their capabilities and usage. In this blog post, we'll compare these two diagnostic tools based on SQL Server 2016 and SQL Server 2017, and provide some examples demonstrating their differences.
SQL Server Profiler, an older tool, has been a staple in the SQL Server family since its inception. It provides a graphical interface that allows administrators to create and manage traces and analyze and replay trace results. It's a useful tool for real-time monitoring and troubleshooting. However, Profiler consumes a significant amount of system resources, which may affect your SQL Server's performance. Here's a sample T-SQL script that can be used to create a trace using SQL Server Profiler:
-- Create a Trace
EXEC sp_trace_create @traceid = 1 OUTPUT , @options = 0 ,
@tracefile = N'c:\Traces\TraceOutput.trc'
-- Set the Events
EXEC sp_trace_setevent @traceid = 1 , @eventid = 12 , @columnid = 1 , @on = 1
On the other hand, SQL Server Extended Events is a lightweight performance monitoring system that uses very few system resources. It was introduced in SQL Server 2008, and with each new SQL Server version, it has been enhanced and given more capabilities. Extended Events offer a highly scalable and configurable data collection method, and they are the future direction for SQL Server's performance troubleshooting. Here's an example of how to create an Extended Events session in T-SQL:
-- Create an Extended Events Session
CREATE EVENT SESSION [QueryMonitoring] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1))
ADD TARGET package0.event_file(SET filename=N'c:\Traces\QueryMonitoring.xel', max_file_size=(5), max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF)
GO
In SQL Server 2016 and SQL Server 2017, we see a major shift towards Extended Events. Many new features are only supported by Extended Events, and Profiler is considered deprecated. Therefore, it is recommended to start using Extended Events if you are still relying on SQL Server Profiler. However, note that Extended Events does not provide a replay feature, which Profiler does. So, if your troubleshooting heavily depends on that feature, you might want to stick with Profiler for now.
It is worth noting that MySQL 5.7 and DB2 11.1, as well as Azure SQL, provide their own diagnostic tools and performance schema that serve similar purposes. However, the principles and methodologies of SQL Server Extended Events still apply. Understanding the concept of events and how to capture and analyze them is essential for any database administrator, regardless of the technology stack.
In conclusion, while SQL Server Profiler has served us well, it is clear that the future lies with SQL Server Extended Events. It is more efficient, flexible, and customizable than SQL Server Profiler, and it is being embraced by Microsoft in its newer versions of SQL Server. As database administrators, it is crucial that we stay up-to-date with these changes and adapt our practices accordingly.