#0142 – SQL Server – Profiler – Part 09 – Introducing the SQLDiag utility to capture SQL and Performance traces


In one of the earlier parts of this tutorial series (Part 06), I had demonstrated how to correlate the SQL Profiler trace with the Windows Performance Logs. I had used two utilities for the demonstration – the SQL Server Profiler and the Windows Reliability & Performance Monitor. In Part 07 and Part 08 of the tutorial series, I demonstrated how to generate and run a server side trace.

Today, I will demonstrate how to generate a server side SQL Trace combined with the Windows performance logs using a single diagnostic utility – SQLDiag.The SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. The SQLDiag utility can capture the following kinds of diagnostic information:

  1. Windows performance logs
  2. Windows Event logs
  3. SQL Server Profiler traces
  4. SQL Server blocking information
  5. SQL Server configuration information

SQLDiag is a command-line utility, and therefore choosing the correct switches is important when working with SQLDiag.

Permissions

Under it’s default mode, because SQLDiag collects SQLTrace information, it needs to be run under a user context that has sysadmin access to the SQL Server instance being monitored. Also, because it collects windows performance & event log information, the user must have Administrator access to the server being monitored.

SQLDiag can also be run under a “Generic” mode, with a /G command line argument. Under the /G switch, SQLDiag does not enforce SQL Server connectivity constraints or verify whether a user can capture the required windows logs. These checks are deferred to the SQL Server and the Windows Operating System respectively.

Please note that by default, SQLDiag connects to the SQL Server instance using Windows authentication.

Location

By default, the SQLDiag utility is located at the following paths:

  • SQL Server 2008: ..Microsoft SQL Server100ToolsBinnSQLDiag.exe
  • SQL Server 2012: ..Microsoft SQL Server110ToolsBinnSQLDiag.exe

Configuring the SQLDiag

SQLDiag collects SQL Trace and windows logs by using a configuration file as input. This configuration file is an XML file, with a default name of – SQLDiag.xml. This XML is not present by default in a clean installation of SQL Server. To generate the XML for the first time, simply run SQLDiag.exe on the command prompt under a user who has sysadmin and system administrative privileges. As soon as execution starts, press Ctrl+C to stop the execution. The SQLDiag.xml would have been generated in the SQLDiag’s launch folder.

image

Along with the SQLDiag.xml, associated XSD is also generated that would help in validating the changes to the xml file using an XML editor such as the SSMS.

The SQLDiag.xml is created under a default configuration. In most cases, this file requires to be edited so that the user can configure the SQL trace and the windows performance log capture.

Manually Editing the SQLDiag.xml file

To edit the SQLDiag.xml, I would always recommend to use an XML editor (the fastest option being to use the SSMS). Allow me to walk-you through the XML structure.

Configuring the machine and instance names

The first thing that needs to be done is to configure the file to point to the correct machine and associated SQL Server instance names. Under the “Machines” node, one can find the “Machine” and the “Instances” nodes. This indicates that SQLDiag can be used to capture:

  • Windows performance data from more than one machine at the same time
  • SQL Trace data from more than one SQL Server instance on each of these machines at the same time

By default only one “Machine” node exists, pointing to the local server. Similarly, only one “Instance” node exists, pointing to the default local instance of SQL Server, the version for which is indicated by the “ssver” attribute.

Word of caution: Because the SQLDiag has the potential to capture a large number of data points, please keep an eye out for IO, memory & CPU pressure. An incorrectly configured SQLDiag file can do more harm than good.

image

Observe that under the instance node, one can not only change the SQL Server instance that is to be monitored, but also whether or not to use Windows authentication, what the SQL Server version of the instance is and the user name in case the user is using SQL authentication.

Configuring the SQL Trace options

Now that I have specified the machine name and the instance name which needs to be monitored, it is time to configure the SQL Trace options. This will be similar to the configuration I did for the server-side tracing (Part 07) – the only difference is that for the standard server side tracing we used T-SQL whereas here we will be using XML. I had to specify the event class, event itself and the columns that needed to be captured. In the T-SQL approach, remembering the enumerated values for each event class and column names was a headache. Here, things are a little better – I already have a list of all event classes & events that are available and I only need to choose whether or not to capture this information. All columns available to an event are captured, because they can be filtered later on by the user.

Under the “Instances” node, expand the “Collectors” node. This would show us the following:

  1. SqlDiagCollector = indicates whether the SqlDiagCollector should collect information based on the below mentioned configuration or not
  2. BlockingCollector = indicates whether SqlDiag should collect blocking related information or not
  3. ProfilerCollector = indicates whether SqlDiag should collect Profiler trace information or not

image

   1:  <Collectors>
   2:      <SqldiagCollector enabled="true" startup="false" shutdown="true" />
   3:      <BlockingCollector enabled="false" pollinginterval="5" maxfilesize="350" filecount="1"/>
   4:      <ProfilerCollector enabled="false" template="_GeneralPerformance90.xml" pollinginterval="5" maxfilesize="350" filecount="1">
   5:          <Events>
   6:              <!--Event Classes here-->
   7:              <EventType name="Deprecation" enabled="false">
   8:                  <Event id="125" name="Deprecation Announcement"  enabled="false" description="Occurs when you use a feature that will be removed from a future version of SQL Server." />
   9:                  <Event id="126" name="Deprecation Final Support"  enabled="false" description="Occurs when you use a feature that will be removed from the next major release of SQL Server." />
  10:              </EventType>
  11:              <!--Event Classes here-->
  12:          </Events>
  13:      </ProfilerCollector>
  14:  </Collectors>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

To enable/disable a particular trace (e.g. ProfilerCollector) and/or a particular event class/event, simply change the “enabled="false"” to “enabled="true"”. Do ensure that if you want to trace a Profiler event, the ProfilerCollector, the associated Event Class and the associated Event should all have “enabled=”true”” set appropriately.

Configuring the Event Log collector

SQLDiag has the ability to monitor the Windows event log and capture appropriate data from it accordingly. To configure Event Log monitoring and collection, expand the “Machine Collectors” node.

image

   1:            <EventlogCollector enabled="false" startup="false" shutdown="true" >
   2:              <Eventlogs>
   3:                <EventlogType name="Application" enabled="true"/>
   4:                <EventlogType name="Security" enabled="true"/>
   5:                <EventlogType name="System" enabled="true"/>
   6:              </Eventlogs>
   7:            </EventlogCollector>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

As mentioned above, changing the “enabled=”true”” to “enabled=”false”” will stop the collection of these event logs.

Configuring the PerfMon Collector

Expanding the “PerfmonCollector” node gives us the “PerfmonCounters” node, which contains a listing of every available performance counter. Working with the “enabled” attribute allows us to control whether or not a particular counter is to be monitored or not.

Do note that this is a list of all counters available under “PerfMon”, had I launched it externally (as I did in Part 06 of the tutorial).

image

Here’s a snippet:

   1:  <PerfmonCollector enabled="false" pollinginterval="5" maxfilesize="256">
   2:      <PerfmonCounters>
   3:          <!--PerfmonObject nodes here-->
   4:          <PerfmonObject name="MSSQL$%s:Buffer Partition(*)" enabled="true">
   5:              <PerfmonCounter name="*" enabled="true" />
   6:              <PerfmonCounter name="Free pages" enabled="true" />
   7:              <PerfmonCounter name="Free list requests/sec" enabled="true" />
   8:              <PerfmonCounter name="Free list empty/sec" enabled="true" />
   9:          </PerfmonObject>
  10:          <!--PerfmonObject nodes here-->
  11:      </PerfmonCounters>
  12:  </PerfmonCollector>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Once the file has been edited, we are ready to begin execution of the SqlDiag utility, however, we will see how to run SqlDiag in the next part of the tutorial.

Editing the SQLDiag.xml file through a GUI

Manually editing the SQLDiag.xml is a very tedious and error-prone task. The SQL Diag Configuration Tool (SDCT) from Codeplex is an intuitive, easy to use tool that will read existing sqldiag.xml configuration files, allow you to select your desired options and save those options to a new file.

The only limitation of this tool is that because SQL 2012 has not been released at the time of writing this blog, the tool does not support the SQLDiag file for SQL2012. (Because CodePlex is an open source community, those interested in open source development can take it up as an action item to work upon).

Welcome Screen

Simply choose the version which the SqlDiag.xml needs to work with.

image 

Authentication page

Choose the machine name and SQL Server instance name to work with.

image
Machine Wide Diagnostics

Choose whether or not Event Log information is to be captured

image
Configure instance specific settings

image
Profiler Options

Configure the SQL trace. Notice that the system provides 3 templates and allows to configure:

1. Maximum file size

2. Polling Interval

3. Maximum number of files required

image
Perfmon Options

Configure the Perfmon counters that are required to be captured. Again, we have 1 pre-defined template to help us get started.

image
Enable Producers image
Enable Analyzers image

After configuring all options using this simple GUI based tool, simply click on “Save” to save a fully-configured copy of the SqlDiag.xml file.

In the next part of the tutorial:

The next part of the tutorial is the final part in the “Profiler” series. We will see how to execute the SQLDiag utility and how to consume it’s output with the SQL Profiler. In addition, I will also be providing some more tools that improve upon the functionality and flexibility provided by SQLDiag.

References:

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s