How to use SQL Nexus - microsoft/SqlNexus GitHub Wiki

1. Install SQL Nexus and RML Utilities
2. Collect perf data from your SQL Server instance using SQL LogScout.
3. Import and analyze the data using SQL Nexus

• Get the latest version for SQL Nexus on https://github.com/microsoft/SqlNexus/releases
• To install, see Installation page

In order to use SQL Nexus, you will first need to collect diagnostic data. You can use either of the following tools:

• SQL LogScout (recommended) – a modern, lightweight data collection tool for SQL Server on Windows and Linux

SQL Nexus supports importing data from SQL Server 2012 and later, including SQL Server on Linux.

1. Launch sqlnexus.exe and log on to a non-production SQL Server instance where you have SQL Nexus installed.
2. Click on Import in the left pane of the main SQL Nexus window.
3. Provide the folder path where you stored the collected data. Note: provide a directory path, not a file name. 1.SQL Nexus will automatically detect and list the files to import, including:
   • Rowset output files (.out text files from SQLDiag/SQL LogScout)
   • SQL Trace files (.trc) — processed via RML Utilities/ReadTrace
   • PSSDIAG XEL files (*pssdiag*.xel) — processed via RML Utilities/ReadTrace
   • SQL LogScout XEL files (*LogScout*.xel, e.g. xevent_LogScout_target*.xel, SQLLogScout_AlwaysOn_Data_Movement*.xel) — processed via RML Utilities/ReadTrace
   • SQLDiag XEL files (*_SQLDIAG*.xel) — auto-generated by SQL Server on Failover Cluster Instances (FCI) and Always On Availability Groups via sp_server_diagnostics
   • Always On Health XEL files (*AlwaysOn_health*.xel) — built-in SQL Server Always On health session
   • System Health XEL files (*system_health*.xel) — built-in SQL Server system health session
   • Performance Monitor logs (.blg) via Perfmon Importer
   • SQL Server ERRORLOG files (*_ERRORLOG*, e.g. SERVERNAME_ERRORLOG, SERVERNAME_ERRORLOG.1) via ERRORLOG Importer
   • Linux performance data (IO stats, CPU, memory, networking, process
4. Press the Import button.
5. Once the import is finished, click on the reports in the left pane. Start with the Bottleneck Analysis report to determine where most query execution time was spent.

When you click the Options button on the Import page, a dropdown menu appears with an Importers submenu. Each importer has configurable options that control its behavior during import. You can enable or disable individual importers and fine-tune their settings.

Processes Extended Events files (*pssdiag*.xel, *LogScout*.xel) using the managed TraceEvent library. This is the recommended importer for XEL files.

Processes trace files using the ReadTrace utility from RML Utilities. This importer requires ReadTrace.exe to be installed. Note: Only one of Trace Event Importer (Managed) or ReadTrace can be enabled at a time — enabling one automatically disables the other.

Imports SQL Server ERRORLOG files (*_ERRORLOG*).

Imports Windows Performance Monitor binary log files (*.BLG).

Imports Linux performance data files (*.perf) collected from SQL Server on Linux.

Imports text-based rowset output files (*.OUT, *.TXT) generated by SQLDiag or SQL LogScout.

Imports XEL files auto-generated by SQL Server for Failover Cluster Instances (*_SQLDIAG*.xel), Always On Availability Groups (*AlwaysOn_health*.xel), and system health sessions (*system_health*.xel).

These options appear at the top level of the Options menu (outside the Importers submenu):

By default, SQL Nexus does not remember your importer option changes between sessions — each time you open the Import page, all importers revert to their default settings.

To persist your choices, check Save import options in the Options menu. Once enabled:

• Every time you toggle an importer option, the change is automatically saved.
• When you reopen the Import page, your saved preferences are restored.
• Clicking Use default options clears all saved preferences and resets everything to defaults.

SQL Nexus uses the standard .NET user settings mechanism to persist preferences. The settings are stored in an XML configuration file located under your Windows user profile:

%LOCALAPPDATA%\Microsoft\sqlnexus.exe_Url_<hash>\<version>\user.config

The user.config file contains all user-scoped settings — not just the importer options. Here is the full list of settings saved in this file:

When Save import options is enabled, the ImporterOptions setting stores all importer choices as an XML string:

<setting name="ImporterOptions" serializeAs="String">
  <value>
    &lt;root&gt;
      &lt;item key='SaveImportOptions' value='True'/&gt;
      &lt;item key='Trace Event Importer (Managed).Enabled' value='True'/&gt;
      &lt;item key='Trace Event Importer (Managed).Drop existing ReadTrace tables' value='True'/&gt;
      &lt;item key='ERRORLOG Importer.Enabled' value='True'/&gt;
      &lt;item key='BLG Blaster (Perfmon/Sysmon BLG files).Enabled' value='True'/&gt;
      &lt;item key='DropDbBeforeImporting' value='False'/&gt;
      &lt;item key='SQLDiagAlwaysOnXEL.Enabled' value='False'/&gt;
      &lt;item key='SQLDiagAlwaysOnXEL.DropExistingTables' value='True'/&gt;
    &lt;/root&gt;
  </value>
</setting>

Each importer option uses the format ImporterName.OptionName as the key. You do not need to edit this file manually — use the Options menu in the Import page to manage your preferences.

You can use SQLNexus as a command line utility. You can get the following help menu via sqlnexus.exe /? or sqlnexus --help

sqlnexus.exe [ [ /S [ /E | /Uuser /Ppwd ] [/D"database"] ] | [/C"connstr"] ] [/Iinputpath] [/Ooutputpath] [/Rreport] [/X] [/Q] [/N]

/S"server"      Specifies a SQL Server name to connect to.
/D"database"    Database to connect to
/E              Log in to SQL using Windows/integrated security
/Uuser          Specifies a SQL (non-Windows) login name
/Ppassword      Specifies the password for a SQL (non-Windows) login
/C"connstring"  Specifies the SqlClient ConnectionString to use (can be used instead of /S /E).
/I"path"        Import SQL diagnostic data from this path
/R"report"      Specifies a report file name to run.
/O"path"        Specifies an export path for reports executed via /R. Also this is where the sqlnexus.000.log would get created
/X              Exit after importing (/I) or exporting (/O) the specified report (/R)
/Vparam=value   Specify the value of a form parameter
/Q              Quiet Mode - minimize windows in console mode
/N              Create a new SQLNexus database before importing (drop existing).

A. Import data using the local instance and Windows authentication. The default log location is %temp%.

sqlnexus.exe /S"." /E /D"sqlnexus" /I"C:\data_collection\output"

B. Import data using a named SQL Server instance, drop the existing sqlnexus database, write the log to a custom folder, and run quietly without interaction.

sqlnexus.exe /S"sqlmachine\sql2017" /E /D"sqlnexus" /I"C:\data_collection\output" /N /O"c:\temp\" /Q

Look at SQL LogScout.