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

General Custom Diagnostics
The SQL Base custom collector is used to add tasks that should be executed for every type of SQL
Server data collection. This custom collector runs before any other custom collector task. One of the
group's primary purposes is to dei ne global parameters that may be of general use in other task groups.
This collector also collects basic coni guration information from the registry, system coni guration
information, the status of the PAE switch, and the trace l ags active on the target SQL Server instance.

All the output i les have the prei x “ SERVER_SQL_Base _ * .”

The MSINFO custom collector collects the msinfo32 output from the target machine.

The SQL Best Practices custom collector collects outputs of various scripts to check the following:

➤

NTFS compression on SQL Server database i les

➤

Hypothetical indexes on the databases (if any)

➤

Use of the / 3GB and / PAE switch in the BOOT.INI i le

Information about all the SQL Server Proi ler traces active on the target SQL Server
instance
All the output i les have the prei x “ SERVER_SQL_Best_Practices_* .”

➤

SQL 2008 Perf Stats
The most common customization that SQL Server professionals and Microsoft engineers add to
SQLdiag is to collect SQL Server blocking diagnostics. You might already be familiar with the con-
i guration option to capture blocking information. This information can be collected using the SQL
2008 Perf Stats custom diagnostic option. This section describes why this custom diagnostic is not
just about collecting blocking information on your SQL Server instance as seen below. Note that the
scripts available in the download section work for SQL Server 2012 instances as well.

However, if you intend to capture the SQL Perf Stats script manually, then you need to get the latest
Perf Stats script, available from CodePlex at http://sqlnexus.codeplex.com/
wikipage?title=Sql2005PerfStatsScript&ProjectName=sqlnexus . After downloading it,
follow these steps:

1. Click Page Info from the navigation bar.

2.

Scroll down to locate the File Attachments section.

3. Locate PerfStatsScript2008R2.zip and download the i le.

4.

After extracting the zip i le, you will i nd the i les shown in Figure 11-7.

FIGURE 11-7