What you will learn
The relational engine compiles and executes queries while the storage engine retrieves and modifies pages and records transactional changes. Memory caches and worker scheduling influence response time. Instead of memorizing a component diagram, you will observe a request and separate its session, wait, and database context.
Before you begin
Use an isolated SQL Server 2022 Developer lab on host1.sample.com and a current SQL Server Management Studio client. Developer edition is for nonproduction use. Connect with a dedicated lab administrator for setup; application access is tested separately. The examples use TutorialDB and synthetic rows only. GO is a client batch separator, not a Transact-SQL statement. Sample paths are on the database server, not your workstation. Create TutorialDB and dbo.Events using the database-creation lesson. Instance-wide request visibility on SQL Server 2022 requires VIEW SERVER PERFORMANCE STATE; grant it only to an appropriate monitoring identity.
1. Generate a harmless observable request
USE TutorialDB;
GO
SELECT @@SPID AS lab_session_id;
WAITFOR DELAY '00:00:15';
SELECT event_id,message FROM dbo.Events;
Run this in window A and note its session ID. The deliberate wait keeps a request visible long enough to inspect it. It is not a benchmark and does not emulate a slow storage device.
2. Inspect the request from another session
SELECT session_id, status, command, DB_NAME(database_id) AS database_name,
wait_type, wait_time, blocking_session_id, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID AND session_id > 50;
Run this in window B during the delay. Identify window A by its recorded ID, not by assuming a particular numeric value. Elapsed time includes waiting; CPU time measures something different. A WAITFOR wait in this exercise is intentional. In real diagnosis, repeated observations matter more than one sample.
3. Measure reads for a simple query
USE TutorialDB;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT event_id,message FROM dbo.Events WHERE event_id=1;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
Inspect the Messages pane and the actual execution plan in SSMS. A tiny table may be served from memory, so zero physical reads does not mean no work occurred. Logical reads count page accesses. Never clear a shared buffer or plan cache merely to make a tutorial result look repeatable.
4. Read the example output
The following is illustrative, normalized output, not a transcript from a customer system. Values depend on your release and lab state.
Illustrative request sample:
command wait_type blocking_session_id
WAITFOR WAITFOR 0
Session IDs, timings, and plans vary. The important observation is that a deliberately waiting request need not be blocked by another session.
Verify the result
The first request disappears from active requests when it completes, while its session may remain connected. Explain the difference between CPU, elapsed time, logical reads, and physical reads using your own captured results.
Troubleshooting
If no request appears, repeat the observation while the delay is active and check monitoring permissions. A blocking_session_id of zero does not prove the server is healthy. Other wait types require investigation of the workload and wait resource before tuning.
Recovery and next steps
Close the test window when finished; this exercise changes no server configuration. Retain a baseline before altering memory or parallelism settings. Investigate a measured bottleneck instead of copying configuration values from an unrelated server.