Free SQL Help for Devs, DBAs, and the Curious

SQL Profiler has been long considered the best tool for monitoring and troubleshooting SQL Server. Its primary use has been to capture and analyze SQL Server events such as batch statements, stored procedures, and login activity. However, with the release of SQL Server 2012, a new feature was introduced – Extended Events. Extended Events is a lightweight performance monitoring system that uses very few performance resources. In this post, we will compare SQL Profiler and Extended Events, especially in the context of SQL 2012 migration.

SQL Profiler, while powerful, has a reputation for being resource-intensive. It could potentially slow down the performance of your server, especially when running heavy traces. On the other hand, Extended Events is designed to be lightweight and cause minimal impact on system performance. This makes Extended Events a better choice for live environment monitoring.

Extended Events also has a wider range of events it can monitor. While SQL Profiler can capture about 180 events, Extended Events can capture over 800, giving you a much more detailed view of what’s happening in your SQL Server. However, because Extended Events is more complex and has a steeper learning curve, many DBAs still prefer SQL Profiler.

-- Example of a SQL Profiler trace 
EXEC sp_trace_create @options = 2, @tracefile = N'C:\Trace\Trace.trc'
EXEC sp_trace_setevent @eventid = 12, @columnid = 34, @on = 1
EXEC sp_trace_setfilter @columnid = 34, @logical_operator = 0, @comparison_operator = 0, @value = N'Admin'
EXEC sp_trace_setstatus @status = 1

In SQL Server 2012, Microsoft has made Extended Events the main performance monitoring tool, with a GUI added to SQL Server Management Studio in SQL Server 2014 for easier use. If you are migrating to SQL Server 2012 or later versions, it is recommended to start using Extended Events, as SQL Profiler is being deprecated. However, the transition from Profiler to Extended Events might be a bit challenging especially for those who have been using Profiler for a long time.

-- Example of an Extended Event session
CREATE EVENT SESSION [Long Queries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
( WHERE ([duration]>(1000)))
ADD TARGET package0.event_file(SET filename=N'LongQueries.xel',max_file_size=(5))
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

Overall, while SQL Profiler has been the go-to tool for SQL Server monitoring, the advent of Extended Events offers a more efficient and detailed monitoring solution. However, it does come with a learning curve. For SQL Server 2012 and later versions, it is recommended to make the transition from SQL Profiler to Extended Events as Profiler is being phased out.

SQL Profiler and Extended Events are not available in other database systems like MySQL 5.6 or DB2 10.5. However, these systems have their own performance monitoring tools. For example, MySQL has Performance Schema and DB2 has Activity Monitor. In Azure SQL, Extended Events is the primary tool for performance monitoring, further underlining the importance of this feature in modern SQL Server environments.

In conclusion, if you are still using SQL Profiler and are planning to migrate to SQL Server 2012 or newer versions, it is a good time to start learning and using Extended Events. It is a powerful, lightweight and comprehensive tool that can help you monitor and troubleshoot your SQL Server better.