If you recognize the term PSSDIAG, then
you have been around the SQL Server world for quite some time now and
have worked on various SQL Server issues that required Microsoft
engineers to collect diagnostic data using the PSSDIAG tool. You would
also be familiar with saving PSSDIAG in a secure location and going
through the XML configuration file to review the additional data that
the PSSDIAG tool collects. In 2011, the Microsoft SQL Server Support
team released a public version of the coveted Configuration Manager
used for setting up data collection using SQLdiag, available on the
CodePlex website, Microsoft’s free open source project hosting site,
under the name Pssdiag/Sqldiag Manager.
This means that you no longer need to painstakingly modify XML
configuration files in a text editor. This tool is provided on an as-is
basis by Microsoft. This tool provides you with the capability of using
a graphical user interface to configure your data collection rather
than having to painstakingly modify a XML configuration file.
NOTE As-is
support means that you are free to download the tool and use it for
your data collection needs. However, Microsoft Support does not support
the tool and the only medium of support you have is to post a question
or issue on the tool’s codeplex homepage.
The tool has the following prerequisites:
- .NET Framework 2.0
- Windows 2003 Server/XP or above
HOW TO INSTALL THE PSSDIAG/SQLDIAG MANAGER
1. Visit the Pssdiag/Sqldiag Manager home page by browsing to http://diagmanager.codeplex.com/.
2. Click the Downloads tab.
3. Download the setup.10.5.1.202.zip file after accepting the license agreement.
4. Extract the files and run the installation using setup.exe.
In order to understand why this tool, hereafter referred to as Diag Manager,
is so important to data collection related to SQL Server issues, the
following list describes the many ways in which it extends your data
collection capabilities:
- It relies on the SQLdiag collector engine to provide a collection
of PerfMon, Profiler trace, msinfo32, error logs, Windows event logs,
T-SQL script output, and registry exports.
- It ships with a ready-to-use set of custom collectors.
- It provides an interface for cutomization of the PerfMon and
Profiler trace collection along with the capability to add your own
custom collectors.
- It will package all your files into a single cab file for the machine from which you intend to collect data.
- The custom collectors shipped will collect data that can be analyzed by the SQL Nexus tool.
NOTE If
you encounter any issues while using the tool, you can file it using
the Issue Tracker link on the home page of the Diag Manager tool on
CodePlex. If you have any questions, you can start a new discussion
using the Discussions link.
Diag Manager is a 32-bit tool whose default installation location is as follows:
- 64-bit — C:\Program Files (x86)\Microsoft\Pssdiag
- 32-bit — C:\Program Files\Microsoft\Pssdiag
Once you have installed Diag Manager, you can find the program under All Programs ⇒ PSSDIAG ⇒ PSSDIAG Configuration Manager.
NOTE The
Diag Manager does not have a specific SQL Server 2012 tab. You can use
the SQL Server 2008 tab for configuring the data collection for SQL
Server 2012. All of the configurations that are available under the SQL
Server 2008 tab work for SQL Server 2012 instances.
Configuring SQLdiag Data Collection Using Diag Manager
After launching the tool, the GUI will provide various configuration options, as shown in Figure 1.
The arrows in Figure 1
show the different configuration options in the UI that enable you to
easily customize your data collection.
Now take a look at what the Diag Manager UI
allows you to customize. The first task is to select the platform: x86,
x64, or IA64 (arrow 10 in Figure 1).
Then you can choose the SQL Server version using the appropriate tabs.
SQL Server 7.0 and SQL Server 2000 configurations (arrow 9 in Figure 1) are not supported by this tool.
WARNING If
you forget to select the platform or the SQL Server version and need to
make a change later or post the customizations made, you will lose any
changes made and the options will default to the template selected.
Now that you know how to select the right platform and SQL Server version, consider some key areas in the tool. The Issue Type section (arrow 3 in Figure 1)
available in the leftmost pane of the UI is the list of templates you
can use for configuring the data collection, with some events and
collectors pre-configured. You could start with the sql_default_2008 collector and edit them as appropriate for your data collection needs.
The Connection Info (arrow 1 in Figure 1)
box is where you provide the machine name and the SQL Server instance
name from which you intend to collect the diagnostic data. The
full-stop (.) and asterisk (*) for the machine name and instance name,
respectively, direct the configured SQLdiag package to collect data
from all the SQL Server instances installed on the local machine. The
best configuration practice here is to always provide a machine name
and an instance name. The considerations mentioned in the section “Configuring and Running SQLdiag on a Failover Cluster” for configuring SQLdiag on a cluster apply here as well.
After providing the machine name and instance
name, you can select the authentication used to permit collection of
the data, Windows or SQL Server authentication. If you choose SQL
Server authentication, you can only provide the username (arrow 2 in Figure 1). You will be prompted for the password at runtime. The ##SQLDIAG.log file will contain the following information when you use SQL Server authentication for logging into the SQL Server instance:
Password:
User prompted for password at runtime
SQLDIAG Initialization starting...
The next section is Machine-wide Diagnostics (see Figure 2 and arrow 4 in Figure 1), which enables you to configure the PerfMon data collection by specifying the following:
- The different PerfMon counters (arrow 4 in Figure 1) that you want to collect
— By default, a set of counters is pre-populated based on the
information present in the selected template. You can enable additional
counters that you deem necessary for analyzing your problem scenario.
It is always a good practice to collect all the SQL Server Performance
Monitor counters so that you do not miss any relevant information
required for your data analysis. Furthermore, the overhead of
collecting PerfMon data is the lowest compared to the other data
collectors.
- The maximum file size and interval at which you want to collect the data samples
- Configuration of the Windows Event Log collection
The next step is to configure the Profiler trace. This can be done using the Instance-specific Diagnostics (arrow 7 in Figure 1) section of the tool, used for configuring database engine/analysis server trace (arrow 6 in Figure 1)
and collection of SQLdiag output. Here you can configure a trace for
the database engine and SQL Server Analysis Services instance. Based on
the SQL Server version selected, you will see the Profiler trace events
populated in the list, which you can configure for your data collection
along with the Profiler trace rollover size. Collection of the SQLdiag
diagnostic script can be enabled or disabled from this section.
WARNING Although
you can right-click on a Profiler event and add filters from this
section, you shouldn’t use this option, as the trace filter will not be
honored when added to the XML configuration file.
The last section in the Diag Manager is Custom Diagnostics (arrow 5 in Figure 1),
which provides a list of pre-built custom collectors that are already
available for data collection. In addition to this, you can extend the
data collection, as described in more detail in the section
“Understanding the Custom Diagnostics.” The Instructions (arrow 8 in Figure 1) tab at the bottom of the Diag Manager UI provides an explanation of all the Custom Diagnostics options when you click on them.
