microsoft / sqlnexus

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 SQL LogScout, SQLDiag or PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

What is SQL Nexus?

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 SQL LogScout or PSSDIAG. It can dramatically reduce the amount of time you spend manually analyzing data. Visit Getting Started page.

Latest release

Current release is 7.24.02.18. Please go to latest release to download latest build of SQL Nexus.

Feature Highlights

1. Fast, easy data loading : You can quickly and easily load SQL Trace files; T-SQL script output, including SQL DMV queries; and Performance Monitor logs into a SQL Server database for analysis. All three facilities use bulk load APIs to insert data quickly. You can also create your own importer for a custom file type.
2. Visualize loaded data via reports : Once the data is loaded, you can fire up several different charts and reports to analyze it.
3. Trace aggregation to show the TOP N most expensive queries (using RML).
4. Wait stats analysis for visualizing blocking and other resource contention issues ( based on pssdiag).
5. Full-featured reporting engine : SQL Nexus uses the SQL Server Reporting Services client-side report viewer (it does not require an RS instance). You can create reports for Nexus from either the RS report designer or the Visual Studio report designer. You can also modify the reports that ship with Nexus using either facility. Zoom in/Zoom out to view server performance during a particular time window. Expand/collapse report regions (subreports) for easier navigation of complex data. Export or email reports directly from SQL Nexus. Nexus supports exporting in Excel, PDF, and several other formats.
6. Extensibility : You can use the existing importers to load the output from any DMV query into a table, and any RS reports you drop in the Reports folder will automatically show up in the reports task pane. If you want, you can even add a new data importer for a new data type. SQL Nexus will automatically "fix up" the database references in your reports to reference the current server and database, and it will provide generic parameter prompting for any parameters your reports support.

Common Tasks

1. How To Use SQL Nexus
2. How to Videos
3. Frequently asked questions(FAQ)
4. Installation
5. Sqldiag data collection templates including performance scripts
6. RML Utility/ReadTrace download
7. Top Issues

Microsoft Code of Conduct

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

License

see License.md

More information

More information and help can be found in the wiki

sqlnexus's Issues

system thread shows blank

it uses 5%. it should use 0% and let user decides

warn if attention occurs before blocking

select distinct session from readtrace.tblInterestingEvents evt join vw_HEAD_BLOCKER_SUMMARY bloc on evt.Session=bloc.head_blocker_session_id
where eventid = 16 and evt.StartTime < bloc.runtime

Correct the Requirements links on insttalation page

The Requirements links on the installation page are going to SQL 2012 components and to run SQLNexus 6 you'll need SQL 2016 components.

sql Nexus run time is incorect

db Jason

Perf Stats Scripts missing

I noticed that the data collectors don't include the perf stats script, is this available anywhere on GitHub?

Unhandled exception while running sqlnexus.exe (jackli)

Tested on:
Windows 10
Windows Server 2012 R2

Installed
SQLSysClrTypes.msi
ReportViewer.msi
RMLSetup_AMD64.msi

After running sqlnexus.exe I see this exception:

checkpoint files grow large

https://support.microsoft.com/en-us/help/3206584/fix-checkpoint-files-grow-excessively-when-you-insert-data-into-memory-optimized-tables-in-sql-server-2016

Nexus is incorrectly saying there is no blocking occurring when there’s clearly blocking [Masha]

See email from Masha on 2017-10-12

Nexus report times out at 60sec

Prashant

change Nexus recovery to simple

from Joseph and Todd

TempDB Collector missing (masha)

In the older versions of PSSDiag, we had a TempDB collector to help us track TempDB growth over time. I'm no longer seeing that as an option... Is that expected?

spinlock report blank

main report version is incorrect

add token perm cache

"access bucket count" for 2008 R2 or below

main report fails when some customers doesn't capture --ServerProperty--

usp_Non_SQL_CPU_consumption: negative number of CPUs

The total number of CPU is -1 which lead to false negative.

add rule for virtual bytes fragementation

virtual bytes fragementation can cause performance issues. we can look at perfmon counter and identify the issue

MSINFO32 not being captured, even though it's configured to do so (masha)

console log is showing that we can't find get_msinfo.cmd but it's there :(

Add parameter to minimize SQL Nexus window in console mode (timchap)

RML Utilities for SQL2016 and SQL2017

Hi,

currently there is no public RML utilities available for SQL Server 2016 and newer (I can use SQLNexus only for SQL Server version 2014 or older). Can you public a new version of them where we can use also profiler/xperf traces for SQL Server 2016 or later?

Thanks,
Octavian

change table query can cause high CPU

https://support.microsoft.com/en-us/help/3188672/cpu-usage-increases-significantly-when-you-execute-queries-that-contain-changetable-functions-in-sql-server-2012-service-pack-3

System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

OS: Windows 2016 Server Standard Edition
SQL server: SQL server 2016 Standard Edition

Click "Open readrace log", it show the error dialog box with detail error message. Any idea? Thanks!
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args)
at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args)
at NexusInterfaces.Util.OpenFile(String fileName, String ErrorMessage) in C:\Users\jackli\Documents\GitHub\Sqlnexus\NexusInterfaces\Classes.cs:line 35
at System.Windows.Forms.LinkLabel.OnLinkClicked(LinkLabelLinkClickedEventArgs e)
at System.Windows.Forms.LinkLabel.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.Label.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2110.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/mscorlib.dll

sqlnexus
Assembly Version: 6.0.0.8
Win32 Version: 6.0.0.8
CodeBase: file:///C:/Temp/SQLNexus/SQLNexus.6.0.0.8/sqlnexus.exe

System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2106.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll

System
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2110.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll

System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll

NexusInterfaces
Assembly Version: 6.0.0.8
Win32 Version: 6.0.0.8
CodeBase: file:///C:/Temp/SQLNexus/SQLNexus.6.0.0.8/NexusInterfaces.DLL

Microsoft.ReportViewer.WinForms
Assembly Version: 13.0.0.0
Win32 Version: 13.0.1601.5
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.ReportViewer.WinForms/13.0.0.0__89845dcd8080cc91/Microsoft.ReportViewer.WinForms.dll

System.Data
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2102.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_64/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll

System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2106.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll

System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2102.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll

System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll

System.ServiceProcess
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.ServiceProcess/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.ServiceProcess.dll

Microsoft.ReportViewer.Common
Assembly Version: 13.0.0.0
Win32 Version: 13.0.1601.5
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.ReportViewer.Common/13.0.0.0__89845dcd8080cc91/Microsoft.ReportViewer.Common.dll

Accessibility
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Accessibility/v4.0_4.0.0.0__b03f5f7f11d50a3a/Accessibility.dll

System.Deployment
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Deployment/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Deployment.dll

System.Transactions
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_64/System.Transactions/v4.0_4.0.0.0__b77a5c561934e089/System.Transactions.dll

System.EnterpriseServices
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_64/System.EnterpriseServices/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll

System.Numerics
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Numerics/v4.0_4.0.0.0__b77a5c561934e089/System.Numerics.dll

Microsoft.VisualBasic
Assembly Version: 10.0.0.0
Win32 Version: 14.7.2053.0 built by: NET47REL1
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualBasic/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll

Microsoft.SqlServer.Types
Assembly Version: 13.0.0.0
Win32 Version: 2015.0130.1601.05 ((SQL16_RTM).160429-2226)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.Types/13.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.Types.dll

Microsoft.ReportViewer.ProcessingObjectModel
Assembly Version: 13.0.0.0
Win32 Version: 13.0.1601.5
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.ReportViewer.ProcessingObjectModel/13.0.0.0__89845dcd8080cc91/Microsoft.ReportViewer.ProcessingObjectModel.dll

************** JIT Debugging **************

add redo rule for AG

Adding time for discussing case with several folks including Jack and Masha.

Going directly to the PerfStats file does SHOW the exact PageLatch waits that was missing in Nexus.

runtime os_thread_id session_id request_id start_time status command database_id wait_type wait_time wait_resource scheduler_id task_address reads writes logical_reads text_size language

---

2017-01-28T17:04:37.530 12272 35 0 2017-01-25 17:08:43.533 background DB STARTUP 5 PAGELATCH_EX 329 5:51:3339590 70 0x00000000047DACA8 50746395 67640305 750594552 4096 us_english
2017-01-28T17:05:40.427 12272 35 0 2017-01-25 17:08:43.533 background DB STARTUP 5 PAGELATCH_EX 62906 5:51:3339590 70 0x00000000047DACA8 50746395 67640305 750594552 4096 us_english
2017-01-28T17:06:43.033 12272 35 0 2017-01-25 17:08:43.533 background DB STARTUP 5 PAGELATCH_EX 125567 5:51:3339590 70 0x00000000047DACA8 50746395 67640305 750594552 4096 us_english
2017-01-28T17:07:45.663 12272 35 0 2017-01-25 17:08:43.533 background DB STARTUP 5 PAGELATCH_EX 188187 5:51:3339590 70 0x00000000047DACA8 50746395 67640305 750594552 4096 us_english
2017-01-28T17:08:48.267 12272 35 0 2017-01-25 17:08:43.533 background DB STARTUP 5 PAGELATCH_EX 250644 5:51:3339590 70 0x00000000047DACA8 50746395 67640305 750594552 4096 us_english

rules on Latch_Suspend_End

https://support.microsoft.com/en-us/help/4019446

SQL Nexus 6.0 references ReportViewer 13 issue

SQL Nexus 6 when you run the application. One gets the following error Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=13.0.0.0,'

Online I was able to find ReportViewer 11 and ReportViewer 12. Do you know how I can get my hands on a reportviewer.msi for version 13? If I uninstall previous versions of ReportViewer and then run SQL Nexus 5.5 I get the same error except different Version which if I recall was 11. Now when I reinstalled that version I had no issue. I looked on CodePlex and Github looking to see if there are references to
a new version of reportviewer to no avail.

x86 pssdiag doesn't work on win 2008

from Masha

provide warning if both .xel and .trc files exist

from Anita

'tbl_REQUESTS has dup column

estimated_completion_time bigint null,

SQL authentication not used for readtrace

Tried using SqlNexus to import files from pssdiag, but it fails login with sql authentication. Looks like it's defaulting to windows authentication. All other files look like they were imported. Only the pssdiag.xel files failed.

including additon info for database (Chirag)

Database Size (and Used Space)
Auto Growth Settings for data and log files
Database Compatibility Level
VLF count

warn on sql sentry

sql sentry tracing may cause perf issue

version 6.6.0.8 for rlease

warn if there is a gap in perf stats script

some times, there are many minutes gap in perf stats run, we should warn this to alert user

clearer instructions on small sampled stats rule

Pual,
I added more instruction in the rule. For next release, I will include this.

integrate power bi

tbl_dm_db_stats_properties not loaded

tbl_dm_db_stats_properties is not loaded due to a typo.

perfmon report depends on tbl_xpmsver

rowset should increase flush size

currently, bulkcopy flushes every 2000. this should increase to 50000 to increase performance

Average System CPU Utilization should be Average System Idle CPU Utilization:

IF AND OBJECT_ID ('tbl_SYSPERFINFO') IS NULL is incorrect

IF AND OBJECT_ID ('tbl_SYSPERFINFO') IS NULL

Bug: usp_Non_SQL_CPU_consumption has duplicated time counters

usp_Non_SQL_CPU_consumption Stored Procedure ran for 5.5 hours, the root causes are:

1. when building the timeline, multi instance could generate duplicate time counters which lead to long execution time
2. Looping logic using cursor

Data Collector

The tools has changed a little bit so bear with me...

I downloaded from the main page and navigated to the "data collectors" folder

I updated the "StartSQLDiagDetailed_Trace2014.cmd" file to find the path of the sqldiag.exe location on the server.

I ran the command StartSQLDiagDetailed_Trace2014.cmd from a cmd window and got the following error...

2017/10/19 08:47:55.12 SQLDIAG Configuration file H:\SqlNexus-master\SqlNexus-ma
ster\DataCollectors\Diag2014\sqldiag_detailed.xml does not exist

In the old days, you downloaded the Perf scipts, update your batch file to the sqldiag location, and updated the xml file to specify a sql instance if needed.

What am I missing? All the old sql perf files and the xml files are absent from the "data collectors" folder

Thx

active CPU consuming queries have dups

from jack

usp_Non_SQL_CPU_consumption taking too much time

perf\Blocking_Attention scenario

the new memory errors output is also failing to import [Masha]

See email from Masha on 2017-10-13

not all trace flags apply to all versions

some trace flags such as 8048 doesn't apply SQL 2016. we need to remove those

tbl_ThreadStats querytext needs to be varchar(max)

change

Adding column for 'Application Name' to readtrace.tblbatches (masha)

This would be a useful column to add to the table upon import, if possible, as it can help us a lot with troubleshooting performance issues coming from specific applications.

Timestamp issues when importing PSSDIAG using SQL Nexus [Masha]

As you know, xEvents convert event time field to local time zone of computer that is importing the data. so when we import both PerfStats and xEvents captured from pssdiag, our timestamps don’t match, which makes it pretty difficult to troubleshoot correlative data. Is there any way we can like… force SQL Nexus to change the time zone of the readtrace data to whatever timezone the perfstats file is in (from the customer server timezone)?

TF 1222 can cause blocking last longer

https://blogs.msdn.microsoft.com/bobsql/2017/05/23/how-it-works-sql-server-deadlock-trace-flag-1222-output/