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 |