The Wait is Finally Over! SQL Diag Configuration Tool released on CodePlex

  • Article

The wait is finally over! The SQLDiag Configuration Tool has been released. One of the most heavily used tools in the SQL Server Premier Field Engineer’s tool belt is PSSDiag. It is maintained by Microsoft’s Customer Service and Support organization (CSS) and has a very nice configuration tool which allows a PFE to configure the items to be captured when investigating performance issues – for example you may enable or disable specific SQL Server Profiler Trace Events, Windows Performance Monitor counter collection, Event Log collection and so on. Starting with SQL Server 2000 and enhanced in SQL Server 2005, 2008, and 2008 R2 a productized version of PSSDiag is shipped with every installation of SQL Server. It is named SQLDiag. The executable can be found in the following location if the user did not change the installation path: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLDiag.exe. This tool will perform almost all of the same collection activities as its sibling PSSDiag. However, the configuration utility that is used by PFEs to configure PSSDiag is not available for SQLDiag. In order to configure SQLDiag you must edit by hand (think notepad or xmlNotePad) which is error prone and tedious. For more information about SQLDiag please visit the SQL Server Books Online Topic – SQLDiag.

Installing and Setting up the SQLDiag Configuration Tool

So, enter SQLDiag Configuration Tool! Your one stop shop for configuring SQLDiag captures. After you install the tool and execute it, you will be presented with the following welcome screen

SQL Diag Configuration Tool

On this opening screen you have the option of selecting an SQLDiag Configuration file pre-configured for SQL Server 2005, 2008, or 2008 R2. Or if you have been working on a file that you want to modify you can press the Open button to select your custom file.

Each subsequent page will allow you to configure a portion of the configuration file.

The Authentication Page lets you specify Server and Instance Name and whether you want to use Windows Authentication or SQL Server Authentication

SQLDiag Authentication Page

Next you specify the Machine Wide Diagnostics options, such as whether or not to capture Event Log Information and whether or not to do so at startup, shutdown, or both.

Machine Wide Diagnostics

After Machine options are configured you can specify Instance specific options such as capturing blocking information and whether or not SQLDiag captures configuration data at start up, shutdown or both.

Specify Instance specific options

Next we select the Profiler Events that we would like to capture. Note we can support predefined Profiler configuration files as well.

Select the Profiler Events that we would like to capture

Next we configure Perfmon options

Perfmon options

We can also enable Producers and Analyzers

Producers Analyzers

And finally we can save the results of our hard work….

All the source code for the SQLDiag Configuration Tool is available on CodePlex.

Until next time,

boB ‘The Toolman’ Taylor