以下是三个简单的测试,它们读取相同的数据,但报告的逻辑读取非常不同：
建立
以下脚本创建一个包含100个相同行的测试表,每个行包含一个xml列,其中包含足够的数据以确保它存储在行外.在我的测试数据库中,每行生成的xml的长度为20,204字节.
-- 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;
测试
以下三个测试读取xml列：
>一个普通的SELECT语句
>将xml分配给变量
>使用SELECT INTO创建临时表
-- 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;
结果
输出是：
=== 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,lob logical reads 0,lob physical reads 0,lob read-ahead reads 0. === SELECT INTO ==== Table 'XMLTest'. Scan count 1,lob logical reads 300,lob read-ahead reads 400.
问题
>为什么LOB读取如此不同？
>当然,每次测试都会读取完全相同的数据？
解决方法
并非所有读数都相同. sql Server知道访问LOB数据很昂贵,并尽可能避免使用它.在每种情况下,读取LOB数据的方式也存在细微差别：
数字不同,因为：
> select以数据包大小的块读取LOB
>变量赋值测试根本不读取LOB
>“select into”测试读取整页中的LOB
详情
>普通SELECT
聚集索引扫描不读取任何LOB数据.它只分配一个存储引擎LOB句柄.在控制权返回到计划的根目录之前,不会使用句柄.
当前行的LOB内容以TDS数据包大小的块读取并流式传输到客户端.逻辑读取计算页面被触摸的次数,因此：
报告的读取数等于执行的分块读取数,每次发生LOB页转换时加1.
例如：当进程触摸对应于流的当前位置的页面时,在每个块的开始处计数逻辑读取.如果数据包小于数据库页面(通常情况下),则会对同一页面计数多个逻辑读取.如果数据包大小太大以至于整个LOB可以放在一个块中,则报告的逻辑读取数将是LOB页数.
>变量赋值
Clustered Index Scan像以前一样分配LOB句柄.在计划的根目录中,LOB句柄被复制到变量中.永远不会访问LOB数据本身(零LOB读取),因为永远不会读取变量.即使它是,它只会通过最后分配的LOB句柄.
没有LOB读取,因为永远不会访问LOB数据.
>选择INTO
此计划使用批量行集提供程序将LOB数据从源表复制到新表.它在每次读取时处理完整的LOB页面(无流式传输或分块).
