SQL Server DBA cheat sheet: the queries and commands you use every week

#Instance and databases

SELECT @@SERVERNAME AS server, SERVERPROPERTY('ProductVersion') AS version,
       SERVERPROPERTY('ProductUpdateLevel') AS cu, SERVERPROPERTY('Edition') AS edition,
       sqlserver_start_time FROM sys.dm_os_sys_info;

SELECT name, state_desc, recovery_model_desc, compatibility_level, is_read_only, log_reuse_wait_desc
FROM sys.databases ORDER BY name;

EXEC sp_configure;                       -- all settings (show advanced options = 1 first)
SELECT cpu_count, physical_memory_kb / 1024 AS mem_mb FROM sys.dm_os_sys_info;

#Sessions and blocking

-- who is running what right now
SELECT r.session_id, r.status, r.blocking_session_id AS blocked_by, r.wait_type, r.wait_time,
       r.cpu_time, r.logical_reads, DB_NAME(r.database_id) AS db, s.login_name, s.host_name,
       SUBSTRING(t.text, r.statement_start_offset/2 + 1,
         (CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END
          - r.statement_start_offset)/2 + 1) AS statement
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID ORDER BY r.cpu_time DESC;

-- head blockers
SELECT session_id, blocking_session_id, wait_type, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

KILL 87;                                  -- last resort; rollback can take long

#Waits and top queries

SELECT TOP 15 wait_type, wait_time_ms / 1000 AS wait_s, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%' AND wait_type NOT LIKE 'BROKER%' AND wait_type NOT LIKE 'XE%'
  AND wait_type NOT IN ('CHECKPOINT_QUEUE','LAZYWRITER_SLEEP','REQUEST_FOR_DEADLOCK_SEARCH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP','DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION')
ORDER BY wait_time_ms DESC;

-- top CPU queries since plan cache start
SELECT TOP 10 qs.total_worker_time / 1000 AS cpu_ms, qs.execution_count,
       qs.total_logical_reads, SUBSTRING(t.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.total_worker_time DESC;

Query Store (per database): Top Resource Consuming Queries and Regressed Queries reports in SSMS.

#Indexes and statistics

-- fragmentation (LIMITED is cheap)
SELECT OBJECT_NAME(ips.object_id) AS tbl, i.name AS idx, ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000 ORDER BY ips.avg_fragmentation_in_percent DESC;

-- missing index suggestions (review, don't blindly create)
SELECT TOP 10 d.statement, d.equality_columns, d.inequality_columns, d.included_columns,
       s.user_seeks, s.avg_user_impact
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY s.user_seeks * s.avg_user_impact DESC;

ALTER INDEX ALL ON dbo.Orders REBUILD WITH (ONLINE = ON);   -- Enterprise for ONLINE
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
EXEC sp_updatestats;

#Space, log and tempdb

-- file sizes and free space in the current database
SELECT name, type_desc, size/128 AS size_mb, size/128 - FILEPROPERTY(name,'SpaceUsed')/128 AS free_mb, physical_name
FROM sys.database_files;

DBCC SQLPERF(LOGSPACE);                                   -- log use for all databases
SELECT name, log_reuse_wait_desc FROM sys.databases;      -- why the log can't truncate

-- tempdb usage by session
SELECT session_id, (user_objects_alloc_page_count + internal_objects_alloc_page_count) * 8 / 1024 AS mb
FROM sys.dm_db_session_space_usage ORDER BY mb DESC;

-- largest tables
SELECT TOP 20 t.name, SUM(a.total_pages) * 8 / 1024 AS mb, SUM(p.rows) / COUNT(DISTINCT a.type) AS row_count
FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY t.name ORDER BY mb DESC;

#Backup and restore

BACKUP DATABASE Sales TO DISK = 'B:\Backup\Sales_full.bak' WITH COMPRESSION, CHECKSUM, STATS = 10;
BACKUP LOG Sales TO DISK = 'B:\Backup\Sales_log.trn' WITH COMPRESSION, CHECKSUM;
RESTORE VERIFYONLY FROM DISK = 'B:\Backup\Sales_full.bak' WITH CHECKSUM;
RESTORE FILELISTONLY FROM DISK = 'B:\Backup\Sales_full.bak';

RESTORE DATABASE Sales_copy FROM DISK = 'B:\Backup\Sales_full.bak'
  WITH MOVE 'Sales' TO 'D:\Data\Sales_copy.mdf', MOVE 'Sales_log' TO 'L:\Log\Sales_copy.ldf',
       NORECOVERY, STATS = 10;
RESTORE LOG Sales_copy FROM DISK = 'B:\Backup\Sales_log.trn' WITH STOPAT = '2026-10-23 09:15', RECOVERY;

-- last backups per database
SELECT database_name, type, MAX(backup_finish_date) AS last_backup
FROM msdb.dbo.backupset GROUP BY database_name, type ORDER BY database_name, type;

-- progress of backup/restore/DBCC
SELECT session_id, command, percent_complete, estimated_completion_time / 60000 AS min_left
FROM sys.dm_exec_requests WHERE percent_complete > 0;

#Integrity

DBCC CHECKDB (Sales) WITH NO_INFOMSGS, ALL_ERRORMSGS;
SELECT * FROM msdb.dbo.suspect_pages;

#Always On availability groups

SELECT ag.name, ar.replica_server_name, ars.role_desc, ars.synchronization_health_desc,
       drs.database_id, drs.synchronization_state_desc, drs.log_send_queue_size, drs.redo_queue_size
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id;

ALTER AVAILABILITY GROUP AG_Sales FAILOVER;                         -- planned, on sync secondary
ALTER DATABASE Sales SET HADR SUSPEND;  ALTER DATABASE Sales SET HADR RESUME;

#SQL Agent

-- failed jobs in the last day
SELECT j.name, h.run_date, h.run_time, h.message
FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.run_status = 0 AND h.step_id = 0
  AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -1, GETDATE()), 112));

EXEC msdb.dbo.sp_start_job @job_name = N'DatabaseBackup - USER_DATABASES - LOG';
EXEC msdb.dbo.sp_help_jobactivity;

#Security

-- sysadmins
SELECT p.name, p.type_desc FROM sys.server_role_members m
JOIN sys.server_principals p ON m.member_principal_id = p.principal_id
WHERE m.role_principal_id = SUSER_ID('sysadmin');

-- orphaned users in the current database
SELECT dp.name FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S','U') AND sp.sid IS NULL AND dp.authentication_type_desc = 'INSTANCE';
ALTER USER app_user WITH LOGIN = app_user;          -- fix mapping

-- encrypted connections
SELECT session_id, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections;

#Error log

EXEC xp_readerrorlog 0, 1, N'error';           -- current log, SQL Server log, filter text
EXEC sp_cycle_errorlog;                          -- start a new error log

#dbatools one-liners (PowerShell)

Install-Module dbatools -Scope CurrentUser
Get-DbaDatabase -SqlInstance sql01 | Select-Object Name, Status, RecoveryModel, SizeMB, LastFullBackup
Get-DbaLastBackup -SqlInstance sql01 | Format-Table
Test-DbaLastBackup -SqlInstance sql01 -Destination sqltest01 -Database Sales
Get-DbaAgentJob -SqlInstance sql01 | Where-Object LastRunOutcome -ne 'Succeeded'
Copy-DbaLogin -Source sql01 -Destination sql02 -ExcludeSystemLogins
Get-DbaDiskSpace -ComputerName sql01
Find-DbaInstance -ComputerName (Get-Content servers.txt)

Tip

Keep Brent Ozar's sp_Blitz, sp_BlitzFirst and sp_WhoIsActive (Adam Machanic) installed on every instance. In an incident they answer "what's wrong right now?" faster than any single query here.