Tool to help you analyze SQL Server SQLDIAG and PSSDIAG output

By: Matteo Lorini   |   Updated: 2009-12-11   |   Comments (8)   |   Related: > Performance Tuning

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

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.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips