Site boardthey.com

Top SQL Server Utilities Every DBA Should Know

Written by

admin-dfv33

in

Troubleshooting SQL Server with Built-In Utilities: A Step-by-Step Guide

Effective troubleshooting keeps SQL Server healthy, performant, and reliable. This guide walks you through built-in SQL Server utilities and step-by-step methods to diagnose and resolve common issues: connectivity problems, performance bottlenecks, blocking/deadlocks, storage and backup failures, and corruption. Use the steps as a checklist—run the relevant utility, interpret results, apply fixes, and verify.

1. Preparation: gather context and safety steps

  • Backup: Ensure you have a recent database backup before making changes.
  • Permissions: Use an account with appropriate administrative privileges.
  • Logs: Locate SQL Server Error Log, Windows Event Viewer (Application/System), and SQL Agent logs.
  • Baseline: Note current CPU, memory, disk, and network usage.

2. Connectivity issues

Utilities: SQL Server Configuration Manager, sqlcmd, SQL Server Management Studio (SSMS)

  1. Check service status
    • Open SQL Server Configuration Manager — confirm SQL Server and SQL Server Browser services are running.
  2. Verify network configuration
    • Ensure TCP/IP is enabled for the instance and correct ports are open (default 1433).
  3. Test connectivity
    • Use sqlcmd: 
      Code
      sqlcmd -S \INSTANCE -U  -P

      or use Windows Authentication: 

      Code
      sqlcmd -S \INSTANCE -E
    • From a remote client, test TCP port with telnet or PowerShell Test-NetConnection.
  4. Inspect error logs
    • Review SQL Server Error Log for login failures, protocol errors, or port binding problems.
  5. Common fixes
    • Start required services, enable protocols, adjust firewall rules, add SPNs for Kerberos, or correct connection strings.

3. High CPU or memory usage

Utilities: Activity Monitor (SSMS), sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_os_performancecounters, Performance Monitor (perfmon)

  1. Get a quick view
    • Open Activity Monitor → check % Processor Time, expensive queries, and blocked processes.
  2. Identify top CPU queries
    • Run: 
      Code
      SELECT TOP 10 total_worker_time/1000 AS total_cpu_ms,   execution_count,   (total_worker_time/execution_count)/1000 AS avg_cpu_ms,   SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
      
 
      ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)   ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text 
      

      FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY total_workertime DESC;
  3. Examine waits
    • Query wait stats: 
      Code
      SELECT wait_type, SUM(wait_time_ms) AS total_wait_ms, SUM(waiting_tasks_count) AS wait_count FROM sys.dm_os_wait_stats GROUP BY wait_type ORDER BY total_waitms DESC;
  4. Check plan and indexes
    • Use actual execution plans in SSMS; check missing index DMVs: 
      Code
      SELECT TOP 50 mid.statement AS [user_seeks],   mid.avg_user_impact, mid.avg_total_user_cost, mid.database_id, mid.object_id FROM sys.dm_db_missing_index_details mid ORDER BY mid.avg_user_impact DESC;
  5. Fixes
    • Update statistics, add/rebuild indexes, rewrite inefficient queries, parameterize, or scale resources. Consider plan forcing only after careful testing.

4. Disk space, I/O, and storage issues

Utilities: sys.dm_io_virtual_file_stats, sys.masterfiles, DBCC SHRINKFILE (cautiously), Disk Management, Performance Monitor

  1. Check file sizes and growth
    • Query: 
      Code
      SELECT DB_NAME(database_id) AS db_name, file_id, type_desc, name, size/128.0 AS size_mb, growth FROM sys.masterfiles;
  2. Monitor I/O latency
    • Run: 
      Code
      SELECT DB_NAME(vf.database_id) AS db_name, mf.physical_name,   io_stall_read_ms, num_of_reads, io_stall_write_ms, num_of_writes FROM sys.dm_io_virtual_file_stats(NULL, NULL) vf JOIN sys.master_files mf ON vf.database_id = mf.database_id AND vf.file_id = mf.file_id ORDER BY (io_stall_read_ms + io_stall_write_ms) DESC;

3

Comments

Leave a Reply

More posts