Welcome back. This lesson is one of the most important “internals” lessons in the entire course.
If you understand pages and extents, you will understand why SQL Server behaves the way it does with storage,
why indexes speed things up (or slow things down), and why some queries are fast today and slow tomorrow.
SQL Server does not read “rows” from disk. It reads pages. Pages are grouped into extents.
Those two facts drive I/O, caching, fragmentation, and performance tuning.
Lesson description: You will learn how SQL Server stores data in 8 KB pages and 64 KB extents,
how SQL Server reads/writes pages, how the buffer pool caches pages in memory, and why logical reads vs physical reads matter.
You will learn key page types, the difference between heap and clustered storage at a physical level,
and how fragmentation creates extra I/O. You will also run safe, practical scripts to observe page activity and I/O evidence.
A SQL Server page is the basic unit of data storage and I/O inside the database.
A standard data page is 8 KB (8192 bytes).
When SQL Server needs data, it typically reads entire 8 KB pages from disk into memory.
Even if your query needs only one row, SQL Server still brings the whole page.
SQL Server performance often comes down to how many pages a query must touch.
Fewer pages = fewer reads = faster.
An extent is a group of 8 pages.
Extents matter because SQL Server allocates space in chunks and often performs more efficiently when data
is laid out in contiguous extents. Fragmentation breaks that contiguity.
Not all pages store table rows. SQL Server uses many page types, including:
DBA note: You do not need to memorize every page type today.
You must understand: SQL Server stores and moves data in pages, and allocation metadata controls where pages go.
SQL Server has two main extent allocation patterns:
Historically, SQL Server used mixed extents for very small objects and then switched to uniform extents as objects grew.
This is why very small tables and small temporary objects can have different allocation behavior than larger tables.
Why it matters for DBAs: Allocation behavior impacts tempdb contention, small object growth patterns,
and can influence performance under high concurrency.
When SQL Server reads a page from disk, it typically places it into memory (buffer pool).
If another query needs the same page later, SQL Server may serve it from memory without re-reading from disk.
A logical read means SQL Server read a page from memory (buffer pool).
It still “counts” because SQL Server had to touch that page, but it did not hit the storage subsystem.
A physical read means SQL Server had to go to disk/storage to fetch the page
because it was not already in memory.
DBA takeaway: Many performance issues are “I/O problems” because queries touch too many pages,
or because the needed pages are not in memory and must be physically read.
Fragmentation is not a “magical percentage.” Physically, fragmentation means pages/extents are no longer laid out
in a clean sequential order on disk. When SQL Server performs range scans (common for reporting and index scans),
fragmentation can cause:
Important: Fragmentation impacts some workloads more than others.
OLTP point lookups often care less; large scans and ordered range queries can care more.
You will learn how to decide later—today you focus on what it is physically.
A heap is stored as “unordered pages.” SQL Server can insert rows into available space in any data page,
and there is no built-in ordering.
Heaps can be fine for certain staging tables, but they can create DBA headaches:
A clustered index stores the table data in key order at the leaf level of the B-tree.
That ordering influences I/O patterns: range queries can become much more efficient when the key aligns with access patterns.
DBA takeaway: Index design is really about controlling which pages SQL Server must read to answer queries.
You will now use a few safe scripts to observe I/O at a high level.
These scripts do not require special trace flags and do not change data.
This prints logical reads and physical reads in the Messages tab when you run a query.
SET STATISTICS IO ON; GO
Use AdventureWorks if installed. If you don’t have it yet, use any database with a table of your choice.
Example (AdventureWorks commonly has Person.Person):
-- Example: adjust database/table names to your environment
USE AdventureWorks;
GO
SELECT TOP (100)
BusinessEntityID,
FirstName,
LastName
FROM Person.Person
ORDER BY BusinessEntityID;
GO
Look at the Messages tab for:
Run the same query again. Often, physical reads drop because pages are now cached in memory.
This demonstrates why “first run” and “second run” timing can differ.
DBA caution: Do not “benchmark” SQL with random repeated runs without understanding cache effects.
DBAs measure carefully using repeatable methods and controlled conditions.
SET STATISTICS IO OFF; GO
SQL Server tracks file-level I/O statistics per database file. This can help DBAs spot:
hot files, slow reads/writes, and latency patterns. Run this safely:
-- File-level I/O stats (safe read-only view)
SELECT
DB_NAME(vfs.database_id) AS DatabaseName,
mf.name AS LogicalFileName,
mf.type_desc,
mf.physical_name,
vfs.num_of_reads,
vfs.num_of_writes,
vfs.io_stall_read_ms,
vfs.io_stall_write_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;
How to interpret: High stall time can indicate slow storage, heavy workload, or both.
Later sections will teach you how to interpret I/O stalls properly.
SET STATISTICS IO ON.C:\DBA\Logs\section6-lesson3-pages-extents-io-proof.txtNext, you will learn how the transaction log works internally and why log behavior drives recovery,
restore strategy, and many “why is my log growing?” emergencies in production.
Not a member yet? Register now
Are you a member? Login now