Using SQL Nexus to troubleshoot OpsMgr SQL Server performance issues

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

With the RML Utilities you can answer questions such as the following:

• Which application, database or login is consuming the most resources, and which queries are responsible for that.
• Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed.
• What queries are running slower in today's data as compared to a previous set of data.

If you think you have OpsMgr SQL Server performance issues maybe SQL Nexus together with SQLDiag and RML Utilities can help with performance tuning and analysis.

Steps:

1. Download SQLNexus , RML Utilities for SQL Server, Microsoft Report Viewer 2008 Redistributable and PerfStatsScript from websites.
   
2. Install Microsoft Report Viewer 2008 Redistributable
   
   
3. Install RML Utilities for SQL Server.
   
   
4. Install\Run SQLNexus by running sqlnexus.exe in folder where you extracted the zipfile.
   
5. Connect to SQL server
   
   
6. After starting SQLNexus a new database sqlnexus is created.
   
   
7. Create a folder SQLDiag and Unzip PerfStatsScript files to folder.
   
8. Open StartSQLDiagTrace.cmd and change configuration as needed.
   
9. Give SQL server service account full control on D:\SQLDIAG folder if you aren’t using the local system account.
   
10. Start D:\SQLDiag\StartSQLDiagTrace.cmd. This script registers sqldiag as a service and starts the service with a template (both profiler and perfmon trace)
    Output is being dumped in D:\SQLDiag\SQLDiagOutput folder
    
    
    
    Remark:
    On a busy system quite some data can be collected!!!!. Watch your freespace carefully! Customer experience on opsmgr environment with 2500 agents 20GB of data is collected in 10 minutes.
    Have it running for 10 mins and then stop the SQLDiag service.
    
11. Stop SQLDiag with StopSQLDiagTrace.cmd script.
    
    
    
12. Importing trace data by selecting File\Import in SQLNexus tool.
    
    
    Select Options and select BLG Blaster on Enabled and Drop Current DB before Importing.
    
     
    
    
    
    Remark: If you are getting the Message PerfStatsAnalysis.sql doesn’t exist message. Just copy the PerfStatsAnalyis.sql file to the requested folder.
    
    
    
    
    
13. The Next Reports are available:
    • Via SQLNexus
      1. Bottleneck Analysis
      2. Blocking and Resource Wait Statistics
      3. Performance Overview with subreports
         • Resource Consumption
         • Unique Batch TopN
         • Interesting Events

I want to thank David Scheltens for showing me how to use SQL Nexus and creating the PerfStatsScripts.

References:

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