Top 50 SQL DBA Problems with Practical Solutions | Complete Guide

1. Identify top CPU-consuming queries

SELECT TOP 10
  qs.total_worker_time/qs.execution_count AS avg_cpu,
  qs.execution_count,
  SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
            ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(qt.text)
              ELSE qs.statement_end_offset END
             - qs.statement_start_offset)/2) + 1) AS query_text,
  qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY avg_cpu DESC;

Tip: Find queries using most CPU to tune or index.

2. Find long-running queries

SELECT session_id, start_time, status, command,
       DATEDIFF(SECOND, start_time, GETDATE()) AS secs_running,
       SUBSTRING(txt.text, (req.statement_start_offset/2)+1, 200) AS sql_text
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) txt
WHERE req.status = 'running'
ORDER BY secs_running DESC;

Tip: Spot blocking or runaway sessions.

3. Detect blocking chain (who’s blocking whom)

SELECT blocking_session_id, session_id, wait_type, wait_time, last_wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Tip: Identify blockers so you can kill or tune offending queries.

4. List active blocked and blocking sessions with SQL

SELECT
  r.session_id, r.blocking_session_id, s.login_name, s.host_name, r.wait_type, r.wait_time,
  SUBSTRING(t.text, r.statement_start_offset/2+1, 200) AS sql_text
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.blocking_session_id <> 0;

Tip: Detailed view for incident response.

5. Kill a blocking SPID (use cautiously)

KILL 57; -- replace 57 with offending session_id

Tip: Emergency unblock; always check consequences.

6. Find missing indexes (DMV)

SELECT TOP 50
  CONVERT(INT, migs.avg_user_impact) AS impact,
  mid.statement AS table_name,
  mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_user_impact DESC;

Tip: Quick guidance for indexing; evaluate before creating.

7. Find unused indexes (index usage stats)

SELECT db_name(DB_ID()) AS DBName, OBJECT_NAME(s.object_id) AS TableName,
       i.name AS IndexName, user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND user_seeks + user_scans + user_lookups = 0
ORDER BY user_updates DESC;

Tip: Candidate indexes to drop (after validation).

8. Rebuild fragmented indexes

ALTER INDEX ALL ON dbo.YourTable REBUILD WITH (ONLINE = ON);
-- or reorganize if fragmentation < 30%
ALTER INDEX ALL ON dbo.YourTable REORGANIZE;

Tip: Reduce fragmentation for I/O efficiency.

9. Find index fragmentation

SELECT DB_NAME() AS DBName, OBJECT_NAME(ps.object_id) AS table_name, i.name,
       ps.index_id, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 10
ORDER BY ps.avg_fragmentation_in_percent DESC;

Tip: Plan index maintenance.

10. Check database integrity (DBCC CHECKDB)

DBCC CHECKDB (YourDatabase) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Tip: Detect corruption early. Run during low-load windows.

11. Repair a corrupt database (last resort)

-- Preferred: restore from backup.
-- If not possible:
DBCC CHECKDB (YourDatabase, REPAIR_ALLOW_DATA_LOSS);

Tip: REPAIR_ALLOW_DATA_LOSS can lose data, use only if restore impossible.

12. Take full backup

BACKUP DATABASE YourDatabase
TO DISK = 'E:\Backups\YourDatabase_FULL.bak'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM;

Tip: Baseline backups for point-in-time recovery.

13. Take differential backup

BACKUP DATABASE YourDatabase
TO DISK = 'E:\Backups\YourDatabase_DIFF.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

Tip: Take faster, smaller backups between full backups.

14. Take transaction log backup (for full recovery model)

BACKUP LOG YourDatabase
TO DISK = 'E:\Backups\YourDatabase_LOG.trn'
WITH COMPRESSION, CHECKSUM;

Tip: Truncate log and enable point-in-time restores.

15. Restore full + diff + log for PITR

RESTORE DATABASE YourDatabase FROM DISK='...FULL.bak' WITH NORECOVERY;
RESTORE DATABASE YourDatabase FROM DISK='...DIFF.bak' WITH NORECOVERY;
RESTORE LOG YourDatabase FROM DISK='...LOG.trn' WITH RECOVERY, STOPAT='2026-07-14 10:15:00';

Tip: Example of point-in-time recovery workflow.

16. Find last full/diff/log backup times

SELECT database_name, type, MAX(backup_finish_date) AS last_backup
FROM msdb.dbo.backupset
WHERE database_name = 'YourDatabase'
GROUP BY database_name, type;
-- type: D=Full, I=Diff, L=Log

Tip: Verify backup cadence.

17. Check backup integrity (RESTORE VERIFYONLY)

RESTORE VERIFYONLY FROM DISK = 'E:\Backups\YourDatabase_FULL.bak';

Tip: Ensure backup file readable and consistent.

18. Automate integrity checks (example Agent job script)

-- Use DBCC CHECKDB in SQL Agent job step
DBCC CHECKDB (YourDatabase) WITH NO_INFOMSGS;

Tip: Schedule regular CHECKDBs.

19. Shrink database file (use sparingly)

DBCC SHRINKFILE (N'YourDB_Log' , 1024); -- shrink to 1GB

Tip: Generally discouraged; only for one-time space reclamation.

20. Find largest tables (by space)

EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''';
-- or faster:
SELECT t.NAME AS table_name, p.rows, 
       SUM(a.total_pages) * 8 AS total_kb,
       SUM(a.used_pages) * 8 AS used_kb, 
       (SUM(a.total_pages)-SUM(a.used_pages)) * 8 AS unused_kb
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY t.Name, p.rows
ORDER BY total_kb DESC;

Tip: Identify space hogs for archiving/partitioning.

21. Move database files to new disk

-- Step 1: set DB offline
ALTER DATABASE YourDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Move the files at OS level, then:
ALTER DATABASE YourDatabase MODIFY FILE (NAME = YourDB_Data, FILENAME = 'D:\DATA\YourDB.mdf');
ALTER DATABASE YourDatabase MODIFY FILE (NAME = YourDB_Log, FILENAME = 'F:\LOG\YourDB.ldf');
ALTER DATABASE YourDatabase SET ONLINE;

Tip: Rebalance I/O across disks. Plan downtime.

22. Check transaction log usage and log_reuse_wait

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDatabase';

Tip: Diagnose why log isn’t truncating (e.g., replication, open txn).

23. Find open transactions preventing log truncation

DBCC OPENTRAN('YourDatabase');
-- or use DMVs
SELECT * FROM sys.dm_tran_active_transactions;

Tip: Long open transactions keep log growth.

24. Shrink log file (only if necessary)

BACKUP LOG YourDatabase TO DISK='NUL';
DBCC SHRINKFILE ('YourDB_Log', 1024);

Tip: Backup log to allow truncation then shrink; be careful—log will regrow.

25. Detect and resolve deadlocks (capture graph)

-- Enable trace flag to capture deadlock graph to error log:
DBCC TRACEON (1222, -1);
-- or use Extended Events session for deadlock_graph

Tip: Analyze and fix deadlock victims by changing order or adding indexes.

26. Find blocked transactions and blocking chain with full details

SELECT r.session_id, r.status, r.command, r.wait_type, r.blocking_session_id,
       s.login_name, s.host_name, s.program_name,
       SUBSTRING(t.text, r.statement_start_offset/2+1, 200) AS sql_text
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.blocking_session_id <> 0;

Tip: In-depth incident analysis.

27. Monitor wait stats (bottleneck detection)

SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK') 
ORDER BY wait_time_ms DESC;

Tip: Helps prioritize tuning (IO, CPU, memory, locks).

28. Clear plan cache (careful – impacts performance)

DBCC FREEPROCCACHE;
-- Or clear for specific plan handle

Tip: Use when bad plans persist; prefer clearing one plan, not whole cache in prod.

29. Find parameter sniffing issues (plan volatility)

SELECT obj.[name], cp.usecounts, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
JOIN sys.objects obj ON cp.objectid = obj.object_id
WHERE cp.cacheobjtype = 'Compiled Plan' AND qp.query_plan.exist('//ParameterList') = 1;

Tip: Detect plans that vary per parameter; consider OPTION (RECOMPILE).

30. Force recompile for a stored proc

EXEC sp_recompile N'dbo.YourProc';
-- or use:
SELECT ... OPTION (RECOMPILE)

Tip: Solve bad cached-plan issues without flushing entire cache.

31. Find unused stored procedures / objects (no execution in cache)

SELECT o.name, o.type_desc
FROM sys.procedures p
LEFT JOIN sys.dm_exec_procedure_stats ps ON p.[object_id] = ps.object_id
LEFT JOIN sys.objects o ON p.object_id = o.object_id
WHERE ps.object_id IS NULL;

Tip: Candidate for cleanup (verify via extended usage logs first).

32. Check TempDB usage and top consumers

SELECT s.session_id, SUM(s.used_pages) * 8 AS used_kb
FROM sys.dm_db_task_space_usage s
GROUP BY session_id
ORDER BY used_kb DESC;
-- Also:
SELECT name, size*8 AS size_kb FROM tempdb.sys.database_files;

Tip: Diagnose TempDB contention and sizing.

33. Add TempDB data files to reduce contention

ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'E:\TempDB\tempdb2.mdf', SIZE = 512MB);
-- Restart recommended for initial sizing changes to take effect evenly.

Tip: Multiple data files reduce allocation contention (typically one per logical CPU up to ~8).

34. Identify columns with missing statistics

SELECT OBJECT_NAME(s.object_id) AS TableName, c.name AS ColumnName, s.name AS StatsName
FROM sys.stats s
JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE s.auto_created = 1 AND s.has_filter = 0;
-- Use sys.dm_db_stats_properties for last_updated_date

Tip: Out-of-date stats cause bad plans.

35. Update statistics for a table or database

-- For a table
UPDATE STATISTICS dbo.YourTable WITH FULLSCAN;
-- For whole DB
EXEC sp_updatestats;

Tip: Ensure optimizer has current distribution info.

36. Identify top wait types causing queries to slow

(See #27) — interpret: IO waits (PAGEIOLATCH_), lock waits (LCK_M_*), etc.
Tip: Map wait types to hardware/config bottlenecks.

37. Detect long-running transactions that prevent log truncation

SELECT s.session_id, s.login_name, t.transaction_begin_time, DATEDIFF(MINUTE,t.transaction_begin_time, GETDATE()) AS mins_running
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions t ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
ORDER BY mins_running DESC;

Tip: Long transactions keep log growth.

38. Check database compatibility level

SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabase';

Tip: New features require newer compatibility; changing can affect behavior.

39. Find orphaned users (database users without login)

EXEC sp_change_users_login 'Report';
-- or for new versions:
SELECT dp.name AS UserName, sp.name AS LoginName
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type_desc = 'SQL_USER' AND sp.name IS NULL;

Tip: Clean-up and security hygiene.

40. Fix orphaned users

ALTER USER [dbuser] WITH LOGIN = [servername\domain\login];
-- Or map using sp_change_users_login for older versions
EXEC sp_change_users_login 'Auto_Fix', 'dbuser', NULL, 'StrongPassword';

Tip: Restore access mapping after restore/move.

41. Audit who changed schema (DDL auditing)

-- Use SQL Server Audit or DDL triggers; simple DDL trigger:
CREATE TABLE AuditDDL (EventTime DATETIME, LoginName SYSNAME, EventData XML);
GO
CREATE TRIGGER trgDDL ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO AuditDDL (EventTime, LoginName, EventData)
VALUES (GETDATE(), SUSER_SNAME(), EVENTDATA());
GO

Tip: Track schema changes for compliance.

42. Disable/Enable Auto Close / Auto Shrink for best performance

ALTER DATABASE YourDatabase SET AUTO_CLOSE OFF;
ALTER DATABASE YourDatabase SET AUTO_SHRINK OFF;

Tip: Auto close/shrink cause overhead; turn off on production DBs.

43. Find databases using full recovery model (important for log backups)

SELECT name, recovery_model_desc FROM sys.databases WHERE recovery_model_desc = 'FULL';

Tip: Ensure log backups scheduled.

44. Convert DB to SIMPLE/ FULL recovery (caution)

ALTER DATABASE YourDatabase SET RECOVERY SIMPLE; -- or FULL

Tip: SIMPLE truncates logs automatically; FULL needed for PITR.

45. Monitor backup job failures (query msdb)

SELECT job.name, h.run_status, h.run_date, h.run_time, h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs job ON h.job_id = job.job_id
WHERE job.name LIKE '%Backup%' AND h.step_id = 0
ORDER BY h.run_date DESC, h.run_time DESC;

Tip: Proactively detect failed backups.

46. Configure Instant File Initialization (for data file growth)

• Grant Perform volume maintenance tasks to SQL Server service account.
  Tip: Speeds data file growth by skipping zeroing; log files still zeroed.

47. Detect large transaction log growth events

SELECT name, size*8/1024 AS size_mb, growth, is_percent_growth FROM sys.database_files WHERE type_desc = 'LOG';
-- Additionally examine msdb backup history and log backup gaps

Tip: Correlate with large operations or missing log backups.

48. Identify and drop duplicate indexes

;WITH c AS (
  SELECT OBJECT_NAME(i.object_id) AS tablename, i.name, i.index_id,
         STUFF((SELECT ',' + COL_NAME(ic.object_id, ic.column_id)
                FROM sys.index_columns ic WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                ORDER BY ic.key_ordinal FOR XML PATH('')),1,1,'') AS cols
  FROM sys.indexes i
  WHERE i.is_primary_key = 0
)
SELECT * FROM c
-- find identical cols on same table, review then DROP INDEX

Tip: Remove redundant indexes to reduce write overhead.

49. Set up Maintenance Plan: rebuild, update stats, backup

Use SQL Server Maintenance Plans or custom Agent jobs combining:

• ALTER INDEX ... REBUILD
• UPDATE STATISTICS
• BACKUP DATABASE
  Tip: Automate repetitive DBA tasks.

50. Configure high availability: basic AG / failover readiness checklist

• Ensure cluster, shared storage or Windows Failover Cluster configured (AGs require FC or Synchronous commit)
• Backup and restore master/ msdb as necessary
• Test failover with ALTER AVAILABILITY GROUP ... FAILOVER;
  Tip: Prepare for disasters and ensure HA works.

Note: Many solutions include DBCC, ALTER DATABASE, RESTORE, DMVs, etc. Run these first in non-production or with safeguards (backups, maintenance windows) where appropriate.