Databases Reference
In-Depth Information
need the query plans with runtime details, you need not capture any of the afore-
mentioned events. The Query Compile event classes (especially the XML events) are
required when you are trying to nail down compilation-related issues and want to
determine the resources (CPU/memory) consumed during query compilation.
➤
Runtime details
—
Showplan Statistics Profile
and
Showplan XML
Statistics Profile
show the query plan with runtime details in text and
XML format, respectively. Based on whether you prefer sifting through text or
XML, you could capture either one. XML events are not the best in terms of
reducing the size of the Proi ler traces captured.
Following are some other high-frequency event classes, which can be excluded as well from your
proi ler trace coni guration unless the issue that you are troubleshooting warrants the collection of
these events:
➤
SP:CacheMiss
,
SP:CacheInsert
,
SP:CacheRemove
, and
SP:CacheHit
— These events are
helpful when tracking procedure cache plan caching issues; otherwise, you are going to
be dealing with a lot of noise in the Proi ler traces if these events are captured, as one of
these events occurs every time a plan is looked up in the procedure cache.
➤
TSQL: Prepare SQL
,
TSQL: Exec Prepared SQL
,
TSQL: Unprepare SQL
— These are
useful for troubleshooting issues with prepared SQL calls from providers but should be
avoided unless and until you are coni dent that the problem is due to how the SQL statement
is being prepared.
Alert-Driven Data Collection with SQLdiag
Another way to minimize the amount of time you need to run the data collection utility is to be
aware of the symptoms associated with your problem period. Troubleshooting intermittent issues
is always tricky; you must tread the i ne line between the necessary evil of collecting diagnostic data
and running the risk of antagonizing your customers or users with even further degraded perfor-
mance. The preceding tips can help you avoid unwanted phone calls when you are trying to be the
Good Samaritan by helping to get the server back up and running while it is reduced to a crawl due
to a performance bottleneck.
An alert eye can spot symptoms like high CPU usage, a high number of lock timeouts, or a high
number of blocking chains during a problem period, any of which can become your trigger point
to start the data collection. SQL Server Agent can monitor and automatically respond to events,
such as messages from SQL Server, specii c performance conditions, and Windows Management
Instrumentation (WMI) events. The response to such an alert can be a job that starts the SQLdiag
data collection through the use of command-line parameters. Not only is this a smart way of
collecting data, it will minimize the amount of time you spend praying that the issue will occur
while you are staring at a dashboard, monitoring the SQL Server instance's performance metrics.
Unfortunately, Murphy's Law has a unique way of rearing its head, so the chances are high that the
bottleneck that brings your SQL Server instance to its knees will appear at the most inopportune
moment possible. You can go one step further and send out e-mails or pager alerts when the data
collection begins, to ensure that necessary attention is given to the issue at the correct time.
Search WWH ::
Custom Search