Troubleshooting and diagnostic tools for SQL Server on-premises and hybrid scenarios

Applies to: SQL Server

Introduction

Microsoft Product support for SQL Server hybrid uses troubleshooting tools to help customers collect logs and narrow down technical issues. These tools are available to use publicly. This article summarizes the diagnostic tools that SQL Server product support engineers have been using. The article details various troubleshooting scenarios and shows the corresponding log collection and log analysis tools.

Diagnostic tools for SQL Server

Scenario Basic logs to collect Tools to gather logs Tools to analyze logs
Availability Group - Cluster logs
- Event logs
- System Monitor (Performance monitor logs)
- SQL Server error logs
- SQLDIAG XEL files
- AlwaysOn Health session XEL files
- System Health session XEL files
- Extended Events for AG data movement
- DMV and Catalog view snapshots for AG
- Preferred: SQL LogScout
Use scenario "AlwaysOn."

- Alternative: TSSv2
Use scenario "SQL Base."

- Alternative: PSSDIAG
Use custom diagnostic "Always On Basic Info."
- Preferred: AGDiag
Scenarios to use: Analyze failover and failures.

- Alternative: SQL Nexus
Scenarios to use: performance, latency, health, and best practices.
Slow performance
- Extended Event (XEvent) trace captures batch-level starting/completed events, errors and warnings, log growth/shrink, lock escalation and timeout, deadlock, login/logout
- List of actively running SQL traces and Xevents
- Snapshots of SQL DMVs that track waits/blocking and high CPU queries
- Query Data Store info (if that is active)
- tempdb contention info from SQL DMVs/system views
- Linked Server metadata (SQL DMVs/system views)
- Service Broker configuration information (SQL DMVs/system views)
- Preferred: SQL LogScout
Use scenarios "GeneralPerf," "DetailedPerf," or "LightPerf."


- Alternative: PSSDIAG
Use scenarios "General Performance," "Detailed Performance," or "Light Performance."
- Preferred: SQL Nexus
Scenarios to use: performance analysis, best practice recommendations, bottleneck analysis, blocking, and top queries.

- Alternative: RML Utilities
Scenarios to use: query analysis to understand top resource consuming queries.
Connection - BID Trace/Driver Traces
- Network Trace
- Auth Trace
- SQL Server Error logs
- Windows Event logs
Snapshots of NETSTAT and TASKLIST
- Preferred: SQL Trace
Configure collection settings in the INI file.

- Alternative: SQL LogScout
Use scenario "NetworkTrace."

- Alternative: SSPICLIENT
Use this tool when encountering SSPI or Kerberos errors and log a detailed trace for analysis.
- Preferred: SQL Network Analyzer UI
SQL Network Analyzer
Scenarios to use: Read network packet capture files and produce a report highlighting potential areas of interest.

- Alternative: SQLCHECK
Reports on any settings that may affect connectivity.

- Alternative: SQLBENCH
Display timings for comparative analysis.

- Alternative: DBTEST
Record how long it takes to connect and how long to execute a command.
Replication - SQL Server error logs
- Replication, CDC, CT diagnostic info (SQL DMVs/system views)
- Preferred: SQL LogScout
Use data collection scenario
"Replication."


- Alternative: PSSDIAG
Use custom diagnostic "Replication."
- Preferred: SQL Nexus
Scenarios to use: replication reports, performance analysis, best practice recommendations, bottleneck analysis, blocking, and top queries.
Installation/Setup - Setup Bootstrap folder with all the setup logs - Preferred: SQL LogScout
Use data collection scenario "Setup."
Backup/Restore - Backup/Restore progress Xevent (backup_restore_progress_trace)
- Trace flags for backup restore progress
- SQL VSS Writer Log (on SQL Server 2019 and later versions)
- VSS Admin (OS) logs for VSS backup-related scenarios
- Performance Monitor
- Preferred: SQL LogScout
Use data collection scenario "BackupRestore."
Memory usage - Memory clerks DMVs or DBCC MEMORYSTATUS
- Performance Monitor
- Preferred: SQL LogScout
Use data collection scenario "Memory."
- Preferred: SQL Nexus
Scenarios to use:import memory output and view at Memory Brokers.
Memory dumps - Manual memory dumps captured for specific t-shooting scenarios
- Automatically generated memory dumps for exception scenarios
- Preferred: SQL LogScout
Use data collection scenario "DumpMemory."

- Alternative: SQLDumper

- Alternative: TSS V2
Use scenario "SQL Base" to gather existing memory dumps.
- Preferred: WinDbg (Debugging tools for Windows)
Scenarios to use: load and analyze memory dump.

- Alternative: SQL CallStack Resolver
Database corruption SQL I/O pattern stress simulation SQLIOSim