As DBAs, we often need to trace activity on our SQL Server instances. We might do this for on-demand tasks, such as collecting requests that take an unexpected and extended amount of time to execute. We may also have multiple proactive traces that collect data to check as needed, such as finding specific deadlock events that we need to analyze.
For years, SQL Server DBAs have relied on (and loved) SQL Server Profiler and Trace to trace SQL Server activity. Then SQL Server 2012 SP1 introduced an Extended Events engine that covered 100% of SQL Server Trace events, and Microsoft encouraged everyone to move from traces to Extended Events. Compared to traces, Extended Events are much more lightweight and even provide more detailed results.
Many SQL Server professionals have resisted the switch from Profiler to Extended Events, and we understand: see “So why do people keep using Trace/Profiler?” at dbatools.io/xevents. When we first started working with Extended Events, we found that creating new Extended Events could be pretty challenging when using SQL Server Management Studio (SSMS). Eventually, however, we realized that leveraging T-SQL scripts and automation made our experiences with Extended Events a whole lot better.