Extended Events was added as a new feature in SQL Server 2008. And it provides a new method for collecting diagnostic data from SQL Server in a way that you could never get detailed information before using the previous features available. SQL Trace in SQL Server 2012 was deprecated as a feature in the product. And deprecated means that it will be removed in some future release of SQL Server. But it remains available in the current release. However, because SQL Trace has been deprecated that makes Extended Events its final replacement. And making sure that you understand how to use Extended Events is going to be critical to being able to support SQL Server in the future.
So what are Extended Events? Well, easily put it’s an advanced event collection infrastructure that was added in SQL Server 2008. And it provides a very flexible implementation for doing complex configuration of event collection. To simplify the diagnostic troubleshooting and problem identification going on in SQL Server. You can do all different kinds of performance tuning that was not possible in any other way.
- Being able to capture stored procedures that exceed the previous execution’s max duration. Or previously seen the maximum amount of CPU or I/O values for a specific object. Or ad-hoc request, even, against SQL Server.
- Being able to identify statement timeouts and attend events. And what caused those actually to occur inside of SQL Server.
- Being able to capture a sample or first five executions of an event Without having that event continue to fire inside of the engine while you are doing your diagnosis or data collection.
- Using the plan_handle and tsql_stack to be able to capture the execution plan. Or the statement text associated with an event firing inside of the engine.
- Capturing session-level wait statistics. So that you know everything that happened in the wait lifecycle of a specific statement or stored procedure execution.
- Being able to examine internal details of how SQL Server works. For example, proportional fill algorithm details. And how it is applied to data files when there are different sizes. Or different amounts of free space within the files.
- Being able to watch page splits occur inside of the engine. So that you can track which objects have the most problems for page splitting. And then be able to make fill-factor adjustments for the indexes. To allow for additional free space in the leaf level. To reduce the amount of page splits that are occurring.
Figure 1 Extended Events Architecture
As shown in figure 1, a high-level overview of Extended Events architecture includes the Extended Events engine and then when modules get loaded, they’re going to register their packages and metadata inside of the engine providing information to the engine about what events or objects are going to be exposed by the individual module. And the metadata is going to include information about events, actions, types, maps, targets, and predicates. The Extended Events engine also contains the list of Event Sessions that have been created on the server, and if those sessions are running, it will maintain the memory buffers and handle the dispatching of memory buffers to the targets for the Event Session as well. And the way it does that is through a separate pool of workers which is the dispatcher pool, and then dispatchers will individually pick-up memory buffers and then buffed them out to the targets for being able to be consumed so that you can make use of the data that is being collected internally in the engine.
The Extended Events engine gives the operational environment a lot of Extended Events inside of SQL Server, and it functions as a server to the rest of the modules that get loaded inside of the SQL Server process. It also manages all of the memory buffers for any Active Event Sessions that are running inside of the engine currently,and it manages a separate pool of worker threads called the dispatcher pool that is used for dispatching events from the memory buffers to the Event Session target for each of the defined Event Sessions whenever the memory buffers are full or when the max dispatch latency for the events have been exceeded based on the Event Session configuration options.
An Extended Events; an Event Session is similar to what a trace was for SQL Trace. It’s a functional boundary for the configuration of a specific point of data collection that you have defined on the server,and it has the configuration of all the events associated with the session, any of their configured actions for adding data when the event gets collected, or predicates for controlling how the event is actually going to fire inside the engine as well as the targets that are being defined to consume the event data and possibly do aggregation on the fly, or just store the raw data available for longer-term retention and data consumption.
When you start an Event Session in Extended Events, it will have a portion of memory that is assigned to it as buffer space, and these memory buffers are used for storing the data being generated by the events before they get dispatched out to the targets by the dispatchers.
Figure 2 Event Execution Life Cycle
As shown in figure 2, the event execution life cycle in Extended Events is very different from that of a trace event inside of SQL Trace. When a code point for an event has been encountered inside of the SQL Server code during execution, it goes into a pre-collect phase and is enable check inside of the Extended Events engine to find out if this specific event is enabled on an active session that is running on the SQL Server. If it passes the is enable check, it goes into a collection mode where the event actually collects all the event data for the versioned schema that is defined for that event, and then it goes into a post-collect mode where it does predicate evaluation and during predicate evaluation, if you’ve defined filters on an event on global state data inside of the engine like a session ID, it has to synchronously execute functioned calls to go collect that additional data, and then evaluate the predicate based on its filtering criteria for the event. Now if the event actually is going to fire, and it passes all the predicate evaluation checks, it goes into a publish phase and during the publish phase, it is going to execute the actions to collect additional data if necessary or perform side-effecting operations that have been defined by the action that has been added to the event, and then it is going to serve the data to the synchronous targets immediately. And this all happens on the thread that was executing when the event point and code was encountered. After the synchronous targets have been served, it will then buffer the event data into the memory buffers for the Event Session where they can later be dispatched to the asynchronous targets by one of the dispatcher threads from the dispatcher pool.
Figure 3 Event session isolation
As shown in figure 3, Event session isolation is provided between different event configurations inside of the Extended Events engine. So if we have two different sessions; the first could have the wait info event defined with a specific predicate for page I/O latch waits, and it is going to collect the session ID as an action associated with whatever session was waiting on page I/O latch waited when the wait type was actually set for that. And then if it collects data, it is going to store it inside of a ring buffer target in the Extended Events Engine. Now we could also have a second session that is collecting wait info for the asynchronous network I/O wait for type, and when it actually is going to fire inside of the engine, it will collect the T-SQL Stack associated with the thread that was executing when it encountered that wait, and it is going to store the information to the event file target. So we have two completely different definitions with different actions and different targets and what happens inside of the SQL Server engine is when the event code for weight info is encountered, it is going to evaluate the predicate on the first session. If it evaluates to true, it will then collect the action for session ID and then it will buffer the data to the target buffers for the ring buffer target to eventually receive that event data. Then the engine is going to go to predicate evaluation for the second session. If it does not meet the criteria for the second session, it preempts event execution inside of the engine and the code execution for the task that was running continues to execute as it was before it encountered that point in the code. And this repeatedly occurs inside of the Extended Events engine any time that you have active sessions that are running, and you could have multiple sessions collecting the same event in different manners, and it will always be handled appropriately by the engine.
Before you begin using any feature, you should know its architecture and how it works.
And understand Extended events is going to be critical for every database administrator to be able to support SQL Server in the future. I hope this article has been informative for you.
SQL Server Extended Events Engine
SQL Server Extended Events Sessions
SQL Server Extended Events Packages