or
Max
sql-server
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 in:

    SELECT *
    FROM dbo.t
    CROSS APPLY sys.fn_PhysLocCracker(%%PHYSLOC%%)

I only see the `DATA_PAGE` listed:

```
╔════════╦═════════════╦═════════════════════╦═════════╦═════════╦═════════╗
║ rownum ║ on_row_data ║     off_row_data    ║ file_id ║ page_id ║ slot_id ║
╠════════╬═════════════╬═════════════════════╬═════════╬═════════╬═════════╣
║      1 ║ on_row_data ║ AAAAAAAAAAAAAAAAAAA ║       3 ║  105368 ║       0 ║
╚════════╩═════════════╩═════════════════════╩═════════╩═════════╩═════════╝
```

Likewise, if I use `DBCC IND(database, table, index)` I only see the two pages listed:

    DBCC IND (tempdb, t, 1);

Output:

```
╔═════════╦═════════╦════════╦════════╦════════════╦═════════╦═════════════════╦═════════════════════╦════════════════╦══════════╦════════════╦═════════════╦═════════════╦═════════════╦═════════════╦══╗
║ PageFID ║ PagePID ║ IAMFID ║ IAMPID ║  ObjectID  ║ IndexID ║ PartitionNumber ║     PartitionID     ║ iam_chain_type ║ PageType ║ IndexLevel ║ NextPageFID ║ NextPagePID ║ PrevPageFID ║ PrevPagePID ║  ║
╠═════════╬═════════╬════════╬════════╬════════════╬═════════╬═════════════════╬═════════════════════╬════════════════╬══════════╬════════════╬═════════════╬═════════════╬═════════════╬═════════════╬══╣
║       1 ║    1598 ║ NULL   ║ NULL   ║ 2069582411 ║       1 ║               1 ║ 6989586877272752128 ║ In-row data    ║       10 ║ NULL       ║           0 ║           0 ║           0 ║           0 ║  ║
║       3 ║  105368 ║ 1      ║ 1598   ║ 2069582411 ║       1 ║               1 ║ 6989586877272752128 ║ In-row data    ║        1 ║ 0          ║           0 ║           0 ║           0 ║           0 ║  ║
╚═════════╩═════════╩════════╩════════╩════════════╩═════════╩═════════════════╩═════════════════════╩════════════════╩══════════╩════════════╩═════════════╩═════════════╩═════════════╩═════════════╩══╝
```


If I look at the actual page contents, using `DBCC PAGE`, it looks like I *still* don't see anything about which page contains the ROW_OVERFLOW_DATA - I'm sure it must be there, I probably just don't know what to look at:

    DBCC PAGE (tempdb, 3, 105368 , 3) WITH TABLERESULTS;

The results are too big to fit here, if I include the memory dump rows, but this is the header output:

```
╔══════════════╦════════════════════════════════╦═══════════════════════════════╦═══════════════════════════════╗
║ ParentObject ║             Object             ║             Field             ║             VALUE             ║
╠══════════════╬════════════════════════════════╬═══════════════════════════════╬═══════════════════════════════╣
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bpage                         ║ 0x000002431A8A2000            ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bhash                         ║ 0x0000000000000000            ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bpageno                       ║ (3:105368)                    ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bdbid                         ║ 2                             ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ breferences                   ║ 0                             ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bcputicks                     ║ 0                             ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bsampleCount                  ║ 0                             ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bUse1                         ║ 63172                         ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bstat                         ║ 0x10b                         ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ blog                          ║ 0x212121cc                    ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bnext                         ║ 0x0000000000000000            ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bDirtyContext                 ║ 0x000002435DA77160            ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bstat2                        ║ 0x0                           ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_pageId                      ║ (3:105368)                    ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_headerVersion               ║ 1                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_type                        ║ 1                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_typeFlagBits                ║ 0x0                           ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_level                       ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_flagBits                    ║ 0xc000                        ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_objId (AllocUnitId.idObj)   ║ 3920762                       ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_indexId (AllocUnitId.idInd) ║ 512                           ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ Metadata: AllocUnitId         ║ 144115445026914304            ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ Metadata: PartitionId         ║ 6989586877272752128           ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ Metadata: IndexId             ║ 1                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ Metadata: ObjectId            ║ 2069582411                    ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_prevPage                    ║ (0:0)                         ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_nextPage                    ║ (0:0)                         ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ pminlen                       ║ 8                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_slotCnt                     ║ 1                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_freeCnt                     ║ 66                            ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_freeData                    ║ 8124                          ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_reservedCnt                 ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_lsn                         ║ (36:47578:1)                  ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_xactReserved                ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_xdesId                      ║ (0:0)                         ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_ghostRecCnt                 ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_tornBits                    ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ DB Frag ID                    ║ 1                             ║
║ PAGE HEADER: ║ Allocation Status              ║ GAM (3:2)                     ║ ALLOCATED                     ║
║ PAGE HEADER: ║ Allocation Status              ║ SGAM (3:3)                    ║ NOT ALLOCATED                 ║
║ PAGE HEADER: ║ Allocation Status              ║ PFS (3:105144)                ║ 0x40 ALLOCATED   0_PCT_FULL   ║
║ PAGE HEADER: ║ Allocation Status              ║ DIFF (3:6)                    ║ NOT CHANGED                   ║
║ PAGE HEADER: ║ Allocation Status              ║ ML (3:7)                      ║ NOT MIN_LOGGED                ║
║ PAGE HEADER: ║ Slot 0 Offset 0x60 Length 8028 ║ Record Type                   ║ PRIMARY_RECORD                ║
║ PAGE HEADER: ║ Slot 0 Offset 0x60 Length 8028 ║ Record Attributes             ║  NULL_BITMAP VARIABLE_COLUMNS ║
║ PAGE HEADER: ║ Slot 0 Offset 0x60 Length 8028 ║ Record Size                   ║ 8028                          ║
╚══════════════╩════════════════════════════════╩═══════════════════════════════╩═══════════════════════════════╝
```
Top Answer
Josh Darnell
Your demo is being hit by a [limitation of REPLICATE][1]:

> 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.

[1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/replicate-transact-sql?view=sql-server-2017
List ROW_OVERFLOW_DATA pages for a specific table

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.