Someone hands you a SQL Server. “Figure out what’s wrong.” No context. No access to monitoring. No deployment history.
What do you run?
This lesson is the eight queries every DBA should have memorized (or at least in a toolbox) to assess an unfamiliar server in under ten minutes. Dynamic Management Views — DMVs — are SQL Server’s built-in telemetry. Eight well-chosen queries tell you more than a day of reading.
Every query in this lesson works on SQL Server 2019+ and Azure SQL MI. Azure SQL DB omits some (DBCC, cross-database DMVs); I’ll note where.
1. The “what am I looking at” query
SELECT
@@SERVERNAME AS server_name,
@@VERSION AS version_string,
SERVERPROPERTY('Edition') AS edition,
SERVERPROPERTY('ProductVersion') AS product_version,
SERVERPROPERTY('ProductLevel') AS product_level,
SERVERPROPERTY('MachineName') AS machine_name,
SERVERPROPERTY('InstanceDefaultDataPath') AS default_data,
SERVERPROPERTY('InstanceDefaultLogPath') AS default_log,
-- Memory
(SELECT value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)') AS max_mem_mb,
-- CPU / OS
cpu_count, hyperthread_ratio, physical_memory_kb / 1024 AS physical_memory_mb,
sqlserver_start_time
FROM sys.dm_os_sys_info;First thing you do. “What version, what edition, how much RAM, how many cores, how long has it been up?”
If the server started 12 minutes ago, all your wait stats are useless (they reset on restart).
2. Wait stats
WITH Waits AS (
SELECT
wait_type,
wait_time_ms / 1000.0 AS wait_s,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_s,
signal_wait_time_ms / 1000.0 AS signal_s,
waiting_tasks_count AS wait_count,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS pct,
ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'DIRTY_PAGE_POLL',
'BROKER_PARTITION_COLLECT', 'WAIT_FOR_RESULTS'
)
)
SELECT TOP (15)
wait_type, wait_s, resource_s, signal_s, wait_count, CAST(pct AS DECIMAL(5,2)) AS pct
FROM Waits
ORDER BY wait_time_ms DESC;The exclude list filters out “background” wait types that don’t matter. The remainder tells you what the server is actually spending time on.
Common patterns:
PAGEIOLATCH_*— waiting on disk. Slow I/O.LCK_*— lock waits. Blocking.ASYNC_NETWORK_IO— waiting on the client to consume results. Fat row sets, slow app.CXPACKET,CXCONSUMER— parallelism. Usually fine; investigate if extreme.SOS_SCHEDULER_YIELD— CPU pressure.PAGELATCH_*— tempdb contention or hot pages.
If you know your waits, you’re 50% of the way to diagnosing any performance problem.
3. Who’s running right now
From lesson 20, adapted:
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
r.status,
r.command,
r.wait_type,
r.wait_time,
r.blocking_session_id,
DB_NAME(r.database_id) AS database_name,
r.cpu_time / 1000.0 AS cpu_s,
r.reads, r.writes, r.logical_reads,
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 current_statement
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
ORDER BY r.cpu_time DESC;The third thing you run after wait stats. “What’s this server doing right now?” Sorted by CPU: the heaviest active query is first.
4. Top queries by resource
SELECT TOP (20)
qs.execution_count,
qs.total_worker_time / 1000 / NULLIF(qs.execution_count, 0) AS avg_cpu_ms,
qs.total_elapsed_time / 1000 / NULLIF(qs.execution_count, 0) AS avg_elapsed_ms,
qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads,
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 statement_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY avg_cpu_ms DESC;The top 20 queries by average CPU. Replace avg_cpu_ms with avg_logical_reads or execution_count for different views.
If this server has Query Store on (lesson 28), prefer that for historical questions. This DMV only knows about plans currently in the plan cache.
5. Buffer pool: what’s in memory
SELECT TOP (20)
DB_NAME(bp.database_id) AS database_name,
COUNT(*) * 8 / 1024 AS mb_in_cache,
100.0 * COUNT(*) / (SELECT COUNT(*) FROM sys.dm_os_buffer_descriptors) AS pct_of_buffer_pool
FROM sys.dm_os_buffer_descriptors AS bp
GROUP BY DB_NAME(bp.database_id)
ORDER BY mb_in_cache DESC;Which databases own how much of the buffer pool. Tells you what’s hot.
For one database, which tables:
USE Runehold;
GO
SELECT TOP (20)
OBJECT_SCHEMA_NAME(p.object_id) + '.' + OBJECT_NAME(p.object_id) AS obj,
COUNT(*) * 8 / 1024 AS mb_in_cache
FROM sys.dm_os_buffer_descriptors AS bp
JOIN sys.allocation_units AS au ON au.allocation_unit_id = bp.allocation_unit_id
JOIN sys.partitions AS p ON p.partition_id = au.container_id OR (au.type = 2 AND au.container_id = p.hobt_id)
WHERE bp.database_id = DB_ID()
GROUP BY OBJECT_SCHEMA_NAME(p.object_id) + '.' + OBJECT_NAME(p.object_id)
ORDER BY mb_in_cache DESC;Sorting big tables by cache footprint. Hot tables at the top. If a “small” table is at the top, something’s wrong (too many scans?).
6. Missing indexes
From lesson 22:
SELECT TOP (20)
[Impact] = mig_stats.avg_total_user_cost * (mig_stats.avg_user_impact / 100.0) * (mig_stats.user_seeks + mig_stats.user_scans),
mid.statement AS table_name,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
mig_stats.user_seeks + mig_stats.user_scans AS seeks_scans
FROM sys.dm_db_missing_index_groups AS mig
JOIN sys.dm_db_missing_index_group_stats AS mig_stats ON mig_stats.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid ON mid.index_handle = mig.index_handle
ORDER BY [Impact] DESC;Top 20 suggestions. Evaluate, don’t blindly create.
7. Unused indexes
SELECT
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
ius.user_seeks + ius.user_scans + ius.user_lookups AS reads,
ius.user_updates AS writes,
i.is_disabled
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
AND ius.database_id = DB_ID()
WHERE i.object_id > 100
AND i.type_desc = 'NONCLUSTERED'
ORDER BY (ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0)) ASC;Indexes with very few reads but real writes. Candidates for dropping.
Caveat: dm_db_index_usage_stats resets on server restart. If the server restarted yesterday, these numbers are meaningless. Verify the server has been up for at least a week.
8. Database file usage
SELECT
DB_NAME(mf.database_id) AS database_name,
mf.name AS logical_name,
mf.type_desc,
mf.size * 8 / 1024 AS size_mb,
CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS BIGINT) * 8 / 1024 AS used_mb,
mf.growth, mf.is_percent_growth
FROM sys.master_files AS mf
WHERE mf.database_id > 4 -- skip system
ORDER BY size_mb DESC;Gives you a per-database file breakdown. Useful when the disk is filling up and you need to know which database is responsible.
Bonus: the “deadlock graph from the system health session” query
From lesson 20:
SELECT TOP (10)
XEvent.value('(@timestamp)[1]', 'DATETIME2') AS ts,
XEvent.query('.') AS deadlock_xml
FROM (
SELECT CAST(target_data AS XML) AS td
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS source
CROSS APPLY td.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS X(XEvent)
ORDER BY ts DESC;Recent deadlocks captured by the default system_health XE session. Essential when someone asks “did we have any deadlocks overnight?”
Where to keep these
Every DBA has a “SQL toolbox” folder on their laptop — a collection of .sql files with queries like these, named clearly (00-system-overview.sql, 01-wait-stats.sql, etc.). Copy this lesson into yours.
For even more polished versions of these, see lessons 37-40 on Brent Ozar’s First Responder Kit. The pro versions output formatted results with color coding, prioritization, and links to blog posts explaining each issue.
Run this on your own machine
Bundle the eight queries above into a single .sql file. Save as dba-overview.sql. Every time you touch an unfamiliar SQL Server, run it top to bottom. Ten minutes of output will tell you 90% of what you need to know to start diagnosing.
Module 7 done — DBA essentials covered end-to-end. Next: the Emergency Kit. Lessons 37-40 cover Brent Ozar’s First Responder Kit (sp_Blitz, sp_BlitzIndex, sp_BlitzCache) and Adam Machanic’s sp_WhoIsActive. The tools every serious DBA keeps sharpened.