SQL Nexus

 Executive Summary:

SQL Nexus is a powerful front-end visualization tool for detecting, analyzing, and troubleshooting problems on SQL Server 2005. It uses SQLdiag on the back end as its main data collector. (For more information about SQLdiag, see Tool Time, "SQLdiag," March 2007, InstantDoc ID 94853.)

SQL Nexus is extensible. Not only does it let you run several useful built-in reports, it also lets you customize existing reports and write your own. The tool uses SQL Server Reporting Services (SSRS) to generate reports and charts from the diagnostic data it collects. SQL Nexus then uses SQL Server to collect and aggregate diagnostic information into a data warehouse; you can use this information for long-term diagnostics and performance analysis.

Functionality

After you download and install SQL Nexus, you can get useful information by running SQLdiag as a service, called diag$sqlnexus, and importing the performance data and trace files. The SQLdiag service is controlled through SQL Nexus's Service toolbar or by SQLdiag directly.

The exact diagnostic data that SQLdiag collects is determined by an XML configuration file that's passed to SQLdiag when it's registered. This file is stored in the Collection subfolder under the SQL Nexus startup folder.

You can run SQL Nexus in real-time mode or postmortem mode. Real-time mode shows you data as it's collected and periodically refreshes the various reports. Postmortem mode treats the data as static and doesn't automatically refresh.

SQLNexus has four main features, each of which reveals a great deal about the performance of your SQL Server instance.

1. Current Server Status: This feature provides the current status of your SQL Server machine. As Web Figure 1 (http://www.sqlmag.com, InstantDoc ID 96774) shows, this screen graphically details CPU utilization and memory utilization. It also provides textual breakouts of database I/O statistics, system warnings, and currently active queries.
2. Bottleneck Analysis: As Web Figures 2 and 3 show, this option shows the major consumers of various system resources, as well as which system components are generating the most waits.
3. Blocking and Wait Statistics: If the Bottleneck Analysis reveals that blocking is the problem, you can use this feature to dive deeper into any existing blocking chains, as Web Figure 4 shows.
4. Profiler Trace Analysis: This option tracks the most expensive SQL queries on your server and provides a graph that details CPU consumption, batch completion, reads, writes, and query duration, as Web Figure 5 shows.

SQL Nexus has built-in functionality to export its reports to most common formats, such as Microsoft Excel, PDF, and several image types. You can also email SQL Nexus data as Excel spreadsheets. And finally, you can use SSRS or Microsoft Visual Studio to customize or even write all new reports for SQL Nexus.

System Requirements

SQL Nexus requires two main components. SQL Nexus itself is the main front-end visualization tool; SQLdiag is the main back-end data collection tool.

You need to have a fairly large amount of free disk space available on your workstation or server, because some diagnostic data (e.g., a Profiler trace file) is collected on your local hard drive, then loaded onto the SQL Server machine after the fact. Although SQL Nexus deletes older data files as it loads new ones, you still need sufficient disk space to store a reasonable number of trace (.trc) files until cleanup can occur. The amount of disk space that's necessary varies widely, based on server load.

SQL Nexus was the brainchild of Ken Henderson and Bart Duncan back when they were both with Microsoft's SQL Server Product Support Services (PSS). You can download SQL Nexus from the official Web site, at http://www.sqlnexus.net. You can download the SQL Server 2005 Performance Statistics Script files, upon which the SQL Nexus reports are based, from Bart Duncan's February 21, 2007, Microsoft PSS SQL Support blog entry; go to http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx and click the zip file at the bottom of the entry. And of course, we want to hear your feedback on the Tool Time discussion forum at http://www.sqlmag.com/go/tooltime.