Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Tool to help you analyze SQL Server SQLDIAG and PSSDIAG output


By:   |   Read Comments (7)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem
SQLDIAG or PSSDIAG are excellent tools used to collect and analyze data in order to identify possible performance root causes. The issue is that the output produced by such utilities is sometimes convoluted, hard to read and hard to understand. In this tip, I will show you a tool that you can use to read and analyze the data collected by these tools.

Solution
SQL Nexus is a tool that helps troubleshoot and identify root causes of SQL Server performance issues. It is used in conjunction with SQLDIAG or PSSDIAG to load and analyze performance data. The main advantage is that SQL Nexus can dramatically reduce the amount of time spent analyzing the data.

In this article I will quickly explain how to use SQL Nexus with an example. If requested, I will provide further detail and explanation regarding the usage of SQL Nexus.


To get started, download a free copy of SQL Nexus from http://sqlnexus.codeplex.com/ and follow the installation instructions. Make sure that ReadTrace is installed otherwise, SQL Nexus will not work correctly. ReadTrace can be found at: http://sqlnexus.codeplex.com/wikipage?title=ReadTrace&referringTitle=Installation

It is important to understand that SQL Nexus does not collect data, it only analyzes data collected by SQLDIAG or PSSDIAG. The best and most effective time to collect data is when the server is experiencing latency, slowness or issues. Data collected during good server performance or not at peak loads may be meaningless.


In this example, I will create a simple blocking situation, collect 15 minutes of SQL Server data using SQLDIAG and display the blocking situation with SQL Nexus.

Please note that in order to protect the Clients information, SQL Server named client information has been erased from all the screenshots.

To create a blocking scenario I have created the following table

create table dummytest ( 
ClientID int primary key, 
ClientName varchar(50), 
LastName varchar(50), 
PhoneNum varchar(50) 
) 

and run the following queries in multiple query windows

--Query window 1 
begin transaction 
insert dummytest values (2,'due','due','1234') 
--Query window 2
update dummytest set PhoneNum = '33' 
--Query window 3
select * from dummytest 

From the DOS command prompt I started SQLDIAG and instructed it to collect 15 minutes of data using the below command.

SQLDIAG  /I "%cd%\SQLDiagPerfStats_Detailed_Trace2008.XML" /O "%cd%\SQLDiagOutput" /P /E +00:15:00

Template SQLDiagPerfStats_Trace2008.XML used by SQLDIAG can be downloaded from http://sqlnexus.codeplex.com/wikipage?title=Sql2005PerfStatsScript&referringTitle=GETTING_STARTED

The information collected by SQLDIAG is saved on the specified output folder /O "%cd%\SQLDiagOutput

To better appreciate what SQL Nexus is capable of doing, try to manually open and analyze the contents of SQL1_sp_sqldiad_Shutdown.OUT file.


Next, start SQL Nexus and upload the collected data.

Upload procedure

The accuracy of SQL Nexus strongly depends on the collected data. Data needs to be collected for at least 15 minutes during poor server performance or latency in order to be able to identify the root cause. Just keep in mind that SQLDIAG is quite intrusive and its usage in a production environment may worsen SQL Server performance.

Once the SQLDIAG data has been uploaded, SQL Nexus has a variety of pre-canned reports that can be executed in order to check the status of your SQL Server. In our example, we can click on the Blocking and Waits Stats reports (see picture below) and see the blocking transactions and the blocking chain.

The top window shows the offending (Blocking) query while the bottom window shows all the queries that have been blocked.

Next Steps

The accuracy of SQL Nexus strongly depends on the collected data. Data needs to be collected for at least 15 minutes during poor server performance or latency in order to be able to identify the root cause. Just keep in mind that SQLDIAG is quite intrusive and its usage in a production environment may worsen SQL Server performance, but sometimes you need to take the hit to collect the data and solve the problem.

Refer to these additional performance related tips.



Last Update:


signup button

next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, June 05, 2012 - 12:01:30 PM - Anwar Back To Top

Hi

i have successfully installed and display report, your article does help to check on blocking, but other reports are hard to interpret, is there a user manual that exists, basically having hard time to understand other reports, want to know more how memory on the server is being used, and who is consuming the most, what SQL Statements are being used etc

Thanks

Anwar


Friday, December 18, 2009 - 5:19:54 PM - dmining06 Back To Top

 I see your problem. Basically there is no installation. Once you have uncompressed the binary file double click on sqlnexus.exe and sql nexus will start.

 

Thanks


Friday, December 18, 2009 - 8:27:10 AM - Dr DBA Back To Top

 Thanks for the insturctions but there seems to be a problem.

  • "click on "Releases" tab of sqlnexus project and download the latest binary files supplied by this project to a local folder (this project is xcopy deployed):"

There is no "Release" tab on the Nexus website. I was able to download the file but there are no instructions within the download on how to actually install.

If you happend to have this information, could you post it?

Thanks for your time and efforts

 

 

 


Friday, December 18, 2009 - 5:38:52 AM - dmining06 Back To Top

   Thanks for your comment. Here are the installation notes for Nexus.

  1. Requirement:
  • Nexus tool requires SQL Server 2005 tools and .NET framework 2.0 (which is part of SQL Server 2005 installation)
  • It also requires a SQL SErver 2005 database engine to load and analyze data.
  • Two configurations will work:
  1. Two sample configurations
  • Configuration 1, you install Nexus to a machine that has both SQL Server 2005 tools and database engine installed.
  • Configuration 2, you install Nexus to a machine that only has SQL Server 2005 tools and connect to a different machine that has SQL Server 2005 engine installed.
  • we don't recommend this tool to point to your production SQL Server.
  1. Installation Steps
  • click on "Releases" tab of sqlnexus project and download the latest binary files supplied by this project to a local folder (this project is xcopy deployed):
  • Install ReadTrace  (http://sqlnexus.codeplex.com/wikipage?title=ReadTrace&referringTitle=Installation)
Nexus can be found at: http://sqlnexus.codeplex.com
 
Thanks

 


Thursday, December 17, 2009 - 10:36:42 AM - Dr DBA Back To Top

 Thanks for the article, it's great!! 

 I have heard of free tool before and it was only on the weekend that I played with it. Found it hard to install, there is no documentation that tells you how to install it. I ran the executeable that I thought would run the tool and it wanted to connect to a SQL server. 

Once I started my SQL server it worked but the reports for block/lock did not work. 

Do you have the installation document? I think it was not installed. Really innterested in using it and comparing it to some 3rd party tools.

It's nice to see that someone actually spent the time do create an excellent article on this tool.

Thanks.

 

 


Friday, December 11, 2009 - 1:00:39 PM - dmining06 Back To Top

You are welcome. If the article about Nexus generates more interest I will try to post more on the subject.


Friday, December 11, 2009 - 9:32:41 AM - aprato Back To Top

 Matteo, thanks for sharing!  I had never heard of SQL Nexus but it looks like a nicce tool to have
in your toolbelt.


Learn more about SQL Server tools