Tool to help you analyze SQL Server SQLDIAG and PSSDIAG output


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

Itís the Database. Or Is It? Put an End to Finger Pointing!

Free MSSQLTips Webinar: Itís the Database. Or Is It? Put an End to Finger Pointing!

During this webinar, we'll show how SolarWinds Server & Application Monitor (SAM) can enable your IT team to monitor the health and availability of your Microsoft SQL Servers. SAM in the hands of your IT teams provides broad visibility across the stack to easily identify the underlying cause for database availability and performance problems.


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 Updated: 2009-12-11


get scripts

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





Comments For This Article




Tuesday, February 19, 2019 - 1:24:39 PM - john xing Back To Top

Hello Matteo,

what are the sqldiag data need to collect in order to identify poosible cause of the issue "cannot to connect remote SQL server with additional information "the target principle name is incorrect. Cannot generate SSPI context (Microsoft SQL Server)". Please note;

  1. Server 1 and server 2 in the same domain,
  2. sql 2016 is installed in both servers.
  3. Default SQL instance SERVER 1/Server 2 (MSSQLSERVER) installed in both server.
  4. a member of domain admin is used to SQL account.
  5. Server 1 is main SQL server and Server 2 is failover,
  6. SQL cannot connect to Server2 (remote) from Server 1.

Thnaks

John


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.



download


Recommended Reading

Different Ways to Flush or Clear SQL Server Cache

Fastest way to Delete Large Number of Records in SQL Server

UPDATE Statement Performance in SQL Server

How to find out how much CPU a SQL Server process is really using

SQL Server stored procedure runs fast in SSMS and slow in application





get free sql tips
agree to terms


Learn more about SQL Server tools