Consolidating Data Capture with SQLdiag - SQL Server 2012 Internals and Troubleshooting - page 300

➤

The SqldiagCollector element specii es whether the utility will collect the SQLdiag out-
put i le, discussed earlier in the “Using SQLdiag in Snapshot Mode ” section.

➤

The ProfilerCollector element specii es the SQL Server Proi ler events to be captured,
along with all the Proi ler trace coni guration settings. Note that SQLdiag always captures
a server-side Proi ler trace to ensure minimal impact of a Proi ler trace capture on the SQL
Server instance.

➤

The BlockingCollector element enables trace l ag 1222 to facilitate the capture of dead-
lock graphs in the SQL Server Error Log.
You can control the collectors used by SQLdiag using the XML templates. Every collector can be
enabled or disabled using <enabled=true> or <enabled=false> within the coni guration i le. Use
either your favorite text editor to modify the XML coni guration i le or even Management Studio,
which supports XML document editing.

WARNING Don't delete collectors from the XML i les, as SQLdiag will subse-
quently fail to load the XSD if the XML i le doesn't match.

If you are wondering what happened to the CustomDiagnostics variable explanation, it is covered
in the section “Using SQLdiag Coni guration Manager.” The following code snippet shows all the
parameters that the SQLdiag utility can accept.

Usage: sqldiag [START | STOP | STOP_ABORT] [/O outputpath] [/I cfgfile] [/M machine1
[machine2 machineN]|@machinelistfile] [/Q] [/C #] [/G] [/R] [/U] [/N #] [/A appname]
[/T {tcp[,port]|np|lpc|via}] [/X] [/L] [/B YYYYMMDD_HH:MM:SS] [/E YYYYMMDD_HH:MM:SS]

Now take a look at how each of these parameters is used, along with some best practices to prevent
the data collection effort from becoming an accessory to a performance degradation crime.
SQLdiag Parameter List

➤

START | STOP | STOP_ABORT — START and STOP are used to start and stop the data
collection, respectively. STOP _ ABORT is used when data collection needs to be terminated
immediately without waiting for all the collectors to complete, which proves quite useful
when your data collection might start competing for resources with a production workload
or your data collection has exceeded your intended data collection time window.

➤

/O outputpath — This specii es the directory in which the utility will write the collected
data. It is advisable to use a local disk on which SQL Server database i les do not reside.

➤

/I cfgfile — This specii es the coni guration i le to be used for the data collection. The
default coni guration i le is always SQLDiag.Xml .