SQL Server Extended Events (Extended events)-Performance considerations

SQL Server Extended events (Extended events)-Performance considerations

When you use the CREATE event session to place extended event sessions together, you need to carefully configure some settings as they may inadvertently affect performance. You first need to decide whether you want to consume events in a synchronous or asynchronous manner. As you can tell, synchronization targets have a greater impact on the performance of the monitored code than the asynchronous target.

As mentioned earlier, when an event is consumed synchronously, the code that triggers the event must wait until the event is consumed. Obviously, if event consumption is a complex process, this can degrade the performance of your code.

For example, in a busy system that processes thousands of small transactions per second, synchronizing consumption of sql_statement_completed events and capturing query plan actions is likely to adversely affect performance. Also keep in mind that predicates are always executed synchronously, so be careful not to create overly complex predicates for events triggered by performance-critical code.

On the other hand, you may be forced to synchronize consumption events. The simplest way to calculate the number of occurrences of a particular event is probably to use the Synchronous_event_counter target.

The second thing you must consider is how to configure event buffering if you decide to use an asynchronous target. The default number of available memory for event buffering is 4MB. The event is triggered and then consumed by the target, and the default schedule for the period is delayed by 30 seconds. This means that if you want to generate some event statistics every 10 seconds, you must adjust the delay time.

Partitioning the memory used to buffer events is done by binding it to the event buffer settings. By default, a set of buffers is created for the entire instance. In SMP (symmetric multiprocessor) and NUMA (non-Uniform memory access) computers, this may cause the processor to have to wait for access to memory, resulting in performance issues.

A third consideration is how to handle event loss. When defining an extended events session, you can specify whether the event can be "lost". This means that if there is not enough memory to buffer an event, it can be discarded directly. The default setting is to allow a single event to be discarded, but you can also allow the entire event buffer to be lost (for sessions where the event buffer is quickly filled up), or even to specify that no events should be lost.

You should take extra care when using the last option, because it forces the code that fires the event to wait until there is enough buffer memory to store the event. Setting this option will almost certainly have a negative impact on performance. Note that if this option is inadvertently enabled, the server will still have sufficient responsiveness to enable you to disable this setting.

Typically, you need to consider these options holistically. In fact, I have no way to provide you with a common best method, all I can do is to emphasize to you the deep understanding of them, otherwise you may encounter performance problems.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1599381

SQL Server Extended Events (Extended events)-Performance considerations