SHARE
TWEET

T-SQL script to test LOB allocations and reads

SqlQuantumLeap May 9th, 2016 (edited) 188 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /****************************************************************************************************************
  2.     This SQL pertains to the following DBA.StackExchange answer:
  3.     http://dba.stackexchange.com/questions/136674/lob-data-slow-table-scans-and-some-i-o-questions/137869#137869
  4.  
  5.     By: Solomon Rutzky
  6.     Created on: 2016-05-09
  7.     Last Modified on: 2016-05-11
  8.     URL of this script: http://pastebin.com/bsrPj2u7
  9.  ****************************************************************************************************************/
  10.  
  11.  
  12.     -----------------------------------------------------------------------------
  13.     -----------------------------------------------------------------------------
  14.     ----
  15.     ----  Do not run this script all at once!
  16.     ----  Run the block-comment sections as each one indicates.
  17.     ----  Highlight and execute each numbered section independently, and in order.
  18.     ----
  19.     -----------------------------------------------------------------------------
  20.     -----------------------------------------------------------------------------
  21.  
  22.  
  23. /* -- Highlight and execute the following statements once:
  24. USE [tempdb];
  25. SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON;
  26. GO
  27. -- DROP TABLE #XmlTest;
  28. CREATE TABLE #XmlTest (Col1 INT PRIMARY KEY CLUSTERED, Col2 BIGINT, Col3 DATETIME, Col4 UNIQUEIDENTIFIER, Col5 XML);
  29. */
  30.  
  31.  
  32. -- 1A) IN_ROW LOB data
  33. INSERT INTO #XmlTest (Col1, Col2, Col3, Col4, Col5) VALUES (1, 200000000, GETDATE(), NEWID(),
  34.         N'<test name="in row LOB"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 3960) + N'</val></test>');
  35.  
  36. SELECT DATALENGTH(tmp.Col5) FROM #XmlTest tmp WHERE tmp.Col1 = 1; -- 7999 bytes
  37. SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest'), NULL, NULL, N'DETAILED');
  38. -- 1 page of IN_ROW_DATA : IAM_PAGE
  39. -- 1 page of IN_ROW_DATA : DATA_PAGE
  40.  
  41.  
  42. -- 1B) Inline Root LOB data
  43. INSERT INTO #XmlTest (Col1, Col2, Col3, Col4, Col5) VALUES (2, 400000000, GETDATE(), NEWID(),
  44.         N'<test name="inline root LOB"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'b'), 20055) + N'</val></test>');
  45.  
  46. SELECT DATALENGTH(tmp.Col5) FROM #XmlTest tmp WHERE tmp.Col1 = 2; -- 40200 bytes
  47. SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest'), NULL, NULL, N'DETAILED');
  48. -- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
  49. -- 1 pre-existing page of IN_ROW_DATA : DATA_PAGE
  50. -- 1 new page of IN_ROW_DATA : INDEX_PAGE
  51. -- 1 new page of IN_ROW_DATA : DATA_PAGE
  52. -- 1 new page of LOB_DATA : IAM_PAGE
  53. -- 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)
  54.  
  55.  
  56. -- 1C) TEXT_TREE LOB data
  57. INSERT INTO #XmlTest (Col1, Col2, Col3, Col4, Col5) VALUES (3, 600000000, GETDATE(), NEWID(),
  58.         N'<test name="text_tree LOB"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'c'), 20058) + N'</val></test>');
  59.  
  60. SELECT DATALENGTH(tmp.Col5) FROM #XmlTest tmp WHERE tmp.Col1 = 3; -- 40202 bytes
  61. SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest'), NULL, NULL, N'DETAILED');
  62. -- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
  63. -- 1 pre-existing page of IN_ROW_DATA : DATA_PAGE
  64. -- 1 pre-existing page of IN_ROW_DATA : INDEX_PAGE
  65. -- 1 pre-existing page of IN_ROW_DATA : DATA_PAGE
  66. -- 1 pre-existing page of LOB_DATA : IAM_PAGE
  67. -- 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)
  68. -- 5 new pages of LOB_DATA : TEXT_MIX_PAGE
  69. -- 1 new page of LOB_DATA : TEXT_TREE_PAGE
  70.  
  71.  
  72. -- Capture PageIDs so we can check against Buffer Pool without reading them into the Buffer Pool
  73. /* -- Highlight and execute the statements below once:
  74. -- DROP TABLE #Pages;
  75. CREATE TABLE #Pages (FileID INT, PageID INT);
  76. INSERT INTO #Pages (FileID, PageID)
  77.     SELECT dddpa.allocated_page_file_id, dddpa.allocated_page_page_id
  78.     FROM   sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest'), NULL, NULL, N'DETAILED') dddpa;
  79. SELECT * FROM #Pages;
  80. */
  81.  
  82. USE [tempdb];
  83.  
  84.  
  85. /* -- Before each test, highlight and execute the 3 lines below:
  86. CHECKPOINT;
  87. DBCC DROPCLEANBUFFERS;
  88. SELECT * FROM sys.dm_os_buffer_descriptors dobd
  89. WHERE dobd.database_id = DB_ID(N'tempdb');
  90. */
  91.  
  92. -- 1D) Check "all columns" reads for IN_ROW LOB data
  93. SET STATISTICS IO ON;
  94. SELECT * FROM #XmlTest WHERE Col1 = 1;
  95. SET STATISTICS IO OFF;
  96. -- Table '#XmlTest'. Scan count 0, logical reads 2,     physical reads 2,     read-ahead reads 0,
  97. --                                 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  98.  
  99. /* -- After each test, highlight and execute the following query:
  100. SELECT * FROM sys.dm_os_buffer_descriptors dobd
  101. INNER JOIN #Pages tmp
  102.     ON tmp.FileID = dobd.[file_id]
  103.     AND tmp.PageID = dobd.page_id
  104. WHERE dobd.database_id = DB_ID(N'tempdb');
  105. */
  106.  
  107.  
  108. -- 1E) Check "all columns" reads for Inline Root LOB data (off row)
  109. SET STATISTICS IO ON;
  110. SELECT * FROM #XmlTest WHERE Col1 = 2;
  111. SET STATISTICS IO OFF;
  112. -- Table '#XmlTest'. Scan count 0, logical reads 2,      physical reads 2,     read-ahead reads 0,
  113. --                                 lob logical reads 14, lob physical reads 0, lob read-ahead reads 16.
  114.  
  115.  
  116. -- 1F) Check "all columns" reads for TEXT_TREE LOB data (off row)
  117. SET STATISTICS IO ON;
  118. SELECT * FROM #XmlTest WHERE Col1 = 3;
  119. SET STATISTICS IO OFF;
  120. -- Table '#XmlTest'. Scan count 0, logical reads 2,      physical reads 2,     read-ahead reads 0,
  121. --                                 lob logical reads 30, lob physical reads 1, lob read-ahead reads 15.
  122.  
  123.  
  124.  
  125. /* -- Before each test, highlight and execute the 3 lines below:
  126. CHECKPOINT;
  127. DBCC DROPCLEANBUFFERS;
  128. SELECT * FROM sys.dm_os_buffer_descriptors dobd
  129. WHERE dobd.database_id = DB_ID(N'tempdb');
  130. */
  131.  
  132. -- 1G) Check non-XML columns reads for row with Inline Root LOB data (off row)
  133. SET STATISTICS IO ON;
  134. SELECT tmp.Col1, tmp.Col2, tmp.Col3, tmp.Col4 FROM #XmlTest tmp WHERE Col1 = 2;
  135. SET STATISTICS IO OFF;
  136. -- Table '#XmlTest'. Scan count 0, logical reads 2,     physical reads 2,     read-ahead reads 0,
  137. --                                 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  138.  
  139.  
  140. -- 1H) Check non-XML columns reads for TEXT_TREE LOB data (off row)
  141. SET STATISTICS IO ON;
  142. SELECT tmp.Col1, tmp.Col2, tmp.Col3, tmp.Col4 FROM #XmlTest tmp WHERE Col1 = 3;
  143. SET STATISTICS IO OFF;
  144. -- Table '#XmlTest'. Scan count 0, logical reads 2,     physical reads 2,     read-ahead reads 0,
  145. --                                 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  146.  
  147.  
  148. -----------------------------------------------------------------------------------
  149. -----------------------------------------------------------------------------------
  150. --
  151. --  PART 2: Test effect of not having enough space left in-row for all 5 pointers
  152. --          of the Inline Root. Will the Inline Root be skipped and go directly
  153. --          to using a TEXT_TREE, or will it use as many pointers as will fit?
  154. --
  155. -----------------------------------------------------------------------------------
  156.  
  157.  
  158. /* -- Highlight and execute the following statements once:
  159. USE [tempdb];
  160. SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON;
  161. GO
  162. -- DROP TABLE #XmlTest2;
  163. CREATE TABLE #XmlTest2 (Col1 INT PRIMARY KEY CLUSTERED, Col2 INT, Col3 SMALLDATETIME, Col4 CHAR(7980), Col5 XML);
  164. */
  165.  
  166.  
  167. -- 2A) IN_ROW LOB data
  168. INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (1, 20, GETDATE(), REPLICATE('Z', 7980),
  169.         N'<test name="in-row"/>');
  170.  
  171. SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 1; -- 53 bytes
  172. SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
  173. -- 1 page of IN_ROW_DATA : IAM_PAGE
  174. -- 1 page of IN_ROW_DATA : DATA_PAGE
  175. ---- 2 pages total
  176.  
  177.  
  178. -- 2B) Inline Root LOB data # 1 (small, but cannot fit in-row)
  179. INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (2, 40, GETDATE(), REPLICATE('Y', 7980),
  180.         N'<test name="inline root LOB # 1"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 3) + N'</val></test>');
  181.  
  182. SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 2; -- 102 bytes
  183. SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
  184. -- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
  185. -- 1 pre-existing page of IN_ROW_DATA : DATA_PAGE
  186. -- 1 new page of IN_ROW_DATA : INDEX_PAGE
  187. -- 1 new page of IN_ROW_DATA : DATA_PAGE
  188. -- 1 new page of LOB_DATA : IAM_PAGE
  189. -- 1 new page of LOB_DATA : TEXT_MIX_PAGE (50% free space)
  190. ---- 6 pages total
  191.  
  192.  
  193. -- 2C) Inline Root LOB data # 2 (should require 2 LOB pages = 36 bytes for in-row pointers)
  194. INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (3, 60, GETDATE(), REPLICATE('X', 7980),
  195.         N'<test name="inline root LOB # 2"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'b'), 4500) + N'</val></test>');
  196.  
  197. SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 3; -- 9097 bytes
  198. SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
  199. -- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
  200. -- 2 pre-existing pages of IN_ROW_DATA : DATA_PAGE
  201. -- 1 pre-existing page page of IN_ROW_DATA : INDEX_PAGE
  202. -- 1 new page of IN_ROW_DATA : DATA_PAGE
  203. -- 1 pre-existing page of LOB_DATA : IAM_PAGE
  204. -- 1 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (50% free space)
  205. -- 1 new page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
  206. ---- 8 pages total
  207.  
  208.  
  209. -- 2D) Inline Root LOB data # 3 (should require 3 LOB pages = 48 bytes for in-row pointers)
  210. INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (4, 80, GETDATE(), REPLICATE('W', 7980),
  211.         N'<test name="inline root LOB # 3"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'c'), 12011) + N'</val></test>');
  212.  
  213. SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 4; -- 24119 bytes
  214. SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
  215. -- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
  216. -- 3 pre-existing pages of IN_ROW_DATA : DATA_PAGE
  217. -- 1 pre-existing page page of IN_ROW_DATA : INDEX_PAGE
  218. -- 1 new page of IN_ROW_DATA : DATA_PAGE
  219. -- 1 pre-existing page of LOB_DATA : IAM_PAGE
  220. -- 1 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (50% free space)
  221. -- 1 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
  222. -- 3 new page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
  223. ---- 12 pages total
  224.  
  225.  
  226. -- 2E) TEXT_TREE LOB data (should require 4 LOB pages = 60 bytes for in-row pointers, BUT not enough space left for 60 bytes)
  227. INSERT INTO #XmlTest2 (Col1, Col2, Col3, Col4, Col5) VALUES (5, 100, GETDATE(), REPLICATE('V', 7980),
  228.         N'<test name="text_tree LOB"><val>' + REPLICATE(CONVERT(NVARCHAR(MAX), N'd'), 12018) + N'</val></test>');
  229.  
  230. SELECT DATALENGTH(tmp.Col5) FROM #XmlTest2 tmp WHERE tmp.Col1 = 5; -- 24121 bytes
  231. SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(N'tempdb'), OBJECT_ID(N'tempdb..#XmlTest2'), NULL, NULL, N'DETAILED');
  232. -- 1 pre-existing page of IN_ROW_DATA : IAM_PAGE
  233. -- 4 pre-existing pages of IN_ROW_DATA : DATA_PAGE
  234. -- 1 pre-existing page page of IN_ROW_DATA : INDEX_PAGE
  235. -- 1 new page of IN_ROW_DATA : DATA_PAGE
  236. -- 1 pre-existing page of LOB_DATA : IAM_PAGE
  237. -- 1 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (50% free space)
  238. -- 4 pre-existing page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
  239. -- 3 new page of LOB_DATA : TEXT_MIX_PAGE (0% free space)
  240. -- 1 new page of LOB_DATA : TEXT_TREE_PAGE
  241. -- 7 new unallocated pages
  242. ---- 24 pages total
  243.  
  244.  
  245.  
  246. -- DBCC PAGE(2, 1, 330, 3) WITH TABLERESULTS;
  247.  
  248. -----------------------------------------------------------------------------------
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