Collecting Diagnostics

 

SQL Nexus uses SQL Server’s SQLDiag facility to collect diagnostics data for SQL Server itself.  This means that not only is the diagnostics data being collected about SQL Server; it is also being stored by SQL Server in a SQL database.  Naturally, this means that the diagnostics collection should be as unobtrusive and low-impact as possible.  Fortunately, SQLDiag was designed for minimal system impact and maximum performance.

 

When you start SQL Nexus, you are asked whether you would like to start the diagnostics collector for real-time analysis.  This allows you to run reports over diagnostic data as it is being collected.  You can then view charts and reports that use this event data as it occurs.

 

SQL Nexus instructs SQLDiag to run as a service in order to minimize impact to the system and provide for data collection that is as robust as possible.  The service SQLDiag will be registered under is named “diag$sqlnexus.”  You can control this service using the Service toolbar in SQL Nexus or using SQLDiag itself.  For additional information about controlling the SQLDiag collection service using sqldiag.exe, see the SQLdiag Utility topic in SQL Server 2005 Books Online.

 

The diagnostics SQLDiag collects are determined by an XML configuration file that is passed to it when it is registered.  You can find this file in the “Collection” subfolder under the SQL Nexus startup folder.  Although we do not recommend it, it is possible to customize the diagnostics configured in this file.  Note that doing so may render some SQL Nexus reports unusable.

 

Some diagnostics necessarily must be collected first to your local hard drive, then loaded into SQL Server rather than being sent to SQL Server directly.  A good example of this is a Profiler trace.  SQLDiag collects server-side Profiler traces using the sp_trace* family of extended stored procedures.  This means that SQL Server itself writes the TRC files that are later imported by the SQL Nexus data loader and requires that you have sufficient disk space available on the machine on which SQL Server is running in order to store these TRC files, at least temporarily.  The SQL Nexus data loader will load these TRC files into the Diagnostic Data Warehouse on a regular time interval and will delete files it has processed, but there must be sufficient disk space to store them until that occurs.  The exact amount of disk space and the length of time it will need to be available will vary widely between systems based on the activity on the server and the rate at which it can be processed by the SQL Nexus data loader.