100 Essential SQL Server DBA Commands for Production Troubleshooting

Instance Basic Information (1-10)

Commands to retrieve SQL Server version, detailed version properties (ProductVersion, ProductLevel, Edition, EngineEdition), instance name, current server time, startup time, CPU/memory configuration, max server memory setting, current database context, all database states (name, state, recovery model, compatibility level), and database creation dates.

Database Space Management (11-20)

Queries for database files with sizes in MB, data vs log file breakdown, database size ranking, log file sizes, log space usage via DBCC SQLPERF(LOGSPACE), database space usage via sp_spaceused, top 20 largest tables using sys.dm_db_partition_stats, table row counts from sys.partitions, file growth settings (growth increment, percent growth flag), and recovery model per database.

Session & Connection Troubleshooting (21-35)

Current sessions from sys.dm_exec_sessions; active requests with status, command, CPU time, elapsed time, wait type, and blocking session; full SQL text via CROSS APPLY sys.dm_exec_sql_text; active user connections grouped by login; client source analysis (host_name, program_name, login_name); long-running requests ordered by elapsed time; top CPU-consuming sessions; current waits with wait_type and wait_time; blocking sessions and full blocking chains; sleeping (idle) connections; KILL command example; user connections configuration limit; login failure log via xp_readerrorlog; and top 20 wait types by cumulative wait_time_ms from sys.dm_os_wait_stats.

Locks, Transactions & Blocking (36-50)

All current locks from sys.dm_tran_locks; open transactions via DBCC OPENTRAN; active transactions from sys.dm_tran_active_transactions; long-running transactions with begin time; lock waits where request_status = 'WAIT'; blocking SQL with wait details; deadlock info from system_health.session_targets; current isolation level via DBCC USEROPTIONS; active transaction count; version store space usage from sys.dm_tran_version_store_space_usage; TempDB version store via sys.dm_db_file_space_usage; total lock count; waiting tasks with resource_description; active Extended Events sessions; and forced session termination with KILL.

SQL Performance Analysis (51-65)

Top 20 CPU-intensive queries (total_worker_time) with query text; top 20 most frequently executed queries (execution_count); top 20 highest average duration queries; cached execution plans from sys.dm_exec_cached_plans; estimated execution plan via SET SHOWPLAN_XML ON; Query Store catalog views (sys.query_store_query); historical high-duration queries from sys.query_store_runtime_stats; top 20 logical reads and physical reads queries; and plan cache size in MB.

Index & Statistics (66-80)

All indexes from sys.indexes; index fragmentation via sys.dm_db_index_physical_stats (LIMITED mode); ALTER INDEX REBUILD and REORGANIZE syntax; UPDATE STATISTICS; missing index details from sys.dm_db_missing_index_details; index usage stats from sys.dm_db_index_usage_stats; unused indexes (zero seeks/scans); statistics last update date via STATS_DATE(); and CREATE INDEX syntax.

Always On High Availability (81-90)

Replica states from sys.dm_hadr_availability_replica_states; database replica sync states from sys.dm_hadr_database_replica_states; log send queue and redo queue sizes for latency monitoring; availability group configuration; listeners; replica metadata; and synchronization health description.

Backup & Recovery (91-97)

Backup history from msdb.dbo.backupset with type, start/finish dates; BACKUP DATABASE and BACKUP LOG commands with disk paths; RESTORE DATABASE syntax; recent backup top 10; and restore history from msdb.dbo.restorehistory.

Permission Management (98-100)

Server principals (logins) from sys.server_principals; database principals (users) from sys.database_principals; database permissions from sys.database_permissions; CREATE LOGIN with password; CREATE USER mapped to login; ALTER ROLE db_datareader ADD MEMBER; ALTER ROLE db_datawriter ADD MEMBER; DROP USER; and DROP LOGIN.

Daily Inspection Supplement (93-100)

SQL Agent jobs from msdb.dbo.sysjobs; failed job history (run_status <> 1); error log via xp_readerrorlog; TempDB space usage from sys.dm_db_file_space_usage; memory clerks from sys.dm_os_memory_clerks; scheduler health from sys.dm_os_schedulers; I/O latency via sys.dm_io_virtual_file_stats; and top 20 wait types from sys.dm_os_wait_stats.

Diagnostic Logic Summary

The article emphasizes that mature DBA skill lies in diagnostic reasoning, not command memorization. Examples: high CPU → check waits and high-cost SQL, not CPU itself; slow database → analyze execution plans before adding indexes; log growth → investigate transactions, backup chain, recovery model; Always On latency → examine log send queue and redo queue, not just seconds.