Here are three simple tests that read the same data, yet report very different logical reads:
Setup
The following script creates a test table with 100 identical rows, each containing an xml column with enough data to ensure it is stored off row. In my test database, the length of the xml generated is 20,204 bytes for each row.
-- Conditional drop
IF OBJECT_ID(N'dbo.XMLTest', N'U') IS NOT NULL
DROP TABLE dbo.XMLTest;
GO
-- Create test table
CREATE TABLE dbo.XMLTest
(
ID integer IDENTITY PRIMARY KEY,
X xml NULL
);
GO
-- Add 100 wide xml rows
DECLARE @X xml;
SET @X =
(
SELECT TOP (100) *
FROM sys.columns AS C
FOR XML
PATH ('row'),
ROOT ('root'),
TYPE
);
INSERT dbo.XMLTest
(X)
SELECT TOP (100)
@X
FROM sys.columns AS C;
-- Flush dirty buffers
CHECKPOINT;
Tests
The following three tests read the xml column with:
- A plain
SELECT
statement - Assigning the xml to a variable
- Using
SELECT INTO
to create a temporary table
-- No row count messages or graphical plan
-- Show I/O statistics
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET STATISTICS IO ON;
GO
PRINT CHAR(10) + '=== Plain SELECT ===='
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== Assign to a variable ===='
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DECLARE @X xml;
SELECT
@X = XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== SELECT INTO ===='
IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL
DROP TABLE #T;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT
XT.X
INTO #T
FROM dbo.XMLTest AS XT
GO
SET STATISTICS IO OFF;
Results
The output is:
=== Plain SELECT ==== Table 'XMLTest'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 795, lob physical reads 37, lob read-ahead reads 796. === Assign to a variable ==== Table 'XMLTest'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. === SELECT INTO ==== Table 'XMLTest'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 300, lob physical reads 37, lob read-ahead reads 400.
Questions
- Why are the LOB reads so different?
- Surely the exact same data was read in each test?