T-SQL script to test various scenarios for LOB reads

/****************************************************************************************************************
    This T-SQL script pertains to the following DBA.StackExchange Q & A:
    http://dba.stackexchange.com/questions/137907/logical-reads-different-when-accessing-the-same-lob-data

    and 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-11
    Last Modified on: 2016-05-11
    URL of this script: http://pastebin.com/edit/pRuzUAbg
 ****************************************************************************************************************/


USE [tempdb];
SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON;
SET STATISTICS XML OFF;
GO

IF (OBJECT_ID(N'tempdb..#XmlReadTest') IS NOT NULL)
BEGIN
    PRINT 'Dropping table: #XmlReadTest...';
    DROP TABLE #XmlReadTest;
END;
CREATE TABLE #XmlReadTest (ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, SourceCode XML);
PRINT '';
GO


PRINT 'Populating table: #XmlReadTest...';
INSERT INTO #XmlReadTest (SourceCode)
    SELECT CONVERT(XML, N'<definition name="'
                + OBJECT_NAME(mods.[object_id], DB_ID(N'master'))
                + N'">'
                + REPLACE(REPLACE(REPLACE(mods.[definition], N'&', N'&amp;'), N'"', N'&quot;'), N'<', N'&lt;')
                + N'</definition>')
    FROM [master].[sys].[all_sql_modules] mods;
CHECKPOINT;
PRINT '';
GO


PRINT '--------------- ALL Rows: DATALENGTH does not use LOB reads ---------------';
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME, IO ON;
SELECT DATALENGTH([SourceCode])
FROM   #XmlReadTest;
SET STATISTICS TIME, IO OFF;
PRINT '------------------------------------------------------------------------------------------';
GO
-- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
--               lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- CPU time = 0 ms,  elapsed time = 120 ms.


PRINT '--------------- ALL Rows: Result Set to Client ---------------';
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME, IO ON;
SELECT tmp.[SourceCode] FROM #XmlReadTest tmp;
SET STATISTICS TIME, IO OFF;
PRINT '------------------------------------------------------------------------------------------';
GO
-- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
--               lob logical reads 5354, lob physical reads 73, lob read-ahead reads 3746.
-- CPU time = 63 ms,  elapsed time = 605 ms.


PRINT '--------------- ALL Rows: SELECT INTO ---------------';
IF (OBJECT_ID(N'tempdb..#SelectIntoTest') IS NOT NULL)
BEGIN
    PRINT 'Dropping table: #SelectIntoTest...';
    DROP TABLE #SelectIntoTest;
    CHECKPOINT;
END;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME, IO ON;
SELECT * INTO #SelectIntoTest FROM #XmlReadTest tmp;
SET STATISTICS TIME, IO OFF;
PRINT '------------------------------------------------------------------------------------------';
GO
-- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
--               lob logical reads 1981, lob physical reads 73, lob read-ahead reads 1660.
-- CPU time = 78 ms,  elapsed time = 1357 ms.


PRINT '--------------- ALL Rows: Convert to NVARCHAR ---------------';
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DECLARE @Xml NVARCHAR(MAX);
SET STATISTICS TIME, IO ON;
SELECT @Xml = CONVERT(NVARCHAR(MAX), tmp.[SourceCode]) FROM #XmlReadTest tmp;
SET STATISTICS TIME, IO OFF;
PRINT '------------------------------------------------------------------------------------------';
GO
-- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
--               lob logical reads 3948, lob physical reads 73, lob read-ahead reads 1660.
-- CPU time = 110 ms,  elapsed time = 409 ms.


PRINT '--------------- ALL Rows: XQuery to Grab Entire Document ---------------';
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DECLARE @Xml XML;
SET STATISTICS TIME, IO ON;
SELECT @Xml = tmp.[SourceCode].query(N'/') FROM #XmlReadTest tmp;
SET STATISTICS TIME, IO OFF;
PRINT '------------------------------------------------------------------------------------------';
GO
-- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
--               lob logical reads 4433, lob physical reads 171, lob read-ahead reads 0.
-- CPU time = 109 ms,  elapsed time = 377 ms.


PRINT '===============================================================================================';
------------------------------------------------
------------------------------------------------
-- Test just the Inline Root records
------------------------------------------------

PRINT '--------------- Inline Root: Result Set to Client ---------------';
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME, IO ON;
SELECT tmp.[SourceCode] FROM #XmlReadTest tmp WHERE DATALENGTH(tmp.[SourceCode]) BETWEEN 8001 AND 42000;
SET STATISTICS TIME, IO OFF;
PRINT '------------------------------------------------------------------------------------------';
GO
-- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
--               lob logical reads 2714, lob physical reads 33, lob read-ahead reads 2493.
-- CPU time = 31 ms,  elapsed time = 464 ms.


PRINT '--------------- Inline Root: Convert to NVARCHAR ---------------';
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DECLARE @Xml NVARCHAR(MAX);
SET STATISTICS TIME, IO ON;
SELECT @Xml = CONVERT(NVARCHAR(MAX), tmp.[SourceCode]) FROM #XmlReadTest tmp WHERE DATALENGTH(tmp.[SourceCode]) BETWEEN 8001 AND 42000;
SET STATISTICS TIME, IO OFF;
PRINT '------------------------------------------------------------------------------------------';
GO
-- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
--               lob logical reads 2120, lob physical reads 33, lob read-ahead reads 1244.
-- CPU time = 78 ms,  elapsed time = 393 ms.


PRINT '--------------- Inline Root: XQuery to Grab Entire Document ---------------';
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DECLARE @Xml XML;
SET STATISTICS TIME, IO ON;
SELECT @Xml = tmp.[SourceCode].query(N'/') FROM #XmlReadTest tmp WHERE DATALENGTH(tmp.[SourceCode]) BETWEEN 8001 AND 42000;
SET STATISTICS TIME, IO OFF;
PRINT '------------------------------------------------------------------------------------------';
GO
-- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
--               lob logical reads 2521, lob physical reads 131, lob read-ahead reads 0.
-- CPU time = 62 ms,  elapsed time = 391 ms.


/*

CHECKPOINT;
DBCC FREESYSTEMCACHE('ALL') WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT * FROM sys.dm_os_buffer_descriptors dobd
WHERE dobd.database_id = DB_ID(N'tempdb');

SELECT * FROM sys.dm_os_buffer_descriptors dobd
INNER JOIN sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlReadTest'), NULL, NULL, N'DETAILED') dddpa
    ON dddpa.allocated_page_file_id = dobd.[file_id]
    AND dddpa.allocated_page_page_id = dobd.page_id
WHERE dobd.database_id = DB_ID(N'tempdb');

SELECT * FROM sys.dm_os_buffer_descriptors dobd
INNER JOIN sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#SelectIntoTest'), NULL, NULL, N'DETAILED') dddpa
    ON dddpa.allocated_page_file_id = dobd.[file_id]
    AND dddpa.allocated_page_page_id = dobd.page_id
WHERE dobd.database_id = DB_ID(N'tempdb');
*/