SHARE
TWEET

T-SQL script to test various scenarios for LOB reads

SqlQuantumLeap May 11th, 2016 (edited) 23 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /****************************************************************************************************************
  2.     This T-SQL script pertains to the following DBA.StackExchange Q & A:
  3.     http://dba.stackexchange.com/questions/137907/logical-reads-different-when-accessing-the-same-lob-data
  4.  
  5.     and the following DBA.StackExchange answer:
  6.     http://dba.stackexchange.com/questions/136674/lob-data-slow-table-scans-and-some-i-o-questions/137869#137869
  7.  
  8.     By: Solomon Rutzky
  9.     Created on: 2016-05-11
  10.     Last Modified on: 2016-05-11
  11.     URL of this script: http://pastebin.com/edit/pRuzUAbg
  12.  ****************************************************************************************************************/
  13.  
  14.  
  15. USE [tempdb];
  16. SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON;
  17. SET STATISTICS XML OFF;
  18. GO
  19.  
  20. IF (OBJECT_ID(N'tempdb..#XmlReadTest') IS NOT NULL)
  21. BEGIN
  22.     PRINT 'Dropping table: #XmlReadTest...';
  23.     DROP TABLE #XmlReadTest;
  24. END;
  25. CREATE TABLE #XmlReadTest (ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, SourceCode XML);
  26. PRINT '';
  27. GO
  28.  
  29.  
  30. PRINT 'Populating table: #XmlReadTest...';
  31. INSERT INTO #XmlReadTest (SourceCode)
  32.     SELECT CONVERT(XML, N'<definition name="'
  33.                 + OBJECT_NAME(mods.[object_id], DB_ID(N'master'))
  34.                 + N'">'
  35.                 + REPLACE(REPLACE(REPLACE(mods.[definition], N'&', N'&amp;'), N'"', N'&quot;'), N'<', N'&lt;')
  36.                 + N'</definition>')
  37.     FROM [master].[sys].[all_sql_modules] mods;
  38. CHECKPOINT;
  39. PRINT '';
  40. GO
  41.  
  42.  
  43. PRINT '--------------- ALL Rows: DATALENGTH does not use LOB reads ---------------';
  44. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  45. SET STATISTICS TIME, IO ON;
  46. SELECT DATALENGTH([SourceCode])
  47. FROM   #XmlReadTest;
  48. SET STATISTICS TIME, IO OFF;
  49. PRINT '------------------------------------------------------------------------------------------';
  50. GO
  51. -- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
  52. --               lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  53. -- CPU time = 0 ms,  elapsed time = 120 ms.
  54.  
  55.  
  56. PRINT '--------------- ALL Rows: Result Set to Client ---------------';
  57. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  58. SET STATISTICS TIME, IO ON;
  59. SELECT tmp.[SourceCode] FROM #XmlReadTest tmp;
  60. SET STATISTICS TIME, IO OFF;
  61. PRINT '------------------------------------------------------------------------------------------';
  62. GO
  63. -- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
  64. --               lob logical reads 5354, lob physical reads 73, lob read-ahead reads 3746.
  65. -- CPU time = 63 ms,  elapsed time = 605 ms.
  66.  
  67.  
  68. PRINT '--------------- ALL Rows: SELECT INTO ---------------';
  69. IF (OBJECT_ID(N'tempdb..#SelectIntoTest') IS NOT NULL)
  70. BEGIN
  71.     PRINT 'Dropping table: #SelectIntoTest...';
  72.     DROP TABLE #SelectIntoTest;
  73.     CHECKPOINT;
  74. END;
  75.  
  76. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  77. SET STATISTICS TIME, IO ON;
  78. SELECT * INTO #SelectIntoTest FROM #XmlReadTest tmp;
  79. SET STATISTICS TIME, IO OFF;
  80. PRINT '------------------------------------------------------------------------------------------';
  81. GO
  82. -- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
  83. --               lob logical reads 1981, lob physical reads 73, lob read-ahead reads 1660.
  84. -- CPU time = 78 ms,  elapsed time = 1357 ms.
  85.  
  86.  
  87. PRINT '--------------- ALL Rows: Convert to NVARCHAR ---------------';
  88. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  89. DECLARE @Xml NVARCHAR(MAX);
  90. SET STATISTICS TIME, IO ON;
  91. SELECT @Xml = CONVERT(NVARCHAR(MAX), tmp.[SourceCode]) FROM #XmlReadTest tmp;
  92. SET STATISTICS TIME, IO OFF;
  93. PRINT '------------------------------------------------------------------------------------------';
  94. GO
  95. -- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
  96. --               lob logical reads 3948, lob physical reads 73, lob read-ahead reads 1660.
  97. -- CPU time = 110 ms,  elapsed time = 409 ms.
  98.  
  99.  
  100. PRINT '--------------- ALL Rows: XQuery to Grab Entire Document ---------------';
  101. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  102. DECLARE @Xml XML;
  103. SET STATISTICS TIME, IO ON;
  104. SELECT @Xml = tmp.[SourceCode].query(N'/') FROM #XmlReadTest tmp;
  105. SET STATISTICS TIME, IO OFF;
  106. PRINT '------------------------------------------------------------------------------------------';
  107. GO
  108. -- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
  109. --               lob logical reads 4433, lob physical reads 171, lob read-ahead reads 0.
  110. -- CPU time = 109 ms,  elapsed time = 377 ms.
  111.  
  112.  
  113. PRINT '===============================================================================================';
  114. ------------------------------------------------
  115. ------------------------------------------------
  116. -- Test just the Inline Root records
  117. ------------------------------------------------
  118.  
  119. PRINT '--------------- Inline Root: Result Set to Client ---------------';
  120. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  121. SET STATISTICS TIME, IO ON;
  122. SELECT tmp.[SourceCode] FROM #XmlReadTest tmp WHERE DATALENGTH(tmp.[SourceCode]) BETWEEN 8001 AND 42000;
  123. SET STATISTICS TIME, IO OFF;
  124. PRINT '------------------------------------------------------------------------------------------';
  125. GO
  126. -- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
  127. --               lob logical reads 2714, lob physical reads 33, lob read-ahead reads 2493.
  128. -- CPU time = 31 ms,  elapsed time = 464 ms.
  129.  
  130.  
  131. PRINT '--------------- Inline Root: Convert to NVARCHAR ---------------';
  132. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  133. DECLARE @Xml NVARCHAR(MAX);
  134. SET STATISTICS TIME, IO ON;
  135. SELECT @Xml = CONVERT(NVARCHAR(MAX), tmp.[SourceCode]) FROM #XmlReadTest tmp WHERE DATALENGTH(tmp.[SourceCode]) BETWEEN 8001 AND 42000;
  136. SET STATISTICS TIME, IO OFF;
  137. PRINT '------------------------------------------------------------------------------------------';
  138. GO
  139. -- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
  140. --               lob logical reads 2120, lob physical reads 33, lob read-ahead reads 1244.
  141. -- CPU time = 78 ms,  elapsed time = 393 ms.
  142.  
  143.  
  144. PRINT '--------------- Inline Root: XQuery to Grab Entire Document ---------------';
  145. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  146. DECLARE @Xml XML;
  147. SET STATISTICS TIME, IO ON;
  148. SELECT @Xml = tmp.[SourceCode].query(N'/') FROM #XmlReadTest tmp WHERE DATALENGTH(tmp.[SourceCode]) BETWEEN 8001 AND 42000;
  149. SET STATISTICS TIME, IO OFF;
  150. PRINT '------------------------------------------------------------------------------------------';
  151. GO
  152. -- Scan count 1, logical reads 611, physical reads 0, read-ahead reads 610,
  153. --               lob logical reads 2521, lob physical reads 131, lob read-ahead reads 0.
  154. -- CPU time = 62 ms,  elapsed time = 391 ms.
  155.  
  156.  
  157. /*
  158.  
  159. CHECKPOINT;
  160. DBCC FREESYSTEMCACHE('ALL') WITH NO_INFOMSGS;
  161. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
  162.  
  163. SELECT * FROM sys.dm_os_buffer_descriptors dobd
  164. WHERE dobd.database_id = DB_ID(N'tempdb');
  165.  
  166. SELECT * FROM sys.dm_os_buffer_descriptors dobd
  167. INNER JOIN sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlReadTest'), NULL, NULL, N'DETAILED') dddpa
  168.     ON dddpa.allocated_page_file_id = dobd.[file_id]
  169.     AND dddpa.allocated_page_page_id = dobd.page_id
  170. WHERE dobd.database_id = DB_ID(N'tempdb');
  171.  
  172. SELECT * FROM sys.dm_os_buffer_descriptors dobd
  173. INNER JOIN sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#SelectIntoTest'), NULL, NULL, N'DETAILED') dddpa
  174.     ON dddpa.allocated_page_file_id = dobd.[file_id]
  175.     AND dddpa.allocated_page_page_id = dobd.page_id
  176. WHERE dobd.database_id = DB_ID(N'tempdb');
  177. */
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top