Databases Reference
In-Depth Information
The following are
not
recommended as methods to deal with space constraints on the target
machine where you are capturing data:
➤
Attempting to trace to a network drive or UNC path or using the client-side Proi ler UI
from a remote machine
— This method of tracing is highly detrimental to SQL Server
performance and should be avoided at all costs! SQLdiag always captures a server-side
Proi ler trace. The destination output folder should always be a local drive!
➤
Attempting to run SQLdiag remotely in an attempt to minimize space usage on
the server
— This isn't useful because Proi ler traces are always captured locally
on the server (even if SQLdiag is running remotely).
Filtering Out the Noise
The prime reason why the size of a SQLdiag data collection balloons out of proportion or the target
SQL Server performance degrades while capturing diagnostic data is the high number of redundant
or irrelevant Proi ler events added to the data collection coni guration.
The following list provides guidelines on when to avoid certain Proi ler event categories which gen-
erate a high number of events when added to the set of proi ler events being captured by SQLdiag.
This can lead to a high amount of chatter in the trace i les captured without adding value to the
analysis:
➤
Broker
— None of the events in the Broker category are required unless you are trouble-
shooting an issue related to Database Mail or Service Broker in SQL Server 2005 or above.
However, the Broker event category can generate a lot of noise if you have Service Broker/
Database Mail coni gured on the server and are using it heavily.
➤
Cursors
— If the client application or provider that connects to your SQL Server instance
uses server-side cursors, then this event category can lead to an unmanageable amount
of data collected that will take you ages to sift through. The
sp_cursor*
calls will
be captured either way in your Stored Procedure event classes, which makes it a moot
point to track
CursorOpen
,
CursorClose
, and
CursorExecute
through this event cat-
egory for general-performance issues. The only useful events in this event category are
CursorImplicitConversion
(which enables you to track implicit conversion of requested
nonsupported cursor types by the SQL Server database engine) and
CursorRecompile
(which
helps you track down T-SQL cursor recompiles due to schema changes). You would want to
use the events in this category only when troubleshooting a specii c cursor-related issue.
➤
Locks
— This event category generates the highest amount of activity.
Lock:Acquired
and
Lock:Released
event classes (if captured) can make your data analysis more difi cult
than scaling Mount Everest. If you need to track the number of locks for each session, then
it is done best outside the Proi ler using DMVs such as
sys.dm_tran_locks
. However, this
event category has some useful event classes:
➤
Lock: Deadlock
and
Lock: Deadlock Chain
— Helpful when troubleshooting
deadlock issues on a SQL Server instance
➤
Lock: Timeout
and
Lock: Timeout (timeout > 0)
— Help troubleshooting
timeout-related issues due to lock blocking
Search WWH ::
Custom Search