Using SQL Nexus to capture Sql Server Performance Issues

This Tutorial will give you a run through of how you can use the great free tool Sql Nexus from to capture performance data from SQL Server and create a set of reports on how your server is operating

Download Prerequisites

Download the following components on the machine where you wish to carry out the analysis and report creation


SQLDiag (installed default with SQL2005 and SQL2008)

PSSDiag (to use with SQL2000)

RML Utilities


For the machine upon which you need to collect data from you only require the Performance scripts download which can be downloaded from

There is a version of the scripts for Sql Server 2005 and 2008 so download the appropriate version for the Sql Server you wish to investigate

On the Analysis Server Install the Required Components

On the Server upon which you will be analyzing the collected data install the following

RML Utilities for Sql Server
Install Microsoft Report Viewer 2008 Redistributable

Extract the SQL Nexus download to a folder on your File System e.g. C:\sqlnexus

You are now ready to collect data from your server that you wish to investigate

Set Up Data Capture Process on the Sql Server to be tested


We now need to setup the Data Capture on the Sql Server that we will be collecting data from using the Performance Scripts downloaded in a previous step

In this example we will be carrying out a data capture on a SQL Server 2005 instance

Download the Performance Scripts for Sql Server 2005 and extract the zip file to a folder on your file system.In this case i used F:\sqldiag

Within this folder you must create a folder called SQLDiagOutput

Next you must edit the batch file that starts the data collection (StartSQLDiagTrace.cmd) and set the path to the correct values for your SQL Server Installation
Make sure you set the path to the output folder and the path to the Sql Server Tools/binn directory
Save the file

Launch the Data Collection


When you are ready to start the data collection navigate to the folder that contains the StartSQLDiagTrace.cmd via a cmd line and start the batch file

This will create a temporary service called SQLDIAG on the system and start the data collection of a set of Performance Monitor Counters and a Sql Server server-side trace and output the data to the SQLDiagOutput Folder

Allow the data collection to run for sufficient time to collect data when your performance issue occurs


On a busy system quite some data can be collected!!!!. Watch your free space carefully! Have it running for maybe 10 mins and then stop the SQLDiag service.

When you have collected sufficient data run the stop batch file StopSQLDIagtrace.cmd

This batch file will finalize the data collection and remove the service called SQLDIAG

Once the batch file has finished running you can then zip up the contents on the SQLDIAGOutput folder and copy it to your server upon which you have SQL Nexus installed for analysis

Launch SQL Nexus for Analysis of Collected Data


Launch the SQL Nexus Tool by running the binary sqlnexus.exe binary
It will then prompt you for connection information to the SQL Server where you wish to store the collected data and analyze it

Create the Connection to an existing SQL Nexus Database or Create a new one


SQL Nexus uses a standard Sql Server Database to store the imported data from the collection scripts and also the analyzed data and reports
You need to either choose an existing SQL Nexus Database ,using the drop down menu in the top right drop down menu

Import the Collected Data


Click the Import Link on the Left hand side and navigate to the file system location where you have stored the contents of the data collected from the server to be analyzed (Contents of SQLDiagOutput Folder)
Use the Options link to set the options shown in the above screen shot
Then Click the Import button to start the import of the collected data into SQL Nexus and to start the analysis.

Examine the Reports


Clicking on the Reports Link on the Left hand Side we can then examine the various reports that the tool creates
The reports can be exported into an excel sheet (Other report formats are listed but Excel is the only one that works in the current release of SQL Nexus).
Some of the reports are shown below

Blocking and Resource Waits Report


SQL Server 2005 Perf Stats Report


SQL Server 2005 Bottleneck Analysis Report


Top Unique Batches Report