02 - Storage Internals

31 min read

Storage Internals

Core Model

SQL Server manages rowstore data in a small number of physical units that every storage topic in this note builds on. Pages are the unit of I/O, extents are the unit of allocation, data and log files carry different durability guarantees, and VLFs are the internal slices that govern log reuse. The table below fixes the sizes and operational meaning of each unit so the later DMV output can be read against a concrete frame of reference.

UnitSizeWhat it means operationally
Page8 KBSQL Server reads and writes disk-based rowstore data one page at a time.
Extent64 KBAn extent is 8 contiguous pages and is the basic space-allocation unit.
Data fileVariableStores data pages, index pages, IAM pages, allocation maps, and metadata.
Log fileVariableStores the sequential transaction log that guarantees durability.
VLFVariableA virtual log file is an internal slice of the log file used for reuse and recovery.

Inspect The Current File Layout

Any sizing, growth, or capacity investigation on a SQL Server database starts with the physical file layout. Before looking at fragmentation, log pressure, or tempdb behavior, confirm how many files the database has, which volumes they live on, how they are allowed to grow, and whether any of them is uncapped. The sys.database_files catalog view is the authoritative source for this information.

sys.database_files | verify how the database is physically configured

sys.database_files is a per-database catalog view that returns one row per file composing the current database. It is the first query to run when investigating growth events, disk-space incidents, or unexpected capacity consumption, because it exposes file type, physical path, current size, growth increment, and maximum cap in a single result set.

List data and log files with size, growth, and cap

Report every physical file of the current database along with the normalized size, growth setting, and maximum cap. Percent growth is flagged separately so the operator can see at a glance whether growth will scale with file size.

List every physical file that composes the current database with its size, growth setting, and maximum cap.

SELECT
    file_id,
    name,
    type_desc,
    physical_name,
    CAST(size / 128.0 AS decimal(18,2)) AS size_mb,
    CASE max_size WHEN -1 THEN 'UNLIMITED' ELSE CAST(CAST(max_size / 128.0 AS decimal(18,2)) AS varchar(30)) END AS max_size_mb,
    CASE is_percent_growth WHEN 1 THEN CAST(growth AS varchar(20)) + '%'
         ELSE CAST(CAST(growth / 128.0 AS decimal(18,2)) AS varchar(30)) + ' MB'
    END AS growth_setting,
    is_percent_growth
FROM sys.database_files
ORDER BY file_id;
file_idnametype_descphysical_namesize_mbmax_size_mbgrowth_settingis_percent_growth
1stoxxROWS/var/opt/mssql/data/stoxx.mdf712.00UNLIMITED64.00 MB0
2stoxx_logLOG/var/opt/mssql/data/stoxx_log.ldf968.002097152.0064.00 MB0
file_id  name       type_desc  physical_name                  size_mb  max_size_mb  growth_setting  is_percent_growth
1        stoxx      ROWS       /var/opt/mssql/data/stoxx.mdf  712.00   UNLIMITED    64.00 MB        0
2        stoxx_log  LOG        /var/opt/mssql/data/stoxx_log.ldf  968.00   2097152.00   64.00 MB        0

The database currently has one data file and one log file, both growing in fixed 64 MB increments. Fixed growth is preferable to percentage growth because it keeps growth behavior predictable. The main production concern in this output is that the data file is allowed to grow without a defined cap; unlimited growth is easy to forget until the underlying volume becomes the real limit.

The reading from this output is straightforward: ROWS is the main data file, LOG is the transaction log file, UNLIMITED on max_size_mb means the file can keep growing until the volume fills, and is_percent_growth = 0 means growth is fixed rather than percentage-based.

Inspect A Real Data Page

Reading the file layout tells the operator how storage is structured at the file level, but it says nothing about how rows are laid out inside an 8 KB page. To reason about clustered-index scans, B-tree traversal, forwarding records, and page splits later in this note, it helps to first look at one real leaf page from a production table and read its header metadata and sibling linkage directly.

sys.dm_db_database_page_allocations + sys.dm_db_page_info | inspect one live data page

sys.dm_db_database_page_allocations enumerates the pages allocated to an object, and sys.dm_db_page_info reads the header of a specific page by (database_id, file_id, page_id). Used together they let the operator pick one real row-bearing page from a clustered index and examine its slot count, free bytes, and neighbor links without attaching a debugger or running undocumented DBCC commands.

Read header metadata and linkage of one leaf data page

Select one leaf DATA_PAGE from the clustered index of a production table, capture its file_id and page_id, then pass those identifiers to sys.dm_db_page_info to read the header.

Pick one real leaf data page from a production table and read its header metadata and linkage.

DECLARE @file_id int, @page_id int;
 
SELECT TOP (1)
    @file_id = allocated_page_file_id,
    @page_id = allocated_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'silver.eurostoxx50_ohlcv'), 1, NULL, 'DETAILED')
WHERE page_type_desc = 'DATA_PAGE'
ORDER BY allocated_page_file_id, allocated_page_page_id;
 
SELECT
    file_id,
    page_id,
    page_type_desc,
    page_level,
    object_id,
    index_id,
    is_mixed_extent,
    has_ghost_records,
    prev_page_file_id,
    prev_page_page_id,
    next_page_file_id,
    next_page_page_id,
    slot_count,
    free_bytes,
    fixed_length
FROM sys.dm_db_page_info(DB_ID(), @file_id, @page_id, 'DETAILED');
file_id  page_id  page_type_desc  page_level  object_id    index_id  is_mixed_extent  has_ghost_records  prev_page_file_id  prev_page_page_id  next_page_file_id  next_page_page_id  slot_count  free_bytes  fixed_length
1        2336     DATA_PAGE       0           1493580359   1         0                0                   1                  3295               1                  2337               88          8           76

This is a real leaf data page from the clustered index of silver.eurostoxx50_ohlcv. It is in file 1, linked to neighboring pages on both sides, contains 88 row slots, and has only 8 free bytes left. That is the physical reality behind a clustered-index scan or seek: the engine is traversing linked 8 KB pages like this one.

The operational readout is simple: DATA_PAGE means a normal row-bearing page, page_level = 0 means leaf level, is_mixed_extent = 0 means a uniform extent, and has_ghost_records = 0 means there is no deferred delete cleanup on this page.

Write-Ahead Logging And Log Health

The diagram below shows the write-ahead logging sequence from row change through log flush and later page flush.


flowchart LR
    A["Row Modification"] --> B["Log Records Generated"]
    B --> C["Log Records Flushed To .ldf"]
    C --> D["Transaction Can Commit"]
    D --> E["Dirty Page Can Flush Later"]

    style A fill:#292e42,stroke:#7aa2f7,stroke-width:2px,color:#c0caf5
    style B fill:#1a1b26,stroke:#565f89,color:#c0caf5
    style C fill:#1a1b26,stroke:#565f89,color:#c0caf5
    style D fill:#1f3b2d,stroke:#73d13d,color:#c0caf5
    style E fill:#24283b,stroke:#e0af68,color:#c0caf5

Write-ahead logging means the log is durable first and the data page is durable later. A committed row can still live only in memory for a while, but the change is already safe because the log record was flushed first.

sys.dm_db_log_space_usage | check current log pressure

sys.dm_db_log_space_usage reports the current size and used percentage of the transaction log for the database the query runs in, together with the number of bytes accumulated since the last log backup. Combined with the recovery model from sys.databases, it is the fastest way to tell whether the log is under pressure and whether the log-backup chain is keeping up with write activity.

Report log size, used percent, and log since last backup

Join sys.databases with sys.dm_db_log_space_usage to report the recovery model, total log size, used percent, free space, and how much log has accumulated since the last log backup. In FULL or BULK_LOGGED recovery, the last column is the single most important reuse signal.

Report the current log size, used percentage, and log accumulated since the last log backup.

SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    CAST(ls.total_log_size_in_bytes / 1048576.0 AS decimal(18,2)) AS total_log_mb,
    CAST(ls.used_log_space_in_bytes / 1048576.0 AS decimal(18,2)) AS used_log_mb,
    CAST(ls.used_log_space_in_percent AS decimal(9,2)) AS used_log_pct,
    CAST((ls.total_log_size_in_bytes - ls.used_log_space_in_bytes) / 1048576.0 AS decimal(18,2)) AS free_log_mb,
    CAST(ls.log_space_in_bytes_since_last_backup / 1048576.0 AS decimal(18,2)) AS log_since_last_backup_mb
FROM sys.databases AS d
CROSS JOIN sys.dm_db_log_space_usage AS ls
WHERE d.database_id = DB_ID();
database_name  recovery_model_desc  total_log_mb  used_log_mb  used_log_pct  free_log_mb  log_since_last_backup_mb
stoxx          FULL                 967.99        638.23       65.93         329.76       621.65

stoxx is in FULL recovery and about two thirds of the current log file is occupied. The most important operational signal is that more than 621 MB of log has accumulated since the last log backup. In FULL recovery, sustained growth in this column usually means the log-backup chain is absent, infrequent, or blocked by a reuse issue.

FULL recovery supports point-in-time recovery, but it depends on regular log backups. SIMPLE truncates the log at checkpoints and gives up point-in-time recovery through log backups. BULK_LOGGED reduces logging for some bulk operations, but it changes recovery semantics enough that it deserves explicit operational review.

For used_log_pct, values below 70 percent are usually comfortable, values from 70 to 90 percent require context from absolute free space and growth rate, and values above 90 percent need immediate attention. If log_reuse_wait_desc says LOG_BACKUP, take a log backup. If it says ACTIVE_TRANSACTION, identify the blocker with DBCC OPENTRAN. If the log has reached MAXSIZE, writes will fail with error 9002.

log_since_last_backup_mb should reset after log backups. If it climbs monotonically across repeated checks in FULL recovery, the backup chain is not keeping up with write activity.

sys.dm_db_log_info | inspect VLF count

sys.dm_db_log_info returns one row per virtual log file in the current database’s transaction log. The total number of VLFs, the number currently active, and the total VLF footprint together indicate whether the log has been fragmented by repeated small growth events, which can slow recovery, startup, and some log-reader operations.

Count total and active VLFs in the current log

Aggregate sys.dm_db_log_info to return total VLF count, active VLF count, and summed VLF size. A large total count with only a small active portion is healthy; a total close to or exceeding 200 is a sign that growth increments have been too small historically.

Count total and active VLFs to detect log fragmentation from repeated small growth events.

SELECT
    COUNT(*) AS vlf_count,
    SUM(CASE WHEN vlf_status = 2 THEN 1 ELSE 0 END) AS active_vlf_count,
    CAST(SUM(vlf_size_mb) AS decimal(18,2)) AS total_vlf_size_mb
FROM sys.dm_db_log_info(DB_ID());
vlf_count  active_vlf_count  total_vlf_size_mb
43         25                967.96

The current VLF layout is healthy. Forty-three VLFs for a roughly 968 MB log is not excessive, and the active portion is materially smaller than the total log. The practical takeaway is that this log is not currently suffering from pathological VLF fragmentation.

vlf_count below 50 is usually healthy for small and medium logs. Counts from 50 to 200 are worth monitoring because the same final size can hide very different growth histories. Counts above 200 are often excessive and can slow recovery, startup, and log-management tasks. active_vlf_count should remain materially lower than vlf_count; when the two are close, the log has little reusable space left.

sys.fn_dblog | confirm that one row change writes multiple log records

sys.fn_dblog exposes the active portion of the transaction log at the log-record level. It is valuable for one specific teaching point: a single business-level row modification is not a single log record. SQL Server also logs page formatting, allocation-map bits, and PFS updates, and sys.fn_dblog makes those secondary log records directly visible for a disposable test table.

Inspect recent log records for a single demo table

Filter sys.fn_dblog output by AllocUnitName to restrict results to one disposable table, then select the top five most recent log records to show the INSERT, page format, allocation-map update, and PFS modification that one row change generates.

Undocumented DMV

sys.fn_dblog is undocumented and unsupported by Microsoft. It is not part of the public DMV contract, its output columns can change between builds, and it can take schema locks while reading the active log.

Disposable diagnostic only

Use sys.fn_dblog only for interactive troubleshooting on controlled, disposable tables. Never embed it in application code, monitoring jobs, or recurring scheduled tasks. For supported log-health signals, prefer sys.dm_db_log_space_usage, sys.dm_db_log_info, and sys.dm_db_log_stats instead.

Read the most recent log records for a disposable demo table to reveal the multi-record cost of a single INSERT.

SELECT TOP (5)
    [Current LSN],
    Operation,
    Context,
    [Page ID],
    AllocUnitName
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName LIKE 'dbo.demo_storage_log%'
ORDER BY [Current LSN] DESC;
Current LSN             Operation       Context       Page ID          AllocUnitName
00000169:0001D9D0:001A  LOP_INSERT_ROWS  LCX_CLUSTERED 0001:00001c40   dbo.demo_storage_log.PK__demo_sto__3213E83FCD8EA637
00000169:0001D9D0:0016  LOP_FORMAT_PAGE  LCX_HEAP      0001:00001c40   dbo.demo_storage_log.PK__demo_sto__3213E83FCD8EA637
00000169:0001D9D0:000E  LOP_SET_BITS     LCX_IAM       0001:000072a2   dbo.demo_storage_log.PK__demo_sto__3213E83FCD8EA637
00000169:0001D9D0:000C  LOP_FORMAT_PAGE  LCX_IAM       0001:000072a2   dbo.demo_storage_log.PK__demo_sto__3213E83FCD8EA637
00000169:0001D9D0:0009  LOP_MODIFY_ROW   LCX_PFS       0001:00000001   dbo.demo_storage_log.PK__demo_sto__3213E83FCD8EA637

One row insert generated multiple log records. That is the operational reason write-heavy workloads are limited by more than row count alone: every change also touches allocation structures, page metadata, and durability bookkeeping.

LOP_INSERT_ROWS is the business-level row change. LOP_FORMAT_PAGE shows page initialization or reuse. LOP_SET_BITS shows allocation-map bookkeeping. LOP_MODIFY_ROW shows in-place metadata or row changes. LCX_CLUSTERED, LCX_IAM, and LCX_PFS indicate the storage structures that were touched.

Heap Forwarding Records

A heap is a table with no clustered index, so rows are stored in no particular key order. When an UPDATE widens a row beyond the free space remaining on its current page, SQL Server does not reorganize the heap; it moves the row to a page with enough space and leaves a forwarding pointer on the original page. Every subsequent read that lands on the original page pays an extra I/O to follow the pointer. Forwarding records therefore act as a structural tax that grows over time on any mutable heap, and sys.dm_db_index_physical_stats exposes their count directly.

Mutable heaps age poorly. A disposable heap with 200 narrow rows is used below to reproduce the failure mode: widening half the rows forces forwarding pointers to appear, which would otherwise require a long-running production workload to observe.

Setup | create a heap with 200 narrow rows

The first step of the reproduction is to create a table with no clustered index and seed it with 200 narrow rows that pack densely onto a single 8 KB page. This establishes a known clean starting state so the forwarding-record count can be measured before and after the widening update.

Create a heap and seed 200 narrow rows

Drop any previous copy of the demo table, create a heap with an int key column and a short varchar(800) payload, and insert 200 rows whose payload is 20 bytes of padding. The ROW_NUMBER() generator from sys.all_objects is a common idiom for producing a small sequential series without a numbers table.

Create a fresh heap and insert 200 narrow rows that pack densely onto a single page.

DROP TABLE IF EXISTS dbo.demo_storage_heap_forwarding;
 
CREATE TABLE dbo.demo_storage_heap_forwarding
(
    row_id int NOT NULL,
    payload varchar(800) NOT NULL
);
 
;WITH n (row_id) AS
(
    SELECT TOP (200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_objects
)
INSERT INTO dbo.demo_storage_heap_forwarding (row_id, payload)
SELECT n.row_id, REPLICATE('A', 20)
FROM n;
No result set returned.

Baseline | confirm a clean heap with zero forwarding records

Before widening any rows, capture the physical state of the heap so the post-update comparison has a known reference. sys.dm_db_index_physical_stats returns the page count, record count, and forwarded record count required to prove that the starting state is clean.

Measure heap page count and forwarding records before widening

Call sys.dm_db_index_physical_stats with the object id of the heap and index_id = 0, using the 'DETAILED' scanning mode to force a full read of every page so forwarded record counts are accurate.

Check the physical state of the heap before any widening update.

SELECT
    index_type_desc,
    page_count,
    record_count,
    forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.demo_storage_heap_forwarding'), 0, NULL, 'DETAILED');
index_type_desc  page_count  record_count  forwarded_record_count
HEAP             1           200           0

The fresh heap fits on one page and has no forwarded rows. This is the clean baseline state before any row widening occurs.

HEAP means there is no clustered index, so forwarding records are possible when a row grows. forwarded_record_count = 0 confirms the starting state is clean. Any value above zero means some rows were moved and lookups now require extra page visits.

Widen The Rows | expand half the rows beyond their original slot size

With the baseline established, an UPDATE is issued that makes half of the existing rows significantly larger than their current slot. Because the table is a heap, SQL Server has no clustered key order to preserve and relocates any widened row that no longer fits on its original page.

Widen half the rows to force forwarding pointers

Run a single UPDATE that replaces the payload of every even-numbered row with 500 bytes of padding. Updates of odd-numbered rows are untouched, leaving a mixed state where some slots are still on their original page and others point forward.

Double the payload size of every even-numbered row so many rows no longer fit their original slot.

UPDATE dbo.demo_storage_heap_forwarding
SET payload = REPLICATE('Z', 500)
WHERE row_id % 2 = 0;
No result set returned.

Post-change Validation | observe pages and forwarding records after widening

Re-running the same sys.dm_db_index_physical_stats query after the widening update quantifies the structural cost. The business row count is unchanged at 200, so any increase in page_count and any non-zero forwarded_record_count is attributable entirely to the storage layout response.

Re-measure page count and forwarding records after widening

Issue the identical query used in the baseline step. The page count, record count, and forwarded record count are the three fields that reveal how the heap absorbed the widening update.

Re-run the heap physical-stats query after the widening update to observe forwarding records.

SELECT
    index_type_desc,
    page_count,
    record_count,
    forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.demo_storage_heap_forwarding'), 0, NULL, 'DETAILED');
index_type_desc  page_count  record_count  forwarded_record_count
HEAP             8           294           94

The heap expanded from 1 page to 8 pages and now has 94 forwarded rows. That is the exact failure mode mutable heaps suffer from: row access becomes less direct over time because the original row location now points somewhere else.

page_count increased materially, so the heap now occupies more pages and reads have more physical work to do. record_count is higher than the business row count because forwarding artifacts are counted in the physical structure. forwarded_record_count = 94 means nearly half the rows now require a forwarding hop, which is a strong signal to rebuild or add a clustered index in production.

Page Splits

A page split occurs when an INSERT or UPDATE needs to place a row on a leaf page that no longer has enough free space to accept it. SQL Server allocates a new page, moves roughly half the existing rows onto it, and links the new page into the doubly-linked leaf chain. The result is extra space consumption, out-of-order leaf pages, and the fragmentation value that sys.dm_db_index_physical_stats reports as avg_fragmentation_in_percent. Splits are the natural counterpart to heap forwarding records: both are what happens when existing rows no longer fit their original slot.

Fragmentation percentage is not a threshold

Do not use avg_fragmentation_in_percent in isolation. Small objects can show dramatic percentages (50-100%) without being a maintenance priority because the absolute page count is trivial. Triggering rebuilds on percentage alone causes unnecessary I/O and log churn on objects where the split pattern is irrelevant.

Use page count and workload together

Combine avg_fragmentation_in_percent with page_count and the workload type before deciding to rebuild. Treat indexes below ~1,000 pages as noise regardless of percentage, focus maintenance on large indexes with sustained range scans, and prefer targeted fill-factor tuning over blind rebuilds for known hot-spot patterns.

Widening updates force page splits. A disposable clustered table with 200 narrow rows is used below to reproduce the split mechanism: widening every row forces the leaf level to allocate additional pages, and the fragmentation column reflects the result.

Setup | create a clustered table with 200 narrow rows

The first step mirrors the heap demo, but the table now carries a clustered primary key on row_id. SQL Server therefore maintains a key-ordered leaf chain, so any later widening update that cannot fit a row in place must split the affected page rather than relocate the row freely.

Create a clustered table and seed 200 narrow rows

Drop any previous copy of the table, create it with a PRIMARY KEY CLUSTERED on row_id, and seed it with 200 rows whose payload is 20 bytes of padding. The starting layout packs all rows onto a single leaf page.

Create a clustered-index table with 200 narrow rows packed densely on one leaf page.

DROP TABLE IF EXISTS dbo.demo_storage_page_splits;
 
CREATE TABLE dbo.demo_storage_page_splits
(
    row_id int NOT NULL,
    payload varchar(800) NOT NULL,
    CONSTRAINT PK_demo_storage_page_splits PRIMARY KEY CLUSTERED (row_id)
);
 
;WITH n (row_id) AS
(
    SELECT TOP (200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_objects
)
INSERT INTO dbo.demo_storage_page_splits (row_id, payload)
SELECT n.row_id, REPLICATE('A', 20)
FROM n;
No result set returned.

Baseline | confirm one leaf page with zero fragmentation

Before widening any rows, capture the leaf-level page count and fragmentation so the post-update result can be compared against a known clean state. The baseline also confirms that the table is small enough to fit on a single leaf page at the start.

Measure leaf page count and fragmentation before widening

Call sys.dm_db_index_physical_stats against the clustered index (index_id = 1) in 'DETAILED' mode and filter to index_level = 0 so only the leaf level is returned. Non-leaf levels are irrelevant to row-read performance in this example.

Measure leaf-level fragmentation and page count before any widening update.

SELECT
    index_level,
    CAST(avg_fragmentation_in_percent AS decimal(9,2)) AS avg_fragmentation_in_percent,
    page_count,
    record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.demo_storage_page_splits'), 1, NULL, 'DETAILED')
WHERE index_level = 0;
index_level  avg_fragmentation_in_percent  page_count  record_count
0            0.00                         1           200

The fresh clustered index is compact: one leaf page, no fragmentation worth discussing, and 200 rows stored in key order.

index_level = 0 isolates the leaf pages that affect row reads directly. avg_fragmentation_in_percent from 0 to 5 is generally healthy, 5 to 30 needs context, and above 30 is usually material on objects large enough to matter. page_count is the companion signal that tells you whether the percentage is actually worth acting on.

Widen The Rows | expand every row so the leaf level must split

Unlike the heap demo, every row is widened this time. Because the clustered index must preserve key order, the engine cannot relocate individual rows freely; instead it allocates new leaf pages and splits the existing page contents across them.

Widen every row to force leaf-level page splits

Run a single UPDATE that replaces the payload of every row with 500 bytes of padding. The clustered key column is not touched, so the update is purely about fitting larger rows into the existing leaf chain.

Widen every row so the leaf level must allocate more pages to hold the payload.

UPDATE dbo.demo_storage_page_splits
SET payload = REPLICATE('Y', 500);
No result set returned.

Post-change Validation | observe page count and fragmentation after widening

Re-running the same leaf-level query after the widening update makes the split visible: the leaf page count rises, fragmentation appears where there was none, and the record count stays fixed at the original 200.

Re-measure leaf page count and fragmentation after widening

Issue the identical leaf-level query used in the baseline. The change in page_count and avg_fragmentation_in_percent relative to the baseline is the storage cost imposed by the widening update.

Re-run the leaf-level physical-stats query after the widening update to observe the page splits.

SELECT
    index_level,
    CAST(avg_fragmentation_in_percent AS decimal(9,2)) AS avg_fragmentation_in_percent,
    page_count,
    record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.demo_storage_page_splits'), 1, NULL, 'DETAILED')
WHERE index_level = 0;
index_level  avg_fragmentation_in_percent  page_count  record_count
0            12.00                        25          200

The row count stayed at 200, but the leaf level expanded from 1 page to 25 pages and fragmentation rose from 0% to 12%. On an object this small the exact percentage is not actionable on its own; the structural effect is what matters: widening rows forces the clustered index to allocate additional leaf pages and breaks the original dense layout.

page_count jumped from 1 to 25, so the same rows now require much more leaf storage. avg_fragmentation_in_percent = 12.00 is not a maintenance trigger by itself on a tiny object, but it proves the split mechanism. record_count stayed constant, which confirms the growth is structural rather than driven by more business rows.

tempdb Space By Category

tempdb is shared by every session on the instance and absorbs several distinct kinds of workload: temp tables and table variables from user sessions, worktables and sorts from the query executor, and row versions from snapshot isolation, RCSI, and online index operations. When tempdb is under pressure, the useful question is not how full it is overall but which category is consuming the space, because the remediation is different for each one.

tempdb.sys.dm_db_file_space_usage | see what is consuming tempdb

sys.dm_db_file_space_usage returns per-file allocation counters for every database, but when run against tempdb specifically it exposes the page-count breakdown by category that the operator needs to localize pressure: free pages, version store, user objects, internal objects, and mixed extents.

Break down tempdb by unallocated, version store, user, internal, and mixed

Aggregate the reserved-page counts across all tempdb files and convert from 8 KB pages to megabytes for each category. The resulting five columns tell the operator whether pressure is coming from versioning, user scratch objects, internal executor workspace, or nothing at all.

Break down current tempdb usage by allocation category: free, version store, user objects, internal objects, and mixed extents.

SELECT
    CAST(SUM(unallocated_extent_page_count) * 8.0 / 1024 AS decimal(18,2)) AS unallocated_mb,
    CAST(SUM(version_store_reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS version_store_mb,
    CAST(SUM(user_object_reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS user_object_mb,
    CAST(SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS internal_object_mb,
    CAST(SUM(mixed_extent_page_count) * 8.0 / 1024 AS decimal(18,2)) AS mixed_extent_mb
FROM tempdb.sys.dm_db_file_space_usage;
unallocated_mb  version_store_mb  user_object_mb  internal_object_mb  mixed_extent_mb
2618.44         0.00              2.44            1.06                2.06

tempdb is healthy at capture time. More than 2.6 GB inside the current files is free, version store usage is effectively zero, and both user and internal object footprints are tiny. This is what an uncongested tempdb looks like.

version_store_mb near zero means snapshot isolation, RCSI, or online-maintenance versioning is not stressing tempdb right now. Sustained growth points to long-running versioned transactions. user_object_mb should be low and transient; high persistent usage points to temp-table pressure or poor cleanup. internal_object_mb should also stay low; high or rising values usually point to spills, sorts, hashes, or other workspace pressure.

Production Recommendations

The following recommendations translate the storage internals covered in the preceding sections into operating rules for production databases. They apply to any OLTP or mixed workload on SQL Server and cover file growth, log backups, heap design, index maintenance, and tempdb monitoring.

  • Use fixed MB growth, not percent growth, for both data and log files.
  • Do not leave primary data files effectively uncapped in production unless the underlying storage layer is explicitly managed and monitored.
  • In FULL recovery, monitor log_since_last_backup_mb and the actual log-backup cadence together. Log reuse depends on regular log backups — schedule them at intervals short enough that the log never approaches its MAXSIZE between backups (every 5–15 minutes for write-heavy OLTP, every 30–60 minutes for lighter workloads). If log_since_last_backup_mb climbs monotonically across multiple checks, the backup chain is absent or stalled and the log will eventually fill.
  • Avoid mutable heaps for long-lived OLTP or frequently updated tables. Forwarding records are a structural tax, not a cosmetic issue.
  • Treat page splits as a design signal first. Sequential clustering, narrower rows, and lower churn usually matter more than blind rebuilds. When leaf_allocation_count in sys.dm_db_index_operational_stats shows a specific index splitting frequently, lower that index’s fill_factor (e.g., from the default 100% to 90% or 80%) with ALTER INDEX [ix] ON [table] REBUILD WITH (FILLFACTOR = 90), then re-check leaf_allocation_count over the next workload cycle to confirm splits decreased — if they did not, the root cause is key choice, not fill factor.
  • Watch tempdb version store and internal object space when troubleshooting snapshot workloads, spills, or online maintenance.

Graph View

Backlinks