I had been waiting for Diag Manager to be made public so that this post could go online. Diag Manager is a configuration tool that is used by CSS Engineers to configure a consolidated data collection for Windows Perfmon counters and SQL Server profiler trace (server-side). Additionally, the tool lets you add pre-configured custom diagnostics for Analysis Services configuration data, Mirroring configuration, Event log collection etc. and even custom data collection (using .sql scripts/custom utilities) which can be added using the Custom Diagnostics-> _MyCollectors option. (UI Screenshot below)

The tip that I want to talk about with this tool is that .CAB file generated uses a PSSDIAG.XML configuration file when using SQLDIAG to collect data. If you need to collect data from multiple different instances using the same configuration file, then you can just use the same extracted .CAB file contents with small tweaks to the SQLDIAG.XML file.

Changing the machine name from which the data needs to be collected: Change the machine name to the appropriate machine name.

<Machine name="MyLaptop">

Changing the instance name from which the data needs to be collected: Change the instance name value to the appropriate SQL Server instance name. MSSQLSERVER as instance name for default instance.

<Instance name="INST1" windowsauth="true" ssver="10" user="">

Disabling/Enabling perfmon data collection: Change the enabled option to true to enable perfmon data collection or false to disable collection. The maxfilesize parameter controls the perfmon file rollover size.

<PerfmonCollector enabled="true" pollinginterval="5" maxfilesize="256">

Changing the profiler trace collection and the rollover file size: Change the enabled value to true/false to enable/disable profiler data collection. Changing the maxfilesize parameter value controls the rollover file size for the profiler traces.

<ProfilerCollector enabled="true" template="_GeneralPerformance90.xml" pollinginterval="5" maxfilesize="350">

Collecting SQLDIAG output during startup/shutdown: This can be controlled using the enabled parameter value and the startup parameter value determines if the SQLDIAG output is to be collected during PSSDIAG startup/initialization. The shutdown parameter controls whether a SQLDIAG output needs to be captured once PSSDIAG collection is signaled to be stopped using CTRL+C.

<SqldiagCollector enabled="true" startup="false" shutdown="true" />

The other option that you have now that you have a GUI is to using Diag Manager to configure a new package for your target machine and click on Save. You will notice in the Save Configuration window, you have two locations that can be modified, one for the PSSDIAG.XML file and one for the .CAB file. Generate a new PSSDIAG.XML file and replace the existing file from a previously configured PSSDIAG with the newly generated PSSDIAG.XML for your new settings to take effect when the PSSDIAG launched again.

Another option that engineers use regularly for a long term PSSDIAG collection is the “Delete Old Trace Files” custom diagnostics. This is an option that is used for long term data collection. More information about this custom diagnostic is available in the FAQs section on Codeplex.

Note: The XML configuration for SQL Server 2008 and 2008 R2 can be mixed and matched using the ssver=”10.5” for SQL Server 2008 R2 instances and ssver=”10” for SQL Server 2008 instances. However, you shouldn’t use the same configured package across different platforms (x86/x64/ia64) and nor should you use the same package for different releases of SQL Server (2005/2008/2008 R2).

We have also documented some Common Issues faced while collecting data using a configured package. In case you have a question regarding the tool, feel free to post a new discussion. In case you find a defect that you need to report, you can log the defect under the Issue Tracker section.

Blog post talking about the release: http://blogs.msdn.com/b/psssql/archive/2011/05/24/pssdiag-sqldiag-configuration-manager-released-to-codeplex.aspx

This is a widely used tool in Microsoft Services for consolidated data collection while working on SQL Server performance issues. In case you have any feedback, please feel free to provide the same on the discussion forum for the tool on Codeplex.