List ROW_OVERFLOW_DATA pages for a specific table

I'm attempting to get a list of pages for a table that has rows with ROW_OVERFLOW_DATA. I can get the list of allocated pages from the undocumented DMV, sys.db_db_database_page_allocations, however, there appears to be no ROW_OVERFLOW_DATA pages listed in the output of that DMV. Is there some other DMV that I simply cannot locate?

Minimal, complete, and (hopefully!) verifiable example:

USE tempdb;

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
GO

CREATE TABLE dbo.t
(
    rownum int NOT NULL IDENTITY(1,1)
        PRIMARY KEY CLUSTERED
    , on_row_data varchar(30) NOT NULL
        DEFAULT ('on_row_data')
    , off_row_data varchar(MAX) NOT NULL
        DEFAULT REPLICATE('A', 20000) --PLENTY BIG ENOUGH!
) WITH (DATA_COMPRESSION = NONE); --not compressing those pages!

INSERT INTO dbo.t DEFAULT VALUES;

DECLARE @ObjectID int = (SELECT o.object_id FROM sys.objects o WHERE o.name = 't');
DECLARE @PageID int;
DECLARE @PageTypeDesc varchar(100);

SELECT FileID = dpa.allocated_page_file_id
    , PageID = dpa.allocated_page_page_id
    , PageTypeDesc = dpa.page_type_desc
FROM sys.dm_db_database_page_allocations(DB_ID(), @ObjectID, NULL, NULL, 'DETAILED') dpa

Output looks like:

╔════════╦════════╦══════════════╗
║ FileID ║ PageID ║ PageTypeDesc ║
╠════════╬════════╬══════════════╣
║ 1 ║ 1598 ║ IAM_PAGE ║
║ 3 ║ 105368 ║ DATA_PAGE ║
║ 3 ║ 105369 ║ NULL ║
║ 3 ║ 105370 ║ NULL ║
║ 3 ║ 105371 ║ NULL ║
║ 3 ║ 105372 ║ NULL ║
║ 3 ║ 105373 ║ NULL ║
║ 3 ║ 105374 ║ NULL ║
║ 3 ║ 105375 ║ NULL ║
╚════════╩════════╩══════════════╝

Which makes sense, other than the missing ROW_OVERFLOW_DATA page. We have a single index allocation map page, and a full extents-worth of 8KB data pages, with only a single one of those pages actually allocated.

Similarly, if I use the undocumented sys.fn_PhysLocCracker function to show the page where each row exists, as

Your demo is being hit by a limitation of REPLICATE:


If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

If I do this:

INSERT INTO dbo.t (off_row_data) VALUES (REPLICATE(CAST('A' as varchar(max)), 20000));

And then run your DMV query from above against dm_db_database_page_allocations, I get pages with a PageTypeDesc of TEXT_MIX_PAGE.

I can then run DBCC PAGE with trace flag 3604 enabled in order to see the details of that off-row page:

DBCC TRACEON (3604);
GO
DBCC PAGE (TestDB, 1, 20696 , 3) -- your page will be different :)

The output is large, but near the beginning you'll see:

Blob row at: Page (1:20696) Slot 0 Length: 3934 Type: 3 (DATA)

And then, you know, a bunch of A's.

StackExchange Database Administrators Q#217660, answer score: 10