To clarify what Extended Events are, they are a feature within the SQL Server database engine that enables SQL Server to watch for and then respond to specific database engine events occurring. These events could be as simple as a user logging in or something more technical such as a long running I/O operation. When a watched for event occurs, the Extended Event session performs an action, which is typically to gather additional information about what has just happened.
select name, description from sys.dm_xe_packages
select c.name, c.description from sys.dm_xe_object_columns c join sys.dm_xe_objects o on o.name= c.object_name where o.name = 'sql_statement_starting'
Actions
It’s unlikely that the event itself will provide all the data required to perform your troubleshooting. For example, sql_statement_starting, containing only the fields line_number, offset, offset_end, and statement, which by themselves probably aren’t adequate to diagnose an issue. You would likely want some additional information, such as the session ID, the user name, or perhaps the query plan’s handle. Actions are the way that Extended Events provides you with the extra data you want about an event.
In fact, in SQL Server Management Studio, the Actions window is also called Global Fields, which is arguably a more accurate name for them. A few actions, however, do perform tasks other than a data lookup, such as creating a minidump or even halting SQL Server. These are technical tasks aimed at hardcore debugging; you won’t find an action with a name like “Execute stored procedure” in Extended Events. There are 48 actions, or global fields, that ship with SQL Server 2012; and like the events themselves, they are defined in the SQL Server source code so it’s not possible to add your own. You can query which are available through a provided DMV:
SELECT name, description FROM sys.dm_xe_objects WHERE object_type = 'action' and capabilities_desc is null ORDER BY name
When an Extended Events session is configured to use an event file as a target, the event data is first written by the Extended Events engine to memory buffers before a second process writes it from memory to disk. This separation of processes is an important difference between Extended Events and other troubleshooting tools, as it prevents the physical writing of the data to disk from affecting performance for end users under normal circumstances. This behavior explains why event files are considered asynchronous targets.
Because Extended Events has been designed for use on servers with even the heaviest of workloads, there are several options you can configure regarding how the data is buffered and what should occur when the buffer is overwhelmed. The next three settings reviewed, memory buffer size, event retention, and maximum dispatch latency, apply to most of the targets Extended Events sessions can use, but it is useful to consider them now that you understand the potential problems a session with a full memory buffer and an event file target could have.
Memory Buffer Size
The size of the memory buffer is a configurable session option that can ultimately determine how much of a performance impact an Extended Events session has on your server, and potentially how much event data you might lose in busy conditions. For a small server with a small workload, the default maximum buffer size of 4MB is probably sufficient; but on a much busier server, you might be writing more event data to the memory buffer than your target can subsequently flush to its storage during peak periods.
As a hypothetical example, if your Extended Events session is capturing event data at the rate of 200MB/sec, but your server can only write 100MB/sec to disk, you’re very quickly going to either need a lot of memory to store events before they’re flushed to disk or begin discarding data from the buffer. The buffer is a First in, First out (FIFO) list, so if unflushed events need to be deleted to make room for newer events, then the oldest ones will be removed first.
No comments:
Post a Comment