Guided1. Measure your buffer pool’s current cached size and compare it to total server RAM.

SELECT COUNT(*) * 8 / 1024 AS cached_data_mb FROM sys.dm_os_buffer_descriptors;
SELECT total_physical_memory_kb/1024 AS total_ram_mb FROM sys.dm_os_sys_memory;

Guided2. Find the top 5 largest cached execution plans by size and note their objtype.

SELECT TOP 5 objtype, size_in_bytes/1024 AS size_kb, usecounts
FROM sys.dm_exec_cached_plans ORDER BY size_in_bytes DESC;

Guided3. Check your current transaction log size and percent used for a test database.

DBCC SQLPERF(LOGSPACE);

Guided4. Create a table, insert 10,000 rows, then check how many pages it occupies.

CREATE TABLE dbo.PageDemo (id INT IDENTITY PRIMARY KEY, filler CHAR(500));
INSERT INTO dbo.PageDemo (filler)
SELECT TOP 10000 REPLICATE('x',500) FROM sys.all_objects a CROSS JOIN sys.all_objects b;
SELECT page_count, record_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.PageDemo'), NULL, NULL, 'DETAILED');

Guided5. Run the same parameterized query twice with very different parameter selectivity and compare the cached plan’s row estimates to actual. Use Ctrl+M in SSMS to capture both actual execution plans and note if the estimate matches the second call.

Challenge6. A colleague reports the server has 64GB RAM but query performance hasn’t improved since a recent upgrade from 16GB. Using the DMVs from this module, form a hypothesis for why RAM alone didn’t help, and what evidence you’d gather next.

Challenge7. A transaction log grew from 1GB to 50GB overnight with no obvious large operations in the app. List three architectural causes you’d investigate first, and the exact DMV/DBCC command for each.

Challenge8. The plan cache shows thousands of single-use ad-hoc plans consuming significant memory. Explain the architectural reason this happens and one setting that mitigates it.

Break-It9. Deliberately induce plan cache bloat: run 500 slightly-different ad-hoc (non-parameterized) queries in a loop, then measure plan cache growth with the query from Guided Lab 2. Then fix it by enabling “optimize for ad hoc workloads” and re-measure.

Break-It10. Deliberately induce log growth pressure: run a large batch DELETE inside an explicit transaction without committing for several minutes while inserting more rows elsewhere, then observe DBCC SQLPERF(LOGSPACE) growing. Resolve it by committing and note the log space reclaimed (or not, until a log backup, depending on recovery model).