Lab: Inspect internal metadata (DMVs introduction safely)

Section 6 — Lesson 8 (Lab): Inspect Internal Metadata — DMVs Introduction (Safely)

Welcome back. This lab turns you into a real DBA, because DBAs do not guess.
DBAs observe what SQL Server is doing using internal metadata—primarily through DMVs
(Dynamic Management Views) and DMFs (Dynamic Management Functions).

DMVs are read-only views into SQL Server’s current state and recent activity:
sessions, requests, waits, memory usage, I/O statistics, index usage, and much more.
They are safe when used correctly, but dangerous when used carelessly (heavy queries, huge cross applies, running on busy production).

Lesson description: You will learn a safe DMV workflow that DBAs use in production:
(1) capture a lightweight “snapshot,” (2) focus on the top signals (waits, active requests, I/O hotspots),
(3) save evidence for comparison, and (4) avoid high-risk DMV patterns that can overload a server.
You will build a reusable DMV script pack and practice capturing results into files—like a professional incident runbook.

0) Safety Rules (Read This Before Running Anything)

DBA mindset: The safest DBA is the one who can prove what happened without impacting the system.

1) What DMVs Are (In One Paragraph)

DMVs (Dynamic Management Views) expose internal state that SQL Server maintains in memory.
They help DBAs answer questions like:
Who is connected? What is running? What is waiting? What is blocked? Is memory under pressure?
Which files are slow? Which indexes are used? What changed since last week?

DMVs are designed for diagnostics and performance monitoring. They are not a replacement for application logging,
but they are your primary tool during DBA triage.

2) Create Your DMV Lab Folder (One-Time Setup)

Create this folder structure on your lab machine so your evidence is organized like a real DBA environment:

C:\DBA\
  Scripts\
    DMV\
  Logs\
    DMV_Snapshots\
  Notes\

In SSMS, you will save the scripts you build today under:
C:\DBA\Scripts\DMV\

3) Your “DMV Snapshot Pack” (Copy-Paste Scripts)

Below are safe, lightweight scripts DBAs commonly use first during troubleshooting.
Run them in this order. After each query, save the results (instructions included).

3.1 Snapshot header (timestamp + server identity)

This creates a timestamp and identifies the instance you are collecting evidence from.
Always capture this first.

SELECT
    GETDATE() AS snapshot_time_local,
    @@SERVERNAME AS server_name,
    SERVERPROPERTY('MachineName') AS machine_name,
    SERVERPROPERTY('InstanceName') AS instance_name,
    SERVERPROPERTY('Edition') AS edition,
    SERVERPROPERTY('ProductVersion') AS product_version;

Save output to: C:\DBA\Logs\DMV_Snapshots\dmv_01_identity.txt

3.2 Uptime (why “cold cache” matters)

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Save output to: C:\DBA\Logs\DMV_Snapshots\dmv_02_uptime.txt

3.3 Active sessions (who is connected)

This shows connected sessions, focusing on useful columns.

SELECT TOP (50)
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.cpu_time,
    s.memory_usage,
    s.reads,
    s.writes,
    s.logical_reads,
    s.login_time
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
ORDER BY s.cpu_time DESC;

Save output to: C:\DBA\Logs\DMV_Snapshots\dmv_03_sessions_top50.txt

3.4 Active requests (what is running right now)

This is one of the most important triage queries.
It shows running requests, waits, and blockers.

SELECT TOP (50)
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.cpu_time,
    r.total_elapsed_time,
    r.reads,
    r.writes,
    r.logical_reads,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id
FROM sys.dm_exec_requests AS r
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

Save output to: C:\DBA\Logs\DMV_Snapshots\dmv_04_requests_top50.txt

3.5 Blocking quick view (simple, safe)

This shows blocked sessions and their blockers.
It is not a full blocking chain, but it is a safe first glance.

SELECT
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests AS r
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Save output to: C:\DBA\Logs\DMV_Snapshots\dmv_05_blocking.txt

3.6 Wait statistics (high-level “what the server has been waiting on”)

Wait stats are powerful but often misused. We will keep this safe and practical:
exclude common idle waits, and look at the top waits by total time.

SELECT TOP (25)
    ws.wait_type,
    ws.waiting_tasks_count,
    ws.wait_time_ms,
    ws.max_wait_time_ms,
    ws.signal_wait_time_ms
FROM sys.dm_os_wait_stats AS ws
WHERE ws.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','XE_DISPATCHER_JOIN','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
    'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','BROKER_EVENTHANDLER',
    'TRACEWRITE','XE_DISPATCHER_WAIT','BROKER_RECEIVE_WAITFOR','REPUBLISH_QUEUE','PREEMPTIVE_OS_GETPROCADDRESS',
    'PREEMPTIVE_OS_AUTHENTICATIONOPS','HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE',
    'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
    'SP_SERVER_DIAGNOSTICS_SLEEP'
)
ORDER BY ws.wait_time_ms DESC;

Save output to: C:\DBA\Logs\DMV_Snapshots\dmv_06_waits_top25.txt

3.7 Memory grants (who is waiting for query execution memory)

This is especially useful if users report slowdowns under concurrency or you suspect spills.

SELECT
    mg.session_id,
    mg.request_time,
    mg.grant_time,
    mg.requested_memory_kb / 1024 AS requested_memory_mb,
    mg.granted_memory_kb / 1024 AS granted_memory_mb,
    mg.used_memory_kb / 1024 AS used_memory_mb,
    mg.max_used_memory_kb / 1024 AS max_used_memory_mb,
    mg.wait_time_ms,
    mg.is_next_candidate,
    mg.dop
FROM sys.dm_exec_query_memory_grants AS mg
ORDER BY mg.requested_memory_kb DESC;

Save output to: C:\DBA\Logs\DMV_Snapshots\dmv_07_memory_grants.txt

3.8 File I/O latency (which database files are “slow”)

This query reads virtual file stats for all databases and surfaces average stall times.
It is a foundational DBA diagnostic tool.

SELECT TOP (50)
    DB_NAME(vfs.database_id) AS database_name,
    mf.type_desc,
    mf.name AS logical_file_name,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms,
    CASE WHEN vfs.num_of_reads = 0 THEN 0 ELSE vfs.io_stall_read_ms / vfs.num_of_reads END AS avg_read_stall_ms,
    CASE WHEN vfs.num_of_writes = 0 THEN 0 ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON vfs.database_id = mf.database_id
 AND vfs.file_id     = mf.file_id
ORDER BY (vfs.io_stall_read_ms + vfs.io_stall_write_ms) DESC;

Save output to: C:\DBA\Logs\DMV_Snapshots\dmv_08_io_latency_top50.txt

4) OPTIONAL (Very Useful): Capture SQL Text for a Specific Running Session

Only do this when you have a specific session_id from the active requests query and you need to see the SQL text.
This is safe if used for one session at a time. Do not run it in a loop.

-- Replace 52 with the session_id you want to inspect
DECLARE @session_id int = 52;

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.cpu_time,
    r.total_elapsed_time,
    r.wait_type,
    r.blocking_session_id,
    SUBSTRING(t.text, (r.statement_start_offset/2) + 1,
        CASE
            WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), t.text))
            ELSE (r.statement_end_offset - r.statement_start_offset)/2 + 1
        END
    ) AS running_statement_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id = @session_id;

DBA warning: This is inspection only. Do not “fix” production by editing queries blindly.
Capture evidence, then follow change control.

5) How to Save Results in SSMS (Professional Evidence Method)

For each query above:

1. Run the query in SSMS.
2. In the results grid, right-click → Save Results As…
3. Save as .csv or .txt into C:\DBA\Logs\DMV_Snapshots\
4. Keep file names exactly as provided (so your evidence is consistent)

If you prefer, you can also set: Query → Results To → Results to Text and then copy-paste into a .txt file.
The key is consistency and timestamp discipline.

6) What You Learned (How DBAs Think With DMVs)

DBAs typically follow this triage decision flow:

1. Is the server recently restarted? (uptime; cold cache considerations)
2. Who is connected and what is running? (sessions/requests)
3. Are we blocked? (blocking quick view; later a full chain if needed)
4. What are we waiting on? (wait stats + live request wait types)
5. Is memory grant pressure present? (memory grants; RESOURCE_SEMAPHORE symptoms)
6. Are files slow? (I/O latency by file; tempdb vs log vs data)
7. Capture proof, then change safely (validate and document)

Practice (Required)

1. Create the folder structure in section 2.
   Then save a copy of this lab script as:
   C:\DBA\Scripts\DMV\dmv_snapshot_pack.sql
2. Run queries 3.1 through 3.8 in order and save each output file exactly as instructed.
3. In your DBA notebook (or C:\DBA\Notes\), write a short “snapshot interpretation”:
   • Did you observe any blocking? If yes, which session IDs?
   • What were your top 3 wait types (from the filtered list)?
   • Which file showed the highest average stall time (read or write)?
   • Were there any active memory grants at the moment?

Lab Summary

• DMVs provide live internal evidence of sessions, requests, waits, memory, and I/O.
• Safe DBA usage means targeted queries, small result sets, and quick snapshots.
• You created a reusable DMV snapshot pack—your first real DBA incident toolset.

Next Section Preview: Section 7 — Core DBA Administration (Configuration That Prevents Outages)

Next, you will begin configuring SQL Server like a production DBA:
stable defaults, controlled changes, memory configuration, MAXDOP, file growth strategy, tempdb baseline,
and SQL Agent foundations—with documentation discipline throughout.