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 codeplex.com 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

SQLNexus
http://www.codeplex.com/sqlnexus

SQLDiag (installed default with SQL2005 and SQL2008)
http://msdn.microsoft.com/en-us/library/ms162833(SQL.90).aspx

PSSDiag (to use with SQL2000)
http://msdn.microsoft.com/en-us/library/aa175399(SQL.80).aspx

RML Utilities
http://blogs.msdn.com/psssql/archive/2007/12/18/rml-utilities-for-
microsoft-sql-server-released.aspx

Note:

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

http://sqlnexus.codeplex.com/Wiki/Info.aspx?title=Sql2005PerfStatsScript&action=Info

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

media_1298546712526.png

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

media_1298547186028.png

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

Note:

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

media_1298548162430.png

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

media_1298548209909.png

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

media_1298548801751.png

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

media_1298549191017.png

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

media_1298549212058.png

SQL Server 2005 Perf Stats Report

media_1298549232810.png

SQL Server 2005 Bottleneck Analysis Report

media_1298549255240.png

Top Unique Batches Report

media_1298549272625.png
Advertisements