Forwarded Records: the Back-Pointer and the Trip Home

2026-08-21 · by Hannah Vernon · in Internals

Illustration of a row of navy and steel-blue mailboxes; one holds a forwarding card connected by a glowing gold thread to a parcel at a distant mailbox, while a letter carrier follows the thread back carrying a smaller parcel

Note: a self-contained MCVE reproducing every result in this post is included at the end – see "The MCVE: run it yourself".

Hugo Kornelis recently challenged a sentence in my post on how SQL Server stores forwarded records. The sentence claimed that a forwarded record carries a back-pointer to its stub, and that the engine uses it to move the row home if it shrinks. It was a fair challenge: I’d taken both claims on faith from sources I trust, but I hadn’t verified either one myself.

Paul Randal wrote about the back-pointer back in 2009, and in a 2016 comment on that post he noted that while a forwarded row can move back, it’s “devilishly difficult” to get all the conditions to line up for a demo.^[1] This post shows the back-pointer in raw bytes, catches a row moving home, and works out the conditions that decide whether the trip happens at all.

Tests ran on SQL Server 2019 (CU32) and SQL Server 2025 (RTM). Every result below is identical on both versions.

Setting the stage

First, a recap of the mechanism. When an UPDATE grows a heap row past what its page can hold, SQL Server moves the row to another page and leaves a 9-byte forwarding stub at the old address. Nonclustered indexes keep pointing at the stub, so none of them need to be touched. The row’s RID never changes; the stub redirects any process that looks for the row in its original location.^[2]

Here’s a small heap to play with:

CREATE TABLE dbo.fwd_demo

(

id int NOT NULL

, v varchar(8000) NOT NULL

);

INSERT INTO dbo.fwd_demo (id, v)

SELECT n, REPLICATE('a', 750)

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS t(n);

Ten rows of 750 characters each. Nine land on the first data page, which I’ll call page 344 since that’s what my test database assigned. Now grow row 5 so it can’t stay put:

UPDATE dbo.fwd_demo

SET v = REPLICATE('b', 3000)

WHERE id = 5;

sys.dm_db_index_physical_stats confirms one forwarded record. %%physloc%% still reports row 5 at its original address, slot 4 on page 344. That’s the stub’s location, not the row’s. The RID is the row’s permanent public address, and stability is the entire point of the forwarding mechanism. If you want to know where the bytes actually live, you have to go find them.

The back-pointer, in bytes

DBCC PAGE on page 344 shows slot 4 has become a FORWARDING_STUB, nine bytes, “Forwarding to = file 1 page 345 slot 1”. That much matches the documentation.

The forwarded record on page 345 is 3,027 bytes: 3,000 characters of data plus 15 bytes of ordinary record overhead plus 12 bytes that weren’t there before. DBCC PAGE prints a decoded “Forwarded from = file 1 page 344 slot 4” line for the record (it didn’t when Paul wrote about the back-pointer in 2009; he’d rewritten DBCC PAGE for SQL Server 2005 and admits he forgot to make it decode the back-pointer). It still doesn’t tell you which bytes hold that pointer, so let’s find them in the memory dump.

The last ten bytes of the record dump are:

1	0004 5801 0000 0100 0400

Reading the little-endian pieces:

0004 = 1024, a marker value Paul describes as the special column ID signifying a back-pointer. It’s not a real column ID. Tables top out at 1,024 columns (30,000 if you use column sets over sparse columns, which live in a different structure inside the record). Since the storage engine numbers column IDs from zero, 1024 is the 1,025th value: the first one an ordinary in-row column can’t occupy, which makes it a safe sentinel.^[3]
5801 0000 = page 344.
0100 = file 1.
0400 = slot 4.

File 1, page 344, slot 4: the stub’s location. The remaining 2 bytes of the 12-byte growth are the extra entry in the variable-length column offset array that the back-pointer occupies.

Paul proved the same thing in 2009 with size arithmetic: create a nonunique clustered index on the heap and the forwarded record shrinks by exactly 10 bytes (the back-pointer goes away, and a 2-byte uniquifier offset entry appears, netting -10 against ordinary rows which grow by 2). I reproduced that too: 3,027 bytes as a heap forwarded record, 3,017 under the clustered index. Two independent methods, same answer.

What the back-pointer is actually for

The back-pointer’s primary purpose isn’t the return trip; it’s keeping the stub current when the row moves again.

Grow row 5 again while it’s forwarded, enough that it can’t stay on page 345 either:

UPDATE dbo.fwd_demo

SET v = REPLICATE('c', 7500)

WHERE id = 5;

The record moves to a third page. Does page 345 now hold a second stub, creating a chain? No. The original stub on page 344 is updated in place and now reads “Forwarding to = file 1 page 346 slot 0”. Page 345 is left clean. The engine could only do that because the forwarded record knew where its stub was. Without the back-pointer, every re-move would either leave a trail of hops or force a scan to find the stub.

Catching the row moving home

Now for the claim Hugo questioned. Shrink row 5 back down while the home page still has room:

UPDATE dbo.fwd_demo

SET v = REPLICATE('d', 750)

WHERE id = 5;

Result: the stub is gone. Slot 4 on page 344 is a normal PRIMARY_RECORD again, the forwarded record count drops to zero, and the row’s RID is unchanged. The row went home on the first try. No index rebuild, no ALTER TABLE REBUILD, just an ordinary UPDATE.

So in the simple case, move-back isn’t difficult at all. Which raises the question: why did Paul call it devilishly difficult? Because the simple case has three hidden conditions, and production heaps rarely satisfy all of them.

Condition 1: the row returns only when an UPDATE touches it

The engine never returns a forwarded row lazily. I filled the home page, shrank the forwarded row (it stayed forwarded, correctly, since there was no room), then freed the home page’s space again. The row stayed forwarded. Nothing happens until the next UPDATE touches the forwarded row – and then any UPDATE counts. A no-op (SET v = v) brought the row home in my tests. The check runs whenever the row is updated; the update doesn’t have to change anything.

There’s no background task that cleans up forwarding the way ghost cleanup removes deleted records. If your workload never updates a forwarded row again, the forwarding persists until you rebuild the heap (ALTER TABLE ... REBUILD), create a clustered index, or delete the row.

Condition 2: the row must fit – and “fit” is layout-dependent

This one took some work to pin down.

I built a state where the home page reported 1,949 free bytes, then shrank the forwarded row to 750 characters (a 765-byte record). A 765-byte record against 1,949 free bytes should fit. It didn’t move. Shrinking to 400 characters worked. Somewhere in between was a threshold, and it didn’t line up with the page’s total free space or with any percentage-based rule.

The explanation is in the page’s physical layout. Free space on a page comes in two flavors: the contiguous chunk between the end of the last row and the slot array, and the fragments (holes) scattered where deleted or shrunk rows used to be. The returning record needs enough gatherable space, and how much the engine can gather depends on where the holes sit. Two useful reference points from the boundary testing, each reproducible to the byte:

The floor is the contiguous chunk plus 9 bytes (the stub the return deletes – its space counts toward the budget). On one heavily churned page the boundary sat at exactly contiguous + 9: 649 + 9 = 658 bytes, with a 665-byte record stuck and a 658-byte record returning.
The engine can do better than the floor: it reorganizes the page to a limited extent. On a page with 649 contiguous bytes and two 500-byte holes, the boundary landed at 1,158 bytes = contiguous + one hole + 9. On a page with an extra stub and three holes, it landed at 1,658 = contiguous + two holes + 9.

Which holes the engine can reclaim varies with the layout; I haven’t found a closed-form rule that predicts every construction, and I’d be interested to hear from anyone who has. What I can say: for a given page layout the boundary is deterministic, exact, and identical on SQL Server 2019 and 2025.

Contrast this with the grow-in-place case: when a row grows, SQL Server compacts the page to keep the row where it is whenever the total free space allows. The return trip gets the limited version of that courtesy: some reorganization, but not full compaction.

One tooling footnote from these tests: on the builds I tested (2019 CU32 and 2025 RTM), the sys.dm_db_page_info column holding the free-space offset is named free_bytes_offset, though the documentation calls it free_data_offset. Query the function’s result set with sys.dm_exec_describe_first_result_set if a documented column comes up Msg 207.

Condition 3: the home page still has to have that room

Obvious, but worth stating because of why it tends to fail. Heap inserts are placed using PFS pages, which track each page’s fullness in coarse buckets: empty, up to 50%, up to 80%, up to 95%, and up to 100% full.^[4] A page that forwarded a row away has free space, so it advertises itself as a landing spot for new inserts. In an active heap, that hole gets refilled quickly, and the space is gone before any update gives the row a chance to return.

The PFS buckets govern where inserts land. The move-back decision itself doesn’t use them; it evaluates the actual byte layout of the home page. Coarse filter for placement, exact check for the return.

Putting it together

The original sentence in my post holds up: the back-pointer exists, and the engine really does use the forwarding machinery to move a shrinking row home. But the fuller picture is worth the extra paragraph:

The back-pointer’s primary purpose is keeping the one-and-only stub current when the row moves again. No chains, ever.
Move-back happens only when an UPDATE touches the forwarded row – and any update counts, including one that changes nothing. There’s no background cleanup.
The returning record must fit a layout-dependent budget on the home page: at minimum the contiguous free chunk plus the reclaimed 9-byte stub, and often one or more free-space holes on top – the engine reorganizes the page to a limited extent, identically on SQL Server 2019 and 2025.
Refilled pages and never-updated rows are why forwarded records accumulate in real heaps, and why ALTER TABLE ... REBUILD remains the practical cure.

My thanks to Hugo for pushing on that sentence. Verifying a claim you’ve repeated in public is a useful kind of discomfort; this one turned out to hold far more than one sentence’s worth.

Questions? Something I should test next? Find me on Bluesky or LinkedIn, or leave a comment below.

The MCVE: run it yourself

Everything above is reproducible with the self-contained script below. It needs SQL Server 2019 or later, sysadmin (for DBCC PAGE), and nothing else: it creates a scratch database named fwd_mcve, computes every page number dynamically, finds the fit boundary on your build with a binary search, and leaves cleanup commented out at the end. Expected results are labeled in each step’s output.

/*
    forwarded-records-mcve.sql

Self-contained repro for the blog post
    "Forwarded Records: the Back-Pointer and the Trip Home"
    https://www.sqlserverscience.com/internals/forwarded-records-the-back-pointer-and-the-trip-home/

Demonstrates, on any SQL Server 2019+ instance:
      STEP 1 - forwarding: a growing heap row moves, leaving a 9-byte stub
      STEP 2 - the back-pointer: the forwarded record's last 10 bytes hold a
               marker (1024) plus the RID of the stub (shown via DBCC PAGE)
      STEP 3 - move-back: shrinking the row returns it to its original slot
      STEP 4 - no lazy return: a forwarded row does not come home when space
               frees up.  The check runs only when an UPDATE touches the
               forwarded row - and ANY update counts, including a no-op
               SET v = v.
      STEP 5 - the fit rule, found empirically: a binary search locates the
               largest record that can return to a fragmented home page.  The
               boundary exceeds the page's contiguous free space: the engine
               performs a limited on-page reorganization, and the exact
               budget depends on the layout of free-space holes.  SQL Server
               2019 and 2025 produce identical boundaries.

Requirements:
      - SQL Server 2019 or later (sys.dm_db_page_info)
      - sysadmin (DBCC PAGE, DBCC TRACEON)
      - Creates and drops a scratch database named fwd_mcve

Everything is computed dynamically: no hard-coded page numbers.  Run the
    whole script in one go, or step through batch by batch and inspect.
*/

USE [master];
GO

IF DB_ID(N'fwd_mcve') IS NOT NULL
BEGIN
    ALTER DATABASE [fwd_mcve] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [fwd_mcve];
END;
GO

CREATE DATABASE [fwd_mcve];
GO

USE [fwd_mcve];
GO

SET NOCOUNT ON;

CREATE TABLE [dbo].[fwd_demo]
(
      [id] int           NOT NULL
    , [v]  varchar(8000) NOT NULL
);

/* ten rows of 750 characters fill most of one 8 KB page */
DECLARE @i int = 1;
WHILE @i <= 10
BEGIN
    INSERT INTO [dbo].[fwd_demo] ( [id], [v] ) VALUES ( @i, REPLICATE('a', 750) );
    SET @i += 1;
END;
GO

PRINT '===== STEP 1: force forwarding by growing row 5 =====';

/* location of every row before the update */
SELECT
      [phase]      = 'before'
    , [id]         = [fd].[id]
    , [row_chars]  = LEN([fd].[v])
    , [physloc]    = CONVERT(varchar(11), [p].[file_id]) + ':'
                   + CONVERT(varchar(11), [p].[page_id]) + ':'
                   + CONVERT(varchar(11), [p].[slot_id])
FROM
    [dbo].[fwd_demo] AS [fd]
CROSS APPLY
    sys.fn_PhysLocCracker([fd].%%physloc%%) AS [p]
ORDER BY
    [fd].[id];

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('b', 3000) WHERE [id] = 5;

/* %%physloc%% for row 5 is UNCHANGED - it reports the stub's RID, not the
   physical location of the forwarded record */
SELECT
      [phase]      = 'after'
    , [id]         = [fd].[id]
    , [row_chars]  = LEN([fd].[v])
    , [physloc]    = CONVERT(varchar(11), [p].[file_id]) + ':'
                   + CONVERT(varchar(11), [p].[page_id]) + ':'
                   + CONVERT(varchar(11), [p].[slot_id])
FROM
    [dbo].[fwd_demo] AS [fd]
CROSS APPLY
    sys.fn_PhysLocCracker([fd].%%physloc%%) AS [p]
WHERE
    [fd].[id] = 5;

SELECT
      [forwarded_records] = [ips].[forwarded_record_count]
    , [record_count]      = [ips].[record_count]
    , [page_count]        = [ips].[page_count]
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];
GO

PRINT '===== STEP 2: dump the stub and the forwarded record =====';
PRINT 'In the FORWARDED_RECORD dump below, the record''s last 10 bytes decode';
PRINT 'little-endian as: 2-byte marker 0x0400 (1024), 4-byte page id,';
PRINT '2-byte file id, 2-byte slot id = the RID of the stub.';
PRINT 'DBCC PAGE also prints it decoded as "Forwarded from = ...".';

DBCC TRACEON(3604);

DECLARE
      @page_id int
    , @sql     nvarchar(200);

DECLARE [page_cursor] CURSOR LOCAL FAST_FORWARD FOR
SELECT
    [pa].[allocated_page_page_id]
FROM
    sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [pa]
WHERE
    [pa].[is_allocated] = 1
    AND [pa].[page_type] = 1    /* data pages only */
ORDER BY
    [pa].[allocated_page_page_id];

OPEN [page_cursor];
FETCH NEXT FROM [page_cursor] INTO @page_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '----- DBCC PAGE (fwd_mcve, 1, ' + CONVERT(varchar(11), @page_id) + ', 3) -----';
    SET @sql = N'DBCC PAGE (N''fwd_mcve'', 1, ' + CONVERT(nvarchar(11), @page_id) + N', 3);';
    EXEC sys.sp_executesql @sql;
    FETCH NEXT FROM [page_cursor] INTO @page_id;
END;
CLOSE [page_cursor];
DEALLOCATE [page_cursor];
GO

PRINT '===== STEP 3: shrink row 5 - it moves back to its original slot =====';

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('c', 750) WHERE [id] = 5;

SELECT
      [id]         = [fd].[id]
    , [row_chars]  = LEN([fd].[v])
    , [physloc]    = CONVERT(varchar(11), [p].[file_id]) + ':'
                   + CONVERT(varchar(11), [p].[page_id]) + ':'
                   + CONVERT(varchar(11), [p].[slot_id])
FROM
    [dbo].[fwd_demo] AS [fd]
CROSS APPLY
    sys.fn_PhysLocCracker([fd].%%physloc%%) AS [p]
WHERE
    [fd].[id] = 5;

SELECT
      [forwarded_records] = [ips].[forwarded_record_count]   /* expect 0 */
    , [record_count]      = [ips].[record_count]             /* stub gone */
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];
GO

PRINT '===== STEP 4: no lazy return; any UPDATE triggers the check =====';
PRINT 'Re-forward row 5, consume the home page''s space, shrink row 5 (stays';
PRINT 'forwarded), then free the space again.  The row does NOT return on its';
PRINT 'own.  It returns on the next UPDATE that touches it - even a no-op.';

/* re-forward row 5 */
UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('d', 3000) WHERE [id] = 5;

/* grow neighbours IN PLACE to use up the home page's free space - 1400 chars
   each is enough to block row 5's return without forwarding rows 1,2 too */
UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('e', 1400) WHERE [id] = 1;
UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('e', 1400) WHERE [id] = 2;

/* shrink row 5: no room at home, so it stays forwarded */
UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('f', 750) WHERE [id] = 5;

SELECT
      [checkpoint_label]  = 'home page full: row 5 stays forwarded (expect 1)'
    , [forwarded_records] = [ips].[forwarded_record_count]
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];

/* free the home page's space again */
UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('g', 750) WHERE [id] = 1;
UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('g', 750) WHERE [id] = 2;

SELECT
      [checkpoint_label]  = 'space freed, row 5 untouched: still forwarded'
    , [forwarded_records] = [ips].[forwarded_record_count]
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];

/* even a no-op update runs the move-back check - the row goes home */
UPDATE [dbo].[fwd_demo] SET [v] = [v] WHERE [id] = 5;

SELECT
      [checkpoint_label]  = 'after no-op update: row RETURNS (any update triggers the check)'
    , [forwarded_records] = [ips].[forwarded_record_count]
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];
GO

PRINT '===== STEP 5: find the fit boundary empirically =====';
PRINT 'A fresh fragmented state is built for every probe, and a binary search';
PRINT 'finds the largest record that can return home.  The boundary exceeds';
PRINT 'contiguous + 9: the engine reorganizes the page to a limited extent.';
PRINT 'For THIS construction (two 500-byte holes) the boundary lands at';
PRINT 'contiguous + 500 + 9 on both SQL Server 2019 and 2025.';
GO

/* rebuild a known fragmented state from scratch for a single probe:
   10 x 750 rows, row 5 forwarded, rows 3,4 grown 1400 / shrunk 900 =
   two ~500-byte holes plus the contiguous region */
CREATE PROCEDURE [dbo].[probe_return]
      @attempt_chars int
    , @returned      bit          OUTPUT
    , @free_bytes    int          OUTPUT
    , @contiguous    int          OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

DECLARE
          @home_page int
        , @free_col  sysname
        , @free_data int
        , @slots     int
        , @fwd1      int
        , @fwd2      int
        , @i         int = 1
        , @sql       nvarchar(600);

IF OBJECT_ID(N'[dbo].[probe]', N'U') IS NOT NULL
    BEGIN
        DROP TABLE [dbo].[probe];
    END;

CREATE TABLE [dbo].[probe]
    (
          [id] int           NOT NULL
        , [v]  varchar(8000) NOT NULL
    );

WHILE @i <= 10
    BEGIN
        INSERT INTO [dbo].[probe] ( [id], [v] ) VALUES ( @i, REPLICATE('a', 750) );
        SET @i += 1;
    END;

UPDATE [dbo].[probe] SET [v] = REPLICATE('b', 3000) WHERE [id] = 5;
    UPDATE [dbo].[probe] SET [v] = REPLICATE('c', 1400) WHERE [id] = 3;
    UPDATE [dbo].[probe] SET [v] = REPLICATE('c', 1400) WHERE [id] = 4;
    UPDATE [dbo].[probe] SET [v] = REPLICATE('d', 900)  WHERE [id] = 3;
    UPDATE [dbo].[probe] SET [v] = REPLICATE('d', 900)  WHERE [id] = 4;

SELECT
        @home_page = [p].[page_id]
    FROM
        [dbo].[probe] AS [fd]
    CROSS APPLY
        sys.fn_PhysLocCracker([fd].%%physloc%%) AS [p]
    WHERE
        [fd].[id] = 5;

/* sys.dm_db_page_info names this column free_data_offset in the ver17
       documentation, but the builds tested (2019 CU32, 2025 RTM) both use
       free_bytes_offset - detect at runtime */
    SELECT
        @free_col = [rs].[name]
    FROM
        sys.dm_exec_describe_first_result_set(
            N'SELECT * FROM sys.dm_db_page_info(1, 1, 1, N''LIMITED'');', NULL, 0) AS [rs]
    WHERE
        [rs].[name] IN (N'free_data_offset', N'free_bytes_offset');

SET @sql = N'
    SELECT
          @free_data  = [pi].' + QUOTENAME(@free_col) + N'
        , @slots      = [pi].[slot_count]
        , @free_bytes = [pi].[free_bytes]
    FROM
        sys.dm_db_page_info(DB_ID(), 1, @home_page, N''DETAILED'') AS [pi];';

EXEC sys.sp_executesql
          @sql
        , N'@home_page int, @free_data int OUTPUT, @slots int OUTPUT, @free_bytes int OUTPUT'
        , @home_page  = @home_page
        , @free_data  = @free_data  OUTPUT
        , @slots      = @slots      OUTPUT
        , @free_bytes = @free_bytes OUTPUT;

SET @contiguous = 8192 - @free_data - (2 * @slots);

SELECT
        @fwd1 = [ips].[forwarded_record_count]
    FROM
        sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[probe]'), 0, NULL, N'DETAILED') AS [ips];

SET @sql = N'UPDATE [dbo].[probe] SET [v] = REPLICATE(''z'', ' + CONVERT(nvarchar(11), @attempt_chars) + N') WHERE [id] = 5;';
    EXEC sys.sp_executesql @sql;

SELECT
        @fwd2 = [ips].[forwarded_record_count]
    FROM
        sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[probe]'), 0, NULL, N'DETAILED') AS [ips];

SET @returned = CASE WHEN @fwd2 < @fwd1 THEN 1 ELSE 0 END;
END;
GO

/* binary search: largest record size (chars) that returns home */
DECLARE
      @lo         int = 200      /* known to return */
    , @hi         int = 2400     /* known to stay stuck */
    , @mid        int
    , @returned   bit
    , @free_bytes int
    , @contiguous int;

WHILE @hi - @lo > 1
BEGIN
    SET @mid = (@lo + @hi) / 2;
    EXEC [dbo].[probe_return]
          @attempt_chars = @mid
        , @returned      = @returned   OUTPUT
        , @free_bytes    = @free_bytes OUTPUT
        , @contiguous    = @contiguous OUTPUT;
    IF @returned = 1
    BEGIN
        SET @lo = @mid;
    END
    ELSE
    BEGIN
        SET @hi = @mid;
    END;
END;

/* this construction leaves two ~500-byte holes; the record overhead for the
   probe table is 15 bytes, so boundaries convert as record = chars + 15 */
SELECT
      [found_boundary_chars]  = @lo
    , [found_boundary_bytes]  = @lo + 15
    , [free_bytes]            = @free_bytes
    , [contiguous_free]       = @contiguous
    , [ref_no_reorg_bytes]    = @contiguous + 9        /* if the engine did not reorganize */
    , [ref_this_layout_bytes] = @contiguous + 500 + 9  /* observed: one 500-byte hole reclaimed */
    , [note] = 'boundary > ref_no_reorg proves on-page reorganization; identical on 2019 and 2025';
GO
DBCC TRACEOFF(3604);
GO

/* cleanup - uncomment to drop the scratch database
USE [master];
GO
ALTER DATABASE [fwd_mcve] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [fwd_mcve];
GO
*/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

forwarded-records-mcve.sql

Self-contained repro for the blog post

"Forwarded Records: the Back-Pointer and the Trip Home"

https://www.sqlserverscience.com/internals/forwarded-records-the-back-pointer-and-the-trip-home/

Demonstrates, on any SQL Server 2019+ instance:

STEP 1 - forwarding: a growing heap row moves, leaving a 9-byte stub

STEP 2 - the back-pointer: the forwarded record's last 10 bytes hold a

marker (1024) plus the RID of the stub (shown via DBCC PAGE)

STEP 3 - move-back: shrinking the row returns it to its original slot

STEP 4 - no lazy return: a forwarded row does not come home when space

frees up. The check runs only when an UPDATE touches the

forwarded row - and ANY update counts, including a no-op

SET v = v.

STEP 5 - the fit rule, found empirically: a binary search locates the

largest record that can return to a fragmented home page. The

boundary exceeds the page's contiguous free space: the engine

performs a limited on-page reorganization, and the exact

budget depends on the layout of free-space holes. SQL Server

2019 and 2025 produce identical boundaries.

Requirements:

- SQL Server 2019 or later (sys.dm_db_page_info)

- sysadmin (DBCC PAGE, DBCC TRACEON)

- Creates and drops a scratch database named fwd_mcve

Everything is computed dynamically: no hard-coded page numbers. Run the

whole script in one go, or step through batch by batch and inspect.

USE [master];

IF DB_ID(N'fwd_mcve') IS NOT NULL

BEGIN

ALTER DATABASE [fwd_mcve] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [fwd_mcve];

END;

CREATE DATABASE [fwd_mcve];

USE [fwd_mcve];

SET NOCOUNT ON;

CREATE TABLE [dbo].[fwd_demo]

(

[id] int NOT NULL

, [v] varchar(8000) NOT NULL

);

/* ten rows of 750 characters fill most of one 8 KB page */

DECLARE @i int = 1;

WHILE @i <= 10

BEGIN

INSERT INTO [dbo].[fwd_demo] ( [id], [v] ) VALUES ( @i, REPLICATE('a', 750) );

SET @i += 1;

END;

PRINT '===== STEP 1: force forwarding by growing row 5 =====';

/* location of every row before the update */

SELECT

[phase] = 'before'

, [id] = [fd].[id]

, [row_chars] = LEN([fd].[v])

, [physloc] = CONVERT(varchar(11), [p].[file_id]) + ':'

+ CONVERT(varchar(11), [p].[page_id]) + ':'

+ CONVERT(varchar(11), [p].[slot_id])

FROM

[dbo].[fwd_demo] AS [fd]

CROSS APPLY

sys.fn_PhysLocCracker([fd].%%physloc%%) AS [p]

ORDER BY

[fd].[id];

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('b', 3000) WHERE [id] = 5;

/* %%physloc%% for row 5 is UNCHANGED - it reports the stub's RID, not the

physical location of the forwarded record */

SELECT

[phase] = 'after'

, [id] = [fd].[id]

, [row_chars] = LEN([fd].[v])

, [physloc] = CONVERT(varchar(11), [p].[file_id]) + ':'

+ CONVERT(varchar(11), [p].[page_id]) + ':'

+ CONVERT(varchar(11), [p].[slot_id])

FROM

[dbo].[fwd_demo] AS [fd]

CROSS APPLY

sys.fn_PhysLocCracker([fd].%%physloc%%) AS [p]

WHERE

[fd].[id] = 5;

SELECT

[forwarded_records] = [ips].[forwarded_record_count]

, [record_count] = [ips].[record_count]

, [page_count] = [ips].[page_count]

FROM

sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];

PRINT '===== STEP 2: dump the stub and the forwarded record =====';

PRINT 'In the FORWARDED_RECORD dump below, the record''s last 10 bytes decode';

PRINT 'little-endian as: 2-byte marker 0x0400 (1024), 4-byte page id,';

PRINT '2-byte file id, 2-byte slot id = the RID of the stub.';

PRINT 'DBCC PAGE also prints it decoded as "Forwarded from = ...".';

DBCC TRACEON(3604);

DECLARE

@page_id int

, @sql nvarchar(200);

DECLARE [page_cursor] CURSOR LOCAL FAST_FORWARD FOR

SELECT

[pa].[allocated_page_page_id]

FROM

sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [pa]

WHERE

[pa].[is_allocated] = 1

AND [pa].[page_type] = 1 /* data pages only */

ORDER BY

[pa].[allocated_page_page_id];

OPEN [page_cursor];

FETCH NEXT FROM [page_cursor] INTO @page_id;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT '----- DBCC PAGE (fwd_mcve, 1, ' + CONVERT(varchar(11), @page_id) + ', 3) -----';

SET @sql = N'DBCC PAGE (N''fwd_mcve'', 1, ' + CONVERT(nvarchar(11), @page_id) + N', 3);';

EXEC sys.sp_executesql @sql;

FETCH NEXT FROM [page_cursor] INTO @page_id;

END;

CLOSE [page_cursor];

DEALLOCATE [page_cursor];

PRINT '===== STEP 3: shrink row 5 - it moves back to its original slot =====';

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('c', 750) WHERE [id] = 5;

SELECT

[id] = [fd].[id]

, [row_chars] = LEN([fd].[v])

, [physloc] = CONVERT(varchar(11), [p].[file_id]) + ':'

+ CONVERT(varchar(11), [p].[page_id]) + ':'

+ CONVERT(varchar(11), [p].[slot_id])

FROM

[dbo].[fwd_demo] AS [fd]

CROSS APPLY

sys.fn_PhysLocCracker([fd].%%physloc%%) AS [p]

WHERE

[fd].[id] = 5;

SELECT

[forwarded_records] = [ips].[forwarded_record_count] /* expect 0 */

, [record_count] = [ips].[record_count] /* stub gone */

FROM

sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];

PRINT '===== STEP 4: no lazy return; any UPDATE triggers the check =====';

PRINT 'Re-forward row 5, consume the home page''s space, shrink row 5 (stays';

PRINT 'forwarded), then free the space again. The row does NOT return on its';

PRINT 'own. It returns on the next UPDATE that touches it - even a no-op.';

/* re-forward row 5 */

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('d', 3000) WHERE [id] = 5;

/* grow neighbours IN PLACE to use up the home page's free space - 1400 chars

each is enough to block row 5's return without forwarding rows 1,2 too */

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('e', 1400) WHERE [id] = 1;

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('e', 1400) WHERE [id] = 2;

/* shrink row 5: no room at home, so it stays forwarded */

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('f', 750) WHERE [id] = 5;

SELECT

[checkpoint_label] = 'home page full: row 5 stays forwarded (expect 1)'

, [forwarded_records] = [ips].[forwarded_record_count]

FROM

sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];

/* free the home page's space again */

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('g', 750) WHERE [id] = 1;

UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('g', 750) WHERE [id] = 2;

SELECT

[checkpoint_label] = 'space freed, row 5 untouched: still forwarded'

, [forwarded_records] = [ips].[forwarded_record_count]

FROM

sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];

/* even a no-op update runs the move-back check - the row goes home */

UPDATE [dbo].[fwd_demo] SET [v] = [v] WHERE [id] = 5;

SELECT

[checkpoint_label] = 'after no-op update: row RETURNS (any update triggers the check)'

, [forwarded_records] = [ips].[forwarded_record_count]

FROM

sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[fwd_demo]'), 0, NULL, N'DETAILED') AS [ips];

PRINT '===== STEP 5: find the fit boundary empirically =====';

PRINT 'A fresh fragmented state is built for every probe, and a binary search';

PRINT 'finds the largest record that can return home. The boundary exceeds';

PRINT 'contiguous + 9: the engine reorganizes the page to a limited extent.';

PRINT 'For THIS construction (two 500-byte holes) the boundary lands at';

PRINT 'contiguous + 500 + 9 on both SQL Server 2019 and 2025.';

/* rebuild a known fragmented state from scratch for a single probe:

10 x 750 rows, row 5 forwarded, rows 3,4 grown 1400 / shrunk 900 =

two ~500-byte holes plus the contiguous region */

CREATE PROCEDURE [dbo].[probe_return]

@attempt_chars int

, @returned bit OUTPUT

, @free_bytes int OUTPUT

, @contiguous int OUTPUT

BEGIN

SET NOCOUNT ON;

DECLARE

@home_page int

, @free_col sysname

, @free_data int

, @slots int

, @fwd1 int

, @fwd2 int

, @i int = 1

, @sql nvarchar(600);

IF OBJECT_ID(N'[dbo].[probe]', N'U') IS NOT NULL

BEGIN

DROP TABLE [dbo].[probe];

END;

CREATE TABLE [dbo].[probe]

(

[id] int NOT NULL

, [v] varchar(8000) NOT NULL

);

WHILE @i <= 10

BEGIN

INSERT INTO [dbo].[probe] ( [id], [v] ) VALUES ( @i, REPLICATE('a', 750) );

SET @i += 1;

END;

UPDATE [dbo].[probe] SET [v] = REPLICATE('b', 3000) WHERE [id] = 5;

UPDATE [dbo].[probe] SET [v] = REPLICATE('c', 1400) WHERE [id] = 3;

UPDATE [dbo].[probe] SET [v] = REPLICATE('c', 1400) WHERE [id] = 4;

UPDATE [dbo].[probe] SET [v] = REPLICATE('d', 900) WHERE [id] = 3;

UPDATE [dbo].[probe] SET [v] = REPLICATE('d', 900) WHERE [id] = 4;

SELECT

@home_page = [p].[page_id]

FROM

[dbo].[probe] AS [fd]

CROSS APPLY

sys.fn_PhysLocCracker([fd].%%physloc%%) AS [p]

WHERE

[fd].[id] = 5;

/* sys.dm_db_page_info names this column free_data_offset in the ver17

documentation, but the builds tested (2019 CU32, 2025 RTM) both use

free_bytes_offset - detect at runtime */

SELECT

@free_col = [rs].[name]

FROM

sys.dm_exec_describe_first_result_set(

N'SELECT * FROM sys.dm_db_page_info(1, 1, 1, N''LIMITED'');', NULL, 0) AS [rs]

WHERE

[rs].[name] IN (N'free_data_offset', N'free_bytes_offset');

SET @sql = N'

SELECT

@free_data = [pi].' + QUOTENAME(@free_col) + N'

, @slots = [pi].[slot_count]

, @free_bytes = [pi].[free_bytes]

FROM

sys.dm_db_page_info(DB_ID(), 1, @home_page, N''DETAILED'') AS [pi];';

EXEC sys.sp_executesql

@sql

, N'@home_page int, @free_data int OUTPUT, @slots int OUTPUT, @free_bytes int OUTPUT'

, @home_page = @home_page

, @free_data = @free_data OUTPUT

, @slots = @slots OUTPUT

, @free_bytes = @free_bytes OUTPUT;

SET @contiguous = 8192 - @free_data - (2 * @slots);

SELECT

@fwd1 = [ips].[forwarded_record_count]

FROM

sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[probe]'), 0, NULL, N'DETAILED') AS [ips];

SET @sql = N'UPDATE [dbo].[probe] SET [v] = REPLICATE(''z'', ' + CONVERT(nvarchar(11), @attempt_chars) + N') WHERE [id] = 5;';

EXEC sys.sp_executesql @sql;

SELECT

@fwd2 = [ips].[forwarded_record_count]

FROM

sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'[dbo].[probe]'), 0, NULL, N'DETAILED') AS [ips];

SET @returned = CASE WHEN @fwd2 < @fwd1 THEN 1 ELSE 0 END;

END;

/* binary search: largest record size (chars) that returns home */

DECLARE

@lo int = 200 /* known to return */

, @hi int = 2400 /* known to stay stuck */

, @mid int

, @returned bit

, @free_bytes int

, @contiguous int;

WHILE @hi - @lo > 1

BEGIN

SET @mid = (@lo + @hi) / 2;

EXEC [dbo].[probe_return]

@attempt_chars = @mid

, @returned = @returned OUTPUT

, @free_bytes = @free_bytes OUTPUT

, @contiguous = @contiguous OUTPUT;

IF @returned = 1

BEGIN

SET @lo = @mid;

END

ELSE

BEGIN

SET @hi = @mid;

END;

/* this construction leaves two ~500-byte holes; the record overhead for the

probe table is 15 bytes, so boundaries convert as record = chars + 15 */

SELECT

[found_boundary_chars] = @lo

, [found_boundary_bytes] = @lo + 15

, [free_bytes] = @free_bytes

, [contiguous_free] = @contiguous

, [ref_no_reorg_bytes] = @contiguous + 9 /* if the engine did not reorganize */

, [ref_this_layout_bytes] = @contiguous + 500 + 9 /* observed: one 500-byte hole reclaimed */

, [note] = 'boundary > ref_no_reorg proves on-page reorganization; identical on 2019 and 2025';

DBCC TRACEOFF(3604);

/* cleanup - uncomment to drop the scratch database

USE [master];

ALTER DATABASE [fwd_mcve] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [fwd_mcve];

Expected output

Page numbers will differ on your instance; everything else should match. This is the output from SQL Server 2025 (RTM) on Windows; SQL Server 2019 (CU32) and SQL Server 2025 on Linux (via db<>fiddle) produce the same numbers. You can also run the whole script on dbfiddle.uk.

===== STEP 1: force forwarding by growing row 5 =====
  before: row 5 at 1:352:4          after: row 5 still at 1:352:4 (the stub RID)
  forwarded_records = 1, record_count = 11, page_count = 2

===== STEP 2: dump the stub and the forwarded record =====
  Slot 4: FORWARDING_STUB, 9 bytes, "Forwarding to = file 1 page 353 slot 1"
  Slot 1 (page 353): FORWARDED_RECORD, 3,027 bytes,
    last 10 bytes 0004 6001 0000 0100 0400 = marker 1024 + RID 1:352:4
    DBCC PAGE prints: "Forwarded from = file 1 page 352 slot 4"

===== STEP 3: shrink row 5 - it moves back to its original slot =====
  row 5 at 1:352:4, forwarded_records = 0, record_count = 10

===== STEP 4: no lazy return; any UPDATE triggers the check =====
  home page full: row 5 stays forwarded (expect 1)                  1
  space freed, row 5 untouched: still forwarded                     1
  after no-op update: row RETURNS (any update triggers the check)   0

===== STEP 5: find the fit boundary empirically =====
  found_boundary_chars  found_boundary_bytes  free_bytes  contiguous_free  ref_no_reorg_bytes  ref_this_layout_bytes
  1143                  1158                  1649        649              658                 1158
  boundary > ref_no_reorg proves on-page reorganization; identical on 2019 and 2025

===== STEP 1: force forwarding by growing row 5 =====

before: row 5 at 1:352:4 after: row 5 still at 1:352:4 (the stub RID)

forwarded_records = 1, record_count = 11, page_count = 2

===== STEP 2: dump the stub and the forwarded record =====

Slot 4: FORWARDING_STUB, 9 bytes, "Forwarding to = file 1 page 353 slot 1"

Slot 1 (page 353): FORWARDED_RECORD, 3,027 bytes,

last 10 bytes 0004 6001 0000 0100 0400 = marker 1024 + RID 1:352:4

DBCC PAGE prints: "Forwarded from = file 1 page 352 slot 4"

===== STEP 3: shrink row 5 - it moves back to its original slot =====

row 5 at 1:352:4, forwarded_records = 0, record_count = 10

===== STEP 4: no lazy return; any UPDATE triggers the check =====

home page full: row 5 stays forwarded (expect 1) 1

space freed, row 5 untouched: still forwarded 1

after no-op update: row RETURNS (any update triggers the check) 0

===== STEP 5: find the fit boundary empirically =====

found_boundary_chars found_boundary_bytes free_bytes contiguous_free ref_no_reorg_bytes ref_this_layout_bytes

1143 1158 1649 649 658 1158

boundary > ref_no_reorg proves on-page reorganization; identical on 2019 and 2025

References

Forwarding and forwarded records, and the back-pointer size – Paul Randal, SQLskills, 2009. Establishes the 10-byte back-pointer (a 2-byte marker plus the 8-byte RID) and the size-delta technique for measuring it. The 2016 comment thread is where the move-back is described as devilishly difficult to demonstrate. ↩
Heaps (Tables without clustered indexes) – Microsoft Learn. The 8-byte RID (file, page, slot), how an update that needs more space creates a forwarded record and forwarding pointer, and why the RID stays stable through the move. ↩
Maximum capacity specifications for SQL Server – Microsoft Learn. 1,024 columns per table, and 30,000 for a table using a column set over sparse columns, which is what puts the value 1024 outside the ordinary in-row column range. ↩
Pages and extents architecture guide – Microsoft Learn. Page and slot-array layout, and the PFS free-space buckets (empty, 50%, 80%, 95%, 100%) that decide which page a heap insert lands on. ↩

Tags: DBCC PAGE, forwarded records, fragmentation, heap, SQL Server, Storage Internals, t-sql

Forwarded Records: the Back-Pointer and the Trip Home

Setting the stage

The back-pointer, in bytes

What the back-pointer is actually for

Catching the row moving home

Condition 1: the row returns only when an UPDATE touches it

Condition 2: the row must fit – and “fit” is layout-dependent

Condition 3: the home page still has to have that room

Putting it together

The MCVE: run it yourself

Expected output

Related posts

References

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta

Forwarded Records: the Back-Pointer and the Trip Home

Setting the stage

The back-pointer, in bytes

What the back-pointer is actually for

Catching the row moving home

Condition 1: the row returns only when an UPDATE touches it

Condition 2: the row must fit – and “fit” is layout-dependent

Condition 3: the home page still has to have that room

Putting it together

The MCVE: run it yourself

Expected output

Related posts

References

Share this:

Related

Let us know your thoughts on this postCancel reply

Search

Categories

Pages

Meta