By David Pless – dpless@microsoft.com

SQLDiag comes built into SQL Server post SQL Server 2005. It is essentially a wrapper for a number of tools that collect performance montitor, SQL Server profiler, and other data at various levels of granularity customizable via an XML configuration file.

From BOL,

“The SQLDiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. You can use SQLDiag to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers. SQLDiag is intended to expedite and simplify diagnostic information gathering..”

SQLDiag Tool
http://msdn.microsoft.com/en-us/library/ms162833.aspx

The beauty of SQLDiag is that you can create templates to quickly capture exactly what you want from the following sources: 

• Windows performance logs
• Windows event logs
• SQL Server Profiler traces
• SQL Server blocking information
• SQL Server configuration information (MSINFO32)
• The default trace output
• SQL Dumper logs
• And other Custom Diagnostics that can be added

To specify the level of detail that is captured you can adjust SQLDiag via the XML configuration files which are located in the <system root>:\Program Files\Microsoft SQL Server\100\Tools\Binn\ directory.

The default location for the SQLDiag is the following directory:
“C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLdiag.exe”

If SQLDiag has never been run there will not be any templates just the actual executable. By default SQLDiag captures very little information. The default view is shown below.

Once SQL Diag is run for the first time the SQLDiag.xml, SD_General.xml, and SD_Detailed.xml configuration files will be created. Each of these configuration files have a different purpose. The default, which is what you will run if you simply double-click the SQLDiag tool directly, collects very little information. It is easier to say what won’t be captured. If you simply double-click the SQLDiag executable you will not collect perfmon counters, SQL Trace data, or blocking. You basically get the error logs and the MSInfo output.

The SD_General.xml and SD_Detailed.xml configuration files both collect perfmon and SQL Trace data. The SQL Trace data is captured at different levels of granularity. Below shows the differences in a comparison of what each template captures for SQL Trace data.

	SD_General	SD_Detailed
Broker:Conversation (Event ID – 124)	FALSE	TRUE
Broker: Message Classify (Event ID – 141)	FALSE	TRUE
Cursor Open (Event ID – 53)	FALSE	TRUE
Showplan XML Statistics Profile (Event ID – 146)	FALSE	TRUE
SP: StmtCompleted (Event ID – 45)	FALSE	TRUE
SQL:StmtCompleted (Event ID – 41)	FALSE	TRUE

Note: Both templates do not collect the blocking information which utilizes the blocking process report.

Otherwise, both templates are exactly the same. The differences between the templates are very important. SD_Detailed will take up more space and will be much more impacting by capturing statement level information from batches and stored procedures as well as the Showplan XML Statistics output. It is usually recommended to capture ‘general’ information and get the detail if necessary.

The XML file is very easy to read. You can essentially turn any counter or option on | off by adjusting the true / false value.

Note: In order to create a custom template, it is necessary to make a copy of either the SD_General or the SD_Detailed.xml file and make the necessary adjustments to the copy.

In order to utilize the trace templates you must launch SQLDiag from a command prompt or batch script utilizing the /I switch to point to a configuration file.

For example, to start SQLDiag using a custom template you would run something similar to the following from the ..Tools\Binn directory:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>sqldiag /I SD_Detailed_w_blocking.xml

Note: When you execute this command you will may see a message asking you if you want to overwrite the previous output directory. If you want to keep the previous output, it is recommended to either rename the directory, perhaps suffxing it with a datetime stamp, or zipping and archiving the directory to another location.

Below is the execution of sqldiag with the SD_Detailed_w_blocking.xml template. Notice that we are gathering perfmon, Profiler, and blocking data which is only available via customization to the templates. This is what we look for to verify that our templates are turning on | off the right options.

Additionally, you will notice when you start SQLDiag a pop-up window gather system diagnotics that looks like the following:

This is expected, do not click cancel or the capture of the system configuration will not be successful. A lot of very valuable system information can be found in the MSInfo output.

As long as SQLDiag is running, data is being captured. For a baseline, 30 minutes to an hour is a good window in the output directory does not grow too large. When troubleshooting a reactive issue, run the trace until the issue has been reproduced in the window.

Important: If SQL Trace data is being captured it is important to monitor space very carefully. By default, SQLDiag will store data in the Program Files\Microsoft SQL Server\100\Tools\Binn\ location which is often the system root drive. Filling space on the system root drive can put the server in an unstable state.

When shutting down SQLDiag, make sure you follow the instructions and hit CTRL + C  and do not simply close the command window. If you close the command window a lot of information will be lost. There is quite a bit of information gathered and processed during the shudown phase.

Below is a listing of the SQLDiag options most commonly used.:

[/I configuration_file] – Used to specify a built-in or custom template
[/O output_folder_path] – Used to point to a different output directory other than the default Program Files\Microsoft SQL Server\100\Tools\Binn\ location
[/N output_folder_management_option] - Sets whether SQLDiag overwrites or renames the output folder when it starts up.

Available options:

• 1 = Overwrites the output folder (default)
• 2 = When SQLdiag starts up, it renames the output folder to SQLDIAG_00001, SQLDIAG_00002, and so on. After renaming the current output folder, SQLdiag writes output to the default output folder SQLDIAG.

[/B [+]start_time] – Specifies the date and time to start collecting diagnostic data in the following format: YYYYMMDD_HH:MM:SS
[/E [+]stop_time] – Specifies the date and time to start collecting diagnostic data in the following format: YYYYMMDD_HH:MM:SS

Note: For more information on ‘Automatically Starting and Stopping SQLdiag’ see the following article: SQLDiag Utility (http://msdn.microsoft.com/en-us/library/ms162833.aspx)

Recommendation: Though it may be tempting, avoid using the /C compression option. Since SQLDiag runs on the server and your SQL Server, it is usually not desired to add additional cpu overhead to your environment. It is better to use the /O option to find a volume that does have enough space. 

 sqldiag
     { [/?] }
     |
     { [/I configuration_file]
       [/O output_folder_path]
       [/P support_folder_path]
       [/N output_folder_management_option]
       [/M machine1 [ machine2 machineN]| @machinelistfile]
       [/C file_compression_type]
       [/B [+]start_time]
       [/E [+]stop_time]
       [/A SQLdiag_application_name]
       [/T { tcp [ ,port ] | np | lpc | via } ]
       [/Q] [/G] [/R] [/U] [/L] [/X] }
     |
     { [START | STOP | STOP_ABORT] }
     |
     { [START | STOP | STOP_ABORT] /A SQLdiag_application_name }

What’s the difference between SQLDiag and PSSDiag?
SQLDiag and PSSDiag are very similar and at the core, collect the same information. The big differenece between SQLDiag and PSSDiag is that PSSDIAG collects some additional information related to system and SQL internals that is usually only valuable to internal Microsoft support organizations. PSSDIAG is configured via a GUI front end that allows a high degree of customization. This front-end is only available to Microsoft support.

SQLDiag is also configurable, but does not collect any other custom diagnostics unless explicitly customized to do so. SQLDiag does not have a front-end, but does include some built-in templates that can be leveraged to capture different levels of detail as well as creating new customized templates.