T-SQL script to test LOB allocations and reads

/****************************************************************************************************************
    This SQL pertains to the following DBA.StackExchange answer:
    http://dba.stackexchange.com/questions/136674/lob-data-slow-table-scans-and-some-i-o-questions/137869#137869

    By: Solomon Rutzky
    Created on: 2016-05-09
    Last Modified on: 2016-05-11
    URL of this script: http://pastebin.com/bsrPj2u7
 ****************************************************************************************************************/


    -----------------------------------------------------------------------------
    -----------------------------------------------------------------------------
    ----
    ----  Do not run this script all at once!
    ----  Run the block-comment sections as each one indicates.
    ----  Highlight and execute each numbered section independently, and in order.
    ----
    -----------------------------------------------------------------------------
    -----------------------------------------------------------------------------


/* -- Highlight and execute the following statements once:
USE [tempdb];
SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON;
GO
-- DROP TABLE #XmlTest;
CREATE TABLE #XmlTest (Col1 INT PRIMARY KEY CLUSTERED, Col2 BIGINT, Col3 DATETIME, Col4 UNIQUEIDENTIFIER, Col5 XML);
*/


-- 1A) IN_ROW LOB data
INSERT INTO #XmlTest (Col1, Col2, Col3, Col4, Col5) VALUES (1, 200000000, GETDATE(), NEWID(),
        N'<test name="in row LOB"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 3960) + N'</val></test>');

SELECT DATALENGTH(tmp.Col5) FROM #XmlTest tmp WHERE tmp.Col1 = 1; -- 7999 bytes
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest'), NULL, NULL, N'DETAILED');
-- 1 page of IN_ROW_DATA : IAM_PAGE
-- 1 page of IN_ROW_DATA : DATA_PAGE


-- 1B) Inline Root LOB data
INSERT INTO #XmlTest (Col1, Col2, Col3, Col4, Col5) VALUES (2, 400000000, GETDATE(), NEWID(),
        N'<test name="inline root LOB"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'b'), 20055) + N'</val></test>');

SELECT DATALENGTH(tmp.Col5) FROM #XmlTest tmp WHERE tmp.Col1 = 2; -- 40200 bytes
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest'), NULL, NULL, N'DETAILED');
-- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
-- 1 pre-existing page of IN_ROW_DATA : DATA_PAGE
-- 1 new page of IN_ROW_DATA : INDEX_PAGE
-- 1 new page of IN_ROW_DATA : DATA_PAGE
-- 1 new page of LOB_DATA : IAM_PAGE
-- 6 new pages of LOB_DATA : TEXT_MIX_PAGE (1 TEXT_MIX_PAGE allocated and in LOB IAM page, but not in DATA_PAGE Inline Root)


-- 1C) TEXT_TREE LOB data
INSERT INTO #XmlTest (Col1, Col2, Col3, Col4, Col5) VALUES (3, 600000000, GETDATE(), NEWID(),
        N'<test name="text_tree LOB"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'c'), 20058) + N'</val></test>');

SELECT DATALENGTH(tmp.Col5) FROM #XmlTest tmp WHERE tmp.Col1 = 3; -- 40202 bytes
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest'), NULL, NULL, N'DETAILED');
-- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
-- 1 pre-existing page of IN_ROW_DATA : DATA_PAGE
-- 1 pre-existing page of IN_ROW_DATA : INDEX_PAGE
-- 1 pre-existing page of IN_ROW_DATA : DATA_PAGE
-- 1 pre-existing page of LOB_DATA : IAM_PAGE
-- 6 pre-existing pages of LOB_DATA : TEXT_MIX_PAGE (1 TEXT_MIX_PAGE allocated and in LOB IAM page, but not in DATA_PAGE Inline Root)
-- 5 new pages of LOB_DATA : TEXT_MIX_PAGE
-- 1 new page of LOB_DATA : TEXT_TREE_PAGE


-- Capture PageIDs so we can check against Buffer Pool without reading them into the Buffer Pool
/* -- Highlight and execute the statements below once:
-- DROP TABLE #Pages;
CREATE TABLE #Pages (FileID INT, PageID INT);
INSERT INTO #Pages (FileID, PageID)
    SELECT dddpa.allocated_page_file_id, dddpa.allocated_page_page_id
    FROM   sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest'), NULL, NULL, N'DETAILED') dddpa;
SELECT * FROM #Pages;
*/

USE [tempdb];


/* -- Before each test, highlight and execute the 3 lines below:
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
SELECT * FROM sys.dm_os_buffer_descriptors dobd
WHERE dobd.database_id = DB_ID(N'tempdb');
*/

-- 1D) Check "all columns" reads for IN_ROW LOB data
SET STATISTICS IO ON;
SELECT * FROM #XmlTest WHERE Col1 = 1;
SET STATISTICS IO OFF;
-- Table '#XmlTest'. Scan count 0, logical reads 2,     physical reads 2,     read-ahead reads 0,
--                                 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

/* -- After each test, highlight and execute the following query:
SELECT * FROM sys.dm_os_buffer_descriptors dobd
INNER JOIN #Pages tmp
    ON tmp.FileID = dobd.[file_id]
    AND tmp.PageID = dobd.page_id
WHERE dobd.database_id = DB_ID(N'tempdb');
*/


-- 1E) Check "all columns" reads for Inline Root LOB data (off row)
SET STATISTICS IO ON;
SELECT * FROM #XmlTest WHERE Col1 = 2;
SET STATISTICS IO OFF;
-- Table '#XmlTest'. Scan count 0, logical reads 2,      physical reads 2,     read-ahead reads 0,
--                                 lob logical reads 14, lob physical reads 0, lob read-ahead reads 16.


-- 1F) Check "all columns" reads for TEXT_TREE LOB data (off row)
SET STATISTICS IO ON;
SELECT * FROM #XmlTest WHERE Col1 = 3;
SET STATISTICS IO OFF;
-- Table '#XmlTest'. Scan count 0, logical reads 2,      physical reads 2,     read-ahead reads 0,
--                                 lob logical reads 30, lob physical reads 1, lob read-ahead reads 15.



/* -- Before each test, highlight and execute the 3 lines below:
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
SELECT * FROM sys.dm_os_buffer_descriptors dobd
WHERE dobd.database_id = DB_ID(N'tempdb');
*/

-- 1G) Check non-XML columns reads for row with Inline Root LOB data (off row)
SET STATISTICS IO ON;
SELECT tmp.Col1, tmp.Col2, tmp.Col3, tmp.Col4 FROM #XmlTest tmp WHERE Col1 = 2;
SET STATISTICS IO OFF;
-- Table '#XmlTest'. Scan count 0, logical reads 2,     physical reads 2,     read-ahead reads 0,
--                                 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


-- 1H) Check non-XML columns reads for TEXT_TREE LOB data (off row)
SET STATISTICS IO ON;
SELECT tmp.Col1, tmp.Col2, tmp.Col3, tmp.Col4 FROM #XmlTest tmp WHERE Col1 = 3;
SET STATISTICS IO OFF;
-- Table '#XmlTest'. Scan count 0, logical reads 2,     physical reads 2,     read-ahead reads 0,
--                                 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
--
--  PART 2: Test effect of not having enough space left in-row for all 5 pointers
--          of the Inline Root. Will the Inline Root be skipped and go directly
--          to using a TEXT_TREE, or will it use as many pointers as will fit?
--
-----------------------------------------------------------------------------------


/* -- Highlight and execute the following statements once:
USE [tempdb];
SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON;
GO
-- DROP TABLE #XmlTest2;
CREATE TABLE #XmlTest2 (Col1 INT PRIMARY KEY CLUSTERED, Col2 INT, Col3 SMALLDATETIME, Col4 CHAR(7980), Col5 XML);
*/


-- 2A) IN_ROW LOB data
INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (1, 20, GETDATE(), REPLICATE('Z', 7980),
        N'<test name="in-row"/>');

SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 1; -- 53 bytes
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
-- 1 page of IN_ROW_DATA : IAM_PAGE
-- 1 page of IN_ROW_DATA : DATA_PAGE
---- 2 pages total


-- 2B) Inline Root LOB data # 1 (small, but cannot fit in-row)
INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (2, 40, GETDATE(), REPLICATE('Y', 7980),
        N'<test name="inline root LOB # 1"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 3) + N'</val></test>');

SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 2; -- 102 bytes
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
-- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
-- 1 pre-existing page of IN_ROW_DATA : DATA_PAGE
-- 1 new page of IN_ROW_DATA : INDEX_PAGE
-- 1 new page of IN_ROW_DATA : DATA_PAGE
-- 1 new page of LOB_DATA : IAM_PAGE
-- 1 new page of LOB_DATA : TEXT_MIX_PAGE (50% free space)
---- 6 pages total


-- 2C) Inline Root LOB data # 2 (should require 2 LOB pages = 36 bytes for in-row pointers)
INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (3, 60, GETDATE(), REPLICATE('X', 7980),
        N'<test name="inline root LOB # 2"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'b'), 4500) + N'</val></test>');

SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 3; -- 9097 bytes
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
-- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
-- 2 pre-existing pages of IN_ROW_DATA : DATA_PAGE
-- 1 pre-existing page page of IN_ROW_DATA : INDEX_PAGE
-- 1 new page of IN_ROW_DATA : DATA_PAGE
-- 1 pre-existing page of LOB_DATA : IAM_PAGE
-- 1 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (50% free space)
-- 1 new page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
---- 8 pages total


-- 2D) Inline Root LOB data # 3 (should require 3 LOB pages = 48 bytes for in-row pointers)
INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (4, 80, GETDATE(), REPLICATE('W', 7980),
        N'<test name="inline root LOB # 3"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'c'), 12011) + N'</val></test>');

SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 4; -- 24119 bytes
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
-- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
-- 3 pre-existing pages of IN_ROW_DATA : DATA_PAGE
-- 1 pre-existing page page of IN_ROW_DATA : INDEX_PAGE
-- 1 new page of IN_ROW_DATA : DATA_PAGE
-- 1 pre-existing page of LOB_DATA : IAM_PAGE
-- 1 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (50% free space)
-- 1 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
-- 3 new page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
---- 12 pages total


-- 2E) TEXT_TREE LOB data (should require 4 LOB pages = 60 bytes for in-row pointers, BUT not enough space left for 60 bytes)
INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (5, 100, GETDATE(), REPLICATE('V', 7980),
        N'<test name="text_tree LOB"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'd'), 12018) + N'</val></test>');

SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 5; -- 24121 bytes
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
-- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
-- 4 pre-existing pages of IN_ROW_DATA : DATA_PAGE
-- 1 pre-existing page page of IN_ROW_DATA : INDEX_PAGE
-- 1 new page of IN_ROW_DATA : DATA_PAGE
-- 1 pre-existing page of LOB_DATA : IAM_PAGE
-- 1 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (50% free space)
-- 4 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
-- 3 new page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
-- 1 new page of LOB_DATA : TEXT_TREE_PAGE
-- 7 new unallocated pages
---- 24 pages total



-- DBCC PAGE(2, 1, 330, 3) WITH TABLERESULTS;

-----------------------------------------------------------------------------------