Database Reference
In-Depth Information
Using a script to create your sessions allows you to automate across a large number of servers. Using the scripts
to start and stop the sessions means you can control them through scheduled events such as through SQL Agent.
In Chapter 18, you will learn how to control the schedule of a session while capturing the activities of a SQL workload
over an extended period of time.
■
the time captured through a session defined as illustrated in this section is stored in microseconds, not
milliseconds. this difference between units can cause confusion if not taken into account. you must filter based on
microseconds.
Note
Extended Events Recommendations
Extended Events is such a game-changer in the way that information is collected that many of the problematic areas
that used to come up when using trace events have been largely eliminated. You have a much reduced need to worry
as much about severely limiting the number of events collected or the number of fields returned. But, as was noted
earlier, you can still negatively impact the system by overloading the events being collected. There are still a few
specific areas you need to watch out for.
•
Set max file size appropriately.
•
Avoid debug events.
•
Avoid use of No_Event_Loss.
I'll go over these in a little more detail in the following sections.
Set Max File Size Appropriately
The default value for files is 1GB. That's actually very small when you consider the amount of information that can
be gathered with Extended Events. It's a good idea to set this number much higher, somewhere in the 50GB to100GB
range to ensure you have adequate space to capture information and you're not waiting on the file subsystem to create
files for you while your buffer fills. This can lead to event loss. But, it does depend on your system. If you have a good
grasp of the level of output you can expect, set the file size more appropriate to your individual environment.
Avoid Debug Events
Not only does Extended Events provide you with a mechanism for observing the behavior of SQL Server and its
internals in a way that far exceeds what was possible under trace events, but Microsoft uses the same functionality as
part of troubleshooting SQL Server. A number of events are related to debugging SQL Server. These are not available
by default through the wizard, but you do have access to them through the T-SQL command, and there's a way to
enable them through the channel selection in the Session editor window.
Without direct guidance from Microsoft, do not use them. They are subject to change and are meant for Microsoft
internal use only. If you do feel the need to experiment, you need to pay close attention to any of the events that include
a break action. This means that should the event fire, it will stop SQL Server at the exact line of code that caused the
event to fire. This means your server will be completely offline and in an unknown state. This could lead to a major
outage if you were to do it in a production system. It could lead to loss of data and corruption of your database.
