Extended Events - SQL Server 2008

Extended Events (EE) is a general event-handling infrastructure used within server systems. Similar to other event-based subsystems like SQL Trace, EE is primarily used to monitor server state and as a tool to diagnose issues in SQL Server. EE leverages the Event Tracing for Windows (ETW) feature within the operating system. This allows for event data captured from SQL Server to be correlated with data from the operating system, as well as any other applications that write ETW logs. Together, the EE services and objects create an infrastructure that is by far the most flexible and efficient eventing engine available within SQL Server. The performance of EE is many times better than that of SQL Trace.

EE events are designed to be available within the SQL Server engine itself and optionally within your own custom DLL. However, for SQL Server 2008, you will be able to use only EE events defined in the packages that come with SQL Server 2008. You will not be able to leverage EE on its own outside SQL Server.

Extended Events Components

At a high level, EE is composed of events, targets (event consumers), and actions. These pieces are all autonomous, such that the event engine has no knowledge of specific individual events that it processes. The event and corresponding target and action information are defined by the process that uses the eventing engine. The flexible design of this feature allows for any target to process any event and any event can write to any target.

A module is the actual binary that exposes one or more package objects. Since, as developers, we cannot add our own modules in SQL Server 2008, there is not much else to discuss with respect to modules other than that a few are installed by the SQL Server database engine and Reporting Services.

Packages

A package with respect to EE has no relation to packages in SSIS. This is yet another example of an overused term in SQL Server.

A package in EE is a container for events, targets, actions, and the other objects and structures used in an EE solution. This relationship between the package and the rest of the supporting objects in EE.

Extended Events components

Extended Events components

Packages are uniquely identified by a name and GUID. In SQL Server, a number of packages are installed and available by default. Using the sys.dm_xe_packages dynamic management view, you can see the list of installed packages, including the GUID and module information. Querying this view just for the name and description yields the following:

Extended Events components

Each one of these packages contains a unique list of events, targets, actions, types, predicates, and maps. Don’t worry about being bound to using only a single package within an EE session; you can easily mix and match events and targets among packages.

Events

Event is another overloaded term in SQL Server. When someone talks about an event, we need to ask ourselves if they are referring to a SQL Trace event, an event within Event Notifications, or one of any of the many other eventing capabilities in SQL Server.

In EE, events are similar to SQL Trace events, in that they are basically points in the engine code. Whether the start of a batch or a user login, all of these interesting points in the database engine code contain events. This is where the similarity between SQL Trace and EE ends, as events in EE are built upon an entirely different architecture.

To obtain a list of the available events, you can query the sys.dm_xe_objects dynamic management view. More than 200 events come with the preinstalled packages in SQL Server 2008. Here are just a few:

  • Database started
  • Database stopped
  • Error log written
  • Checkpoint has begun
  • Checkpoint has ended
  • Deadlock
  • SQL statement starting
  • SQL statement completed

Targets

Targets are event consumers and can be written to either synchronously or asynchronously. Targets can process these events individually or process a buffer full of them. These capabilities are available on a per-target basis.

To obtain a list of the available targets, you can query the sys.dm_xe_objects dynamic management view. A total of 13 targets come preinstalled in SQL Server 2008. Here is a sampling:

  • ETW synchronous target
  • Asynchronous ring buffer
  • Asynchronous security audit NT security log target
  • Asynchronous security audit file target

Actions

Actions are code that runs on the thread that fired the event. They are used to capture stack dumps, to inspect event data, and even to add data from outside the event’s payload. To obtain a list of the available actions, you can query the sys.dm_xe_objects dynamic management view. A total of 35 actions come preinstalled in SQL Server 2008. A sample list:

  • Collect SQL text
  • Collect client application name
  • Collect NT username
  • Collect current task execution time

Predicates

Since firing an event is expensive, predicates exist to help evaluate conditions prior to the firing of the event. Predicates are full Boolean expressions that evaluate to true or false.

There are two types of predicates: those that use local event data and those that use global state data or data that is not directly a part of the event itself. Some examples of global data include the session ID and SP ID of the connection context. Predicates also can store state, allowing scenarios where you can keep a counter such that when events occur, you can fire them every other time or every nth time. This is good to use if you have events that fire frequently and, for performance reasons, want to obtain a sample of the data, as opposed to record every single event data point.

Event Sessions

Event sessions are bindings between events and targets. Events themselves can be leveraged in multiple sessions, but the actions and predicates are defined on a per-session basis.

Extended Events Example: Detecting Deadlocks

As an example of using EE, we will create a lock condition between two users. We can create an event session that will listen for a sqlserver.lock_deadlock event within the AdventureWorks database. When this event is received, it will be placed in a ring buffer. In addition to the event information, we are requesting the actual SQL command be included as part of the event. The following code will create our event session:

CREATE EVENT SESSION AdventureWorksDeadLocks
ON SERVER
ADD EVENT sqlserver.lock_deadlock
(ACTION (sqlserver.sql_text)
WHERE sqlserver.database_id = 7)
ADD TARGET package0.ring_buffer
( SET max_memory=1024)
GO
ALTER EVENT SESSION AdventureWorksDeadLocks ON SERVER
STATE=START
GO

Next, we will create the deadlock using two user connections. Assume both User1 and User2 have appropriate access to the AdventureWorks database and Sales.CurrencyRate table. First, User1 issues these statements:

USE AdventureWorks
GO
BEGIN TRAN
UPDATE Sales.CurrencyRate SET AverageRate=2 where CurrencyRateID=1

Next, User2 issues these statements:

USE AdventureWorks
GO
BEGIN TRAN
UPDATE Sales.CurrencyRate SET AverageRate=2 where CurrencyRateID=1

To create the deadlock, User1 issues this statement:

SELECT * FROM Sales.CurrencyRate

Once we issue the last SELECT statement, we will have a deadlock, and SQL Server will choose a deadlock victim. When this happens, an event will be raised and sent to our ring buffer target. To see a list of the targets, you can query the sys.dm_xe_session_targets dynamic management view. Since we are using the ring buffer as a target, the actual event data is written in XML form to the target_data column of this view. Reading that column, we can see some valuable information including the SQL text. An abbreviated result set for the target_data column:

<RingBufferTarget eventsPerSec="166" processingTime="12" totalEventsProcessed="1"
eventCount="1" droppedCount="0" memoryUsed="512">
<event name="lock_deadlock" package="sqlserver" id="70" version="1"
timestamp="2008-01-16 23:19:45.403">
...
<data name="mode">
<type name="lock_mode" package="sqlserver" />
<value>5</value>
<text>
<![CDATA[ LCK_M_X ]]>
</text>
</data>
<data name="owner_type">
<type name="lock_owner_type" package="sqlserver" />
<value>1</value>
<text>
<![CDATA[ Transaction ]]>
</text>
</data>
<data name="transaction_id">
<type name="int64" package="package0" />
<value>196350</value>
<text />
</data>
<data name="database_id">
<type name="uint16" package="package0" />
<value>7</value>
<text />
</data>
...
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>
<![CDATA[ BEGIN TRAN UPDATE Sales. CurrencyRate SET AverageRate=2
where CurrencyRateID=1
]]>
</value>
<text />
</action>
...
</event>
</RingBufferTarget>

From this target data, we can obtain important information, including the lock ID, lock type, and SQL text that caused the deadlock.

This example used the ring buffer as a place to target events. The real power of EE comes with writing to the trace target for ETW. ETWis the next version of the tracing capabilities for Windows, available starting with Windows Server 2008. Having the ability to write to ETW trace files allows you to easily correlate events that happened in the operating system with events that happened in SQL Server, as well as any other provider that writes to ETW.

EE is a powerful and somewhat complicated event platform within the SQL Server engine. Here, we have just scratched the surface of what you can do with it. EE’s applications reach far beyond just performance tuning—after all, this feature was originally implemented for use only by the Microsoft Product Support team. Late in the product cycle, the product team made a decision to expose interfaces into the feature and allow users to leverage the power of EE. Now that these interfaces are public, be on the lookout for really creative performance and troubleshooting designs that take advantage of EE; they should be appearing in the near future.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics