Databases Reference
In-Depth Information
which means it requires far less code to function compared to older tools that connect to SQL Server
like a regular user does before they send event data. Second, although other tools such as Proi ler
may try to limit the amount of data they request, they tend to use inherently inefi cient mechanisms
within SQL Server that often results in capturing more data than is needed.
Instead, Extended Events take a reactive approach, only collecting and sending data to their target
when a previously coni gured situation being monitored for occurs. If that event doesn't happen,
then the SQL Server engine will not capture or store any event data. Microsoft has measured just
how lightweight the events architecture that Extended Events use actually is, and determined that
20,000 events per second i ring on a server with a 2 GHz Pentium CPU and 1GB of memory con-
sumed less than 2% of the CPU's resource — and that's on a very old specii cation server!
Of course, it's always possible to coni gure anything badly and suffer undesirable consequences,
including with Extended Events, so low overhead should not be an excuse to avoid planning and
testing their deployment into your production environments. In fact, it's even possible to purposely
coni gure an Extended Events session to stop SQL Server when a specii c event occurs; it's that
powerful! However, to do that, you would have to deliberately coni gure that very specii c action to
occur, so don't worry that you could accidentally stop SQL Server.
When You Might Use Extended Events
Having discussed some of the reasons why you might want to use Extended Events, this section
considers what you might use it for. If you're already familiar with tools like Proi ler and SQL Trace,
then you will already be familiar with these examples based on your current monitoring:
➤
Troubleshooting blocking and deadlocking
➤
Finding long-running queries
➤
Tracking DDL operations
➤
Logging missing column statistics
After you start to explore some of the more advanced capabilities of Extended Events, you'll see it's
just as easy to track events such as the following:
➤
Long-running physical I/O operations
➤
Statements that cause specii c wait stats to occur
➤
SQL Server memory pressure
➤
AlwaysOn Availability Groups events
WHAT ARE EXTENDED EVENTS?
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 specii c 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. For
Search WWH ::
Custom Search