Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA).

仅仅在表中使用XML列没有那种效果.在某些情况下,XML数据的存在会导致行的数据的某些部分在LOB_DATA页面上存储在行外.虽然一个(或者几个;-)可能会争辩说,但是,XML列暗示确实存在XML数据,但不能保证XML数据需要存储在行外：除非该行已经很多了在任何XML数据之外填充,小文档(最多8000个字节)可能适合行内并且永远不会转到LOB_DATA页面.

am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size,but also because sql Server can’t scan the clustered index effectively when there’s a lot of LOB_DATA pages in the table?

扫描是指查看所有行.当然,当读取数据页时,即使您选择了列的子集,也会读取所有行内数据.与LOB数据的不同之处在于,如果不选择该列,则不会读取行外数据.因此,对于sql Server如何有效地扫描此聚簇索引得出结论并不公平,因为您没有完全测试(或者您测试了一半).您选择了所有列,其中包括XML列,如您所述,这是大多数数据所在的位置.

所以我们已经知道SELECT TOP 1000 *测试不只是连续读取一系列8k数据页,而是每行跳到其他位置. LOB数据的确切结构可以根据它的大小而变化.根据此处显示的研究(What is the Size of the LOB Pointer for (MAX) Types Like Varchar,Varbinary,Etc?),有两种类型的行外LOB分配：

>内联根 – 对于8001到40,000(实际为42,000)字节之间的数据,在空间允许的情况下,将有1到5个指针(24-72字节)IN ROW直接指向LOB页面.
> TEXT_TREE – 对于超过42,000字节的数据,或者如果1到5指针不能适合行内,则只有一个24字节指针指向LOB页面指针列表的起始页(即“text_tree”页面).

每次检索超过8000字节或不适合行内的LOB数据时,都会发生这两种情况之一.我在PasteBin.com(T-SQL script to test LOB allocations and reads)上发布了一个测试脚本,它显示了3种类型的LOB分配(基于数据的大小)以及每种类型对逻辑和物理读取的影响.在您的情况下,如果XML数据确实每行少于42,000个字节,那么它们中的任何一个(或者很少)都应该是效率最低的TEXT_TREE结构.

如果要测试sql Server扫描该聚簇索引的速度,请执行SELECT TOP 1000,但指定一个或多个不包含该XML列的列.这对您的结果有何影响？它应该快一点.

is it considered reasonable to have such a table structure/data pattern?

鉴于我们对实际表结构和数据模式的描述不完整,任何答案可能都不是最佳的,具体取决于缺少的细节.考虑到这一点,我会说你的表结构或数据模式显然没有任何不合理之处.

I can (in a c# app) compress XML from 20KB to ~2.5KB and store it in VARBINARY column,preventing usage of LOB data pages. This speeds SELECTs 20x times in my tests.

这使得选择所有列,甚至只是更快地选择XML数据(现在在VARBINARY中),但它实际上会伤害不选择“XML”数据的查询.假设您在其他列中有大约50个字节并且FILLFACTOR为100,那么：

>无压缩：15k的XML数据应该需要2个LOB_DATA页面,然后需要2个指针用于内联根.第一个指针是24个字节,第二个指针是12,对于XML数据,行内存储的总共36个字节.总行大小为86字节,您可以将这些行中的大约93行放入8060字节数据页.因此,100万行需要10,753个数据页.
>自定义压缩：2.5k的VARBINARY数据将适合行内.总行大小为2610(2.5 * 1024 = 2560)字节,您只能将这些行中的3行放入8060字节数据页.因此,100万行需要333,334个数据页.

因此,实施自定义压缩会使聚集索引的数据页面增加30倍.这意味着,使用聚簇索引扫描的所有查询现在都有大约322,500个要读取的数据页.有关进行此类压缩的其他后果,请参阅下面的详细部分.

我会警告不要根据SELECT TOP 1000 *的性能进行任何重构.这不太可能是应用程序甚至会发出的查询,也不应该被用作可能不必要的优化的唯一基础.

有关更多详细信息和更多测试,请参阅以下部分.

这个问题无法给出明确的答案,但我们至少可以取得一些进展并提出进一步的研究,以帮助我们更接近找出确切的问题(理想情况是基于证据).

我们所知道的：

>表大约有100万行
>表大小约为15 GB
> Table包含一个XML列和其他几个类型的列：INT,BIGINT,UNIQUEIDENTIFIER,“etc”
> XML列“大小”平均大约为15k
>运行DBCC DROPCLEANBUFFERS后,以下查询完成需要20 – 25秒：SELECT TOP 1000 * FROM TABLE
>正在扫描聚集索引
>聚集指数的碎片接近0％

我们认为我们所知道的：

>这些查询之外没有其他磁盘活动.你确定吗？即使没有其他用户查询,是否还有后台操作？是否有sql Server外部的进程在可能占用一些IO的同一台机器上运行？可能没有,但仅根据提供的信息不清楚.
>返回> 15 MB的XML数据.这个号码是基于什么的？从1000行乘以每行15k XML数据的平均值得出的估计值？或者对该查询收到的内容的程序化聚合？如果它只是一个估计,我不会依赖它,因为XML数据的分布可能不是简单平均所暗示的方式.
> XML压缩可能会有所帮助.你究竟如何在.NET中进行压缩？通过GZipStream或DeflateStream课程？这不是零成本选择.它肯定会压缩一些数据,但它也需要更多的cpu,因为每次都需要一个额外的过程来压缩/解压缩数据.该计划还将完全消除您的能力：

>通过.nodes,.value,.query和.modify XML函数查询XML数据.
>索引XML数据.

请记住(因为您提到XML是“高度冗余的”)XML数据类型已经过优化,因为它将元素和属性名称存储在字典中,为每个项目分配整数索引ID,然后使用该整数整个文档中的ID(因此它不会重复每次使用的全名,也不会再次重复它作为元素的结束标记).实际数据还删除了无关的空白区域.这就是为什么提取的XML文档不保留其原始结构以及为什么空元素提取为< element />的原因.即使他们以< element>< / element>进入.所以通过压缩GZip(或其他任何东西)得到的任何收益只能通过压缩元素和/或属性值来找到,这是一个比大多数人预期的要小得多的表面积,并且很可能不值得丢失如上所述的能力.

还请记住,压缩XML数据和存储VARBINARY(MAX)结果不会消除LOB访问,它只会减少它.根据行上其余数据的大小,压缩值可能适合行内,或者可能仍需要LOB页面.

这些信息虽然有用,但还不够.有很多因素会影响查询性能,因此我们需要更详细的了解情况.

我们不知道但需要：

>为什么SELECT *的性能很重要？这是您在代码中使用的模式.如果是这样,为什么？
>仅选择XML列的性能如何？如果你只是做什么统计和时间：SELECT TOP 1000 XmlColumn FROM TABLE; ？
>返回这1000行所需的20 – 25秒中有多少与网络因素(通过线路获取数据)有关,以及与客户端因素有多大关系(渲染大约15 MB加上其余的非线性) -XML数据进入SSMS网格,还是可能保存到磁盘)？

有时可以通过简单地不返回数据来解决操作的这两个方面.现在,人们可能会考虑选择临时表或表变量,但这只会引入一些新变量(即tempdb的磁盘I / O,事务日志写入,tempdb数据和/或日志文件可能的自动增长,需要缓冲池中的空间等).所有这些新因素实际上可以增加查询时间.相反,我通常将列存储到变量(适当的数据类型;而不是sql_VARIANT)中,这些变量会被每个新行覆盖(即SELECT @ Column1 = tab.Column1,…).

但是,正如@PaulWhite在这个DBA.StackExchange Q& A,Logical reads different when accessing the same LOB data,在PasteBin(T-SQL script to test various scenarios for LOB reads)上发布了我自己的其他研究,在SELECT,SELECT INTO,SELECT @XmlVariable = XmlColumn,SELECT @XmlVariable = XmlColumn.query(N’/’)和SELECT之间不能一致地访问LOB @NVarCharVariable = CONVERT(NVARCHAR(MAX),XmlColumn).所以我们的选择在这里有一点限制,但这里可以做的是：

>通过在SSMS或sqlCMD.EXE中运行sql Server的服务器上执行查询来排除网络问题.
>通过转到查询选项 – >来排除SSMS中的客户问题.结果 – >网格并检查“执行后丢弃结果”选项.请注意,此选项将阻止所有输出,包括消息,但仍可用于排除SSMS每行分配内存然后在网格中绘制所需的时间.
或者,您可以通过sqlCMD.EXE执行查询,并通过以下方式将输出定向到无处：-o NUL：.

>是否存在与此查询关联的等待类型？如果是,那等待类型是什么？
>返回的XML列的实际数据大小是多少？如果“TOP 1000”行包含总XML数据中不成比例的大部分,那么整个表中该列的平均大小并不重要.如果您想了解TOP 1000行,请查看这些行.请运行以下内容：

SELECT TOP 1000 tab.*,SUM(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [TotalXmlKBytes],AVG(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [AverageXmlKBytes]
       STDEV(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [StandardDeviationForXmlKBytes]
FROM   SchemaName.TableName tab;

>确切的表架构.请提供完整的CREATE TABLE语句,包括所有索引.
>查询计划？这是你可以发布的东西吗？这个信息可能不会改变任何东西,但最好知道它不会猜测它不会是错的;-)
>数据文件中是否存在物理/外部碎片？虽然这可能不是一个很重要的因素,但由于您使用的是“消费级SATA”而不是SSD甚至是超级昂贵的SATA,因此次优订购扇区的影响将更加明显,尤其是这些扇区的数量需要阅读的人数增加了.
>以下查询的确切结果是什么：

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'dbo.SchemaName.TableName'),1,N'LIMITED');

UPDATE

在我看来,我应该尝试重现这个场景,看看我是否经历过类似的行为.因此,我创建了一个包含多个列的表(类似于问题中的模糊描述),然后用100万行填充它,XML列每行有大约15k的数据(参见下面的代码).

我发现第一次在8秒内完成SELECT TOP 1000 * FROM TABLE,之后每次2到4秒(是的,在每次运行SELECT *查询之前执行DBCC DROPCLEANBUFFERS).我几年前的笔记本电脑并不快：sql Server 2012 SP2开发人员版,64位,6 GB RAM,双2.5 Ghz Core i5和5400 RPM SATA驱动器.我还运行SSMS 2014,sql Server Express 2014,Chrome和其他一些东西.

根据我系统的响应时间,我将重复我们需要更多信息(即有关表和数据的详细信息,建议的测试结果等),以帮助缩小20-25秒响应时间的原因你看到了.

SET ANSI_NULLS,NOCOUNT ON;
GO

IF (OBJECT_ID(N'dbo.XmlReadTest') IS NOT NULL)
BEGIN
    PRINT N'Dropping table...';
    DROP TABLE dbo.XmlReadTest;
END;

PRINT N'Creating table...';
CREATE TABLE dbo.XmlReadTest 
(
    ID INT NOT NULL IDENTITY(1,1),Col2 BIGINT,Col3 UNIQUEIDENTIFIER,Col4 DATETIME,Col5 XML,CONSTRAINT [PK_XmlReadTest] PRIMARY KEY CLUSTERED ([ID])
);
GO

DECLARE @MaxSets INT = 1000,@CurrentSet INT = 1;

WHILE (@CurrentSet <= @MaxSets)
BEGIN
    RAISERROR(N'Populating data (1000 sets of 1000 rows); Set # %d ...',10,@CurrentSet) WITH NOWAIT;
    INSERT INTO dbo.XmlReadTest (Col2,Col3,Col4,Col5)
        SELECT  TOP 1000
                CONVERT(BIGINT,CRYPT_GEN_RANDOM(8)),NEWID(),GETDATE(),N'<test>'
                  + REPLICATE(CONVERT(NVARCHAR(MAX),CRYPT_GEN_RANDOM(1),2),3750)
                  + N'</test>'
        FROM        [master].[sys].all_columns sac1;

    IF ((@CurrentSet % 100) = 0)
    BEGIN
        RAISERROR(N'Executing CHECKPOINT ...',1) WITH NOWAIT;
        CHECKPOINT;
    END;

    SET @CurrentSet += 1;
END;

--

SELECT COUNT(*) FROM dbo.XmlReadTest; -- Verify that we have 1 million rows

-- O.P. states that the "clustered index fragmentation is close to 0%"
ALTER INDEX [PK_XmlReadTest] ON dbo.XmlReadTest REBUILD WITH (FILLFACTOR = 90);
CHECKPOINT;

--

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO,TIME ON;
SELECT TOP 1000 * FROM dbo.XmlReadTest;
SET STATISTICS IO,TIME OFF;

/*
Scan count 1,logical reads 21,physical reads 1,read-ahead reads 4436,lob logical reads 5676,lob physical reads 1,lob read-ahead reads 3967.

 sql Server Execution Times:
   cpu time = 171 ms,elapsed time = 8329 ms.
*/

并且,因为我们想要分解读取非LOB页面所花费的时间,所以我运行以下查询来选择除XML列之外的所有页面(我在上面建议的测试之一).这相当一致地在1.5秒内返回.

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO,TIME ON;
SELECT TOP 1000 ID,Col2,Col4 FROM dbo.XmlReadTest;
SET STATISTICS IO,lob logical reads 0,lob physical reads 0,lob read-ahead reads 0.

 sql Server Execution Times:
   cpu time = 0 ms,elapsed time = 1666 ms.
*/

结论(暂时)
根据我重新创建场景的尝试,我认为我们不能指出SATA驱动器或非顺序I / O是20 – 25秒的主要原因,特别是因为我们仍然不知道有多快不包含XML列时返回查询.而且我无法重现你正在展示的大量逻辑读取(非LOB),但我觉得我需要根据这一点和每条行添加更多数据：

~90% of table pages are LOB_DATA

我的表有100万行,每行有超过15k的XML数据,sys.dm_db_index_physical_stats显示有200万个LOB_DATA页面.剩下的10％将是222k IN_ROW数据页,但我只有11,630个.因此,我们需要有关实际表模式和实际数据的更多信息.