View difference between Paste ID: BXj8WnAi and bsrPj2u7

 /****************************************************************************************************************
 	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;
 
 -----------------------------------------------------------------------------------