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:
- Windows performance logs
- Windows Event logs
- SQL Server Profiler traces
- SQL Server blocking information
- 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.
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.
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:
- SqlDiagCollector = indicates whether the SqlDiagCollector should collect information based on the below mentioned configuration or not
- BlockingCollector = indicates whether SqlDiag should collect blocking related information or not
- ProfilerCollector = indicates whether SqlDiag should collect Profiler trace information or not
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.
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).
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. |
 |
|
Authentication page Choose the machine name and SQL Server instance name to work with. |
 |
| Machine Wide Diagnostics
Choose whether or not Event Log information is to be captured |
 |
| Configure instance specific settings |  |
| 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 |
 |
| Perfmon Options
Configure the Perfmon counters that are required to be captured. Again, we have 1 pre-defined template to help us get started. |
 |
| Enable Producers |  |
| Enable Analyzers |  |
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:
- SQLDiag Utility: http://msdn.microsoft.com/en-us/library/ms162833.aspx
- SQLDiag Configuration Tool: http://sdct.codeplex.com/
- Adapting SQLDiag.xml for custom logging: http://blogs.msdn.com/b/deviations/archive/2009/01/20/adapting-sqldiag-to-your-needs.aspx
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
SQLTwins by Nakul Vachhrajani 