Databases Reference
In-Depth Information
➤
Lock: Escalation
— This one is debatable. It helps track down lock escalations but
on servers that have a high number of these, this can become a high frequency event.
➤
OLEDB
— Unless you are sure, based on the symptoms shown regarding the issue,
that OLEDB calls need to be traced, you shouldn't be collecting any event from this event
category except for OLEDB errors. Therefore, if your OLEDB provider were returning a
large number of errors, you would be able to track it down using the
HRESULT
obtained
from this event class.
➤
Query Notifications
— The same logic used earlier for the
Broker
event category applies
to this event category.
➤
Scans
— This might be useful when you are doing a repro of sorts on a test or development
environment, but on a production server this is a strict no-no as you will end up collect-
ing more noise than any useful data for analysis purposes! Capturing this event unless and
until absolutely required is a very easy way to bloat your proi ler trace i le size very quickly!
You should be able to determine, looking at the plans, whether there was a table/index scan
without having to capture a separate noisy event in the Proi ler traces.
➤
Security Audit
— This is a very good event category when troubleshooting security or
permissions-related issues on a SQL Server instance. For performance-related issues, this is
just plain noise! The events under this category are i red nineteen-to-the-dozen on a SQL
Server instance, which just bloats your Proi ler trace size rather than do anything useful!
The two truly useful events in this category are
Audit: Login
and
Audit: Logout
, which
help track the session's connection settings and other attributes, such as host name, user
name, etc.
➤
Transactions
— This event category also has a lot of noise events that aid more in bloat-
ing Proi ler trace size rather than doing anything useful. In particular, don't capture the
Transaction Manager
(
TM:*
) event classes unless you are troubleshooting an issue related
to Begin/Commit/Rollback that is taking a long time to complete. The
SQLTransation
event is quite useful for troubleshooting deadlock-related issues, as it helps track transaction
begin and commit/rollback and nest levels for a given session. The
DTCTransaction
event
class is useful for troubleshooting DTC transaction, related issues and tracking the different
states of a DTC transaction.
➤
Performance Event
— This is one of the most widely used event categories among
Microsoft SQL Server Support Engineers for troubleshooting query performance issues.
That's because this event category helps you capture query plans. There are several different
event classes in this category for capturing query plans, which can be divided into two types
based on data you can obtain from these events:
➤
Compile-time details
—
Showplan All
,
Showplan XML
, and
Showplan Text
(occurs during query execution but does not contain runtime details like rows
returned) and
Showplan XML For Query Compile
and
Showplan All For Query
Compile
(shows the compile-time query plan). These two events can be very use-
ful when you want to capture a query plan for timeout-related issues, as the other
events that show the execution time query plans may not be generated when you
are dealing with query timeouts. Therefore, if you are not facing any timeouts and
Search WWH ::
Custom Search