主页 / dba / 问题 / 136674
Accepted
Alex Shelemin
Asked: 2016-04-27 14:30:12 +0800 CST

LOB_DATA、缓慢的表扫描和一些 I/O 问题

  • 772

我有一个相当大的表，其中一列是 XML 数据，XML 条目的平均大小约为 15 KB。所有其他列都是常规整数、大整数、GUID 等。要获得一些具体数字，假设该表有一百万行，大小约为 15 GB。

我注意到的是，如果我想选择所有列，那么从这个表中选择数据真的很慢。当我做

SELECT TOP 1000 * FROM TABLE

从磁盘读取数据大约需要 20-25 秒——即使我没有对结果进行任何排序。我使用冷缓存（即 after DBCC DROPCLEANBUFFERS）运行查询。IO统计结果如下：

扫描计数 1，逻辑读取 364，物理读取 24，预读读取 7191，lob 逻辑读取 7924，lob 物理读取 1690，lob 预读读取 3968。

它抓取约 15 MB 的数据。正如我所料，执行计划显示聚集索引扫描。

除了我的查询之外，磁盘上没有 IO 进行；我还检查了聚集索引碎片接近 0%。这是一个消费级 SATA 驱动器，但我仍然认为 SQL Server 能够以超过 ~100-150 MB/分钟的速度扫描表。

XML 字段的存在导致大部分表数据位于 LOB_DATA 页面上（实际上大约 90% 的表页面是 LOB_DATA）。

我想我的问题是 - 我认为 LOB_DATA 页面会导致缓慢扫描不仅是因为它们的大小，而且还因为当表中有很多 LOB_DATA 页面时 SQL Server 无法有效地扫描聚集索引，我是否正确？

更广泛地说——拥有这样的表结构/数据模式是否合理？使用 Filestream 的建议通常说明更大的字段大小，所以我真的不想走那条路。我还没有真正找到关于这个特定场景的任何好信息。

我一直在考虑 XML 压缩，但它需要在客户端或使用 SQLCLR 完成，并且需要在系统中实现相当多的工作。

我尝试了压缩，由于 XML 高度冗余，我可以（在 ac# 应用程序中）将 XML 从 20KB 压缩到 ~2.5KB 并将其存储在 VARBINARY 列中，从而防止使用 LOB 数据页。这在我的测试中将 SELECT 速度提高了 20 倍。

sql-server performance

2 个回答

  1. Best Answer

    XML 字段的存在导致大部分表数据位于 LOB_DATA 页面上（实际上大约 90% 的表页面是 LOB_DATA）。

    仅在表中包含 XML 列不会产生这种效果。在某些条件下，正是 XML数据的存在导致行数据的某些部分存储在行外的 LOB_DATA 页面上。虽然一个（或几个 ;-) 可能会争辩说，呃，该列暗示确实会有 XML 数据，但不能保证 XML 数据需要存储在行外：除非该行已经被填满除了它们是任何 XML 数据之外，小文档（最多 8000 字节）可能适合行内并且永远不会进入 LOB_DATA 页面。XML

    我是否正确地认为 LOB_DATA 页面会导致缓慢的扫描，不仅因为它们的大小，还因为当表中有很多 LOB_DATA 页面时，SQL Server 无法有效地扫描聚集索引？

    扫描是指查看所有行。当然，当读取数据页时，会读取所有行内数据，即使您选择了列的子集。与 LOB 数据的不同之处在于，如果不选择该列，则不会读取行外数据。因此，得出关于 SQL Server 扫描此聚集索引的效率的结论是不公平的，因为您没有完全测试它（或者您测试了它的一半）。您选择了所有列，其中包括 XML 列，正如您所提到的，这是大部分数据所在的位置。

    所以我们已经知道，SELECT TOP 1000 *测试不仅仅是连续读取一系列 8k 数据页，而是每行跳转到其他位置。该 LOB 数据的确切结构可能因它的大小而异。根据此处显示的研究（什么是（MAX）类型的 LOB 指针的大小，例如 Varchar、Varbinary 等？），有两种类型的行外 LOB 分配：

    1. 内联根——对于 8001 到 40,000（实际上是 42,000）字节之间的数据，如果空间允许，在 ROW 中将有 1 到 5 个指针（24 - 72 字节）直接指向 LOB 页。
    2. TEXT_TREE——对于超过 42,000 字节的数据，或者如果 1 到 5 个指针不能容纳在行中，那么将只有一个 24 字节指针指向指向 LOB 页的指针列表的起始页（即“ text_tree”页面）。

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

    如果您想测试 SQL Server 扫描该聚集索引的速度有多快，请SELECT TOP 1000指定一个或多个不包括该 XML 列的列。这对您的结果有何影响？它应该快一点。

    拥有这样的表结构/数据模式是否合理？

    鉴于我们对实际表结构和数据模式的描述不完整，任何答案都可能不是最佳的，具体取决于那些缺失的细节是什么。考虑到这一点，我想说您的表结构或数据模式没有明显不合理的地方。

    我可以（在 ac# 应用程序中）将 XML 从 20KB 压缩到 ~2.5KB 并将其存储在 VARBINARY 列中，从而防止使用 LOB 数据页。这在我的测试中将 SELECT 速度提高了 20 倍。

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

    • 无压缩：15k 的XML数据应该需要 2 个 LOB_DATA 页，然后需要 2 个指针用于 Inline Root。第一个指针是 24 个字节，第二个是 12 个字节，总共 36 个字节存储在 XML 数据的行中。总行大小为 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 *. 这不太可能是应用程序甚至会发出的查询，并且不应用作可能不必要的优化的唯一基础。

    有关更多详细信息和更多测试，请参阅下面的部分。

    这个问题不能给出明确的答案，但我们至少可以取得一些进展并提出额外的研究，以帮助我们更接近于找出确切的问题（最好基于证据）。

    我们所知道的：

    1. 表大约有 100 万行
    2. 表大小约为 15 GB
    3. 表包含一XML列和其他几列类型：INT, BIGINT, UNIQUEIDENTIFIER, "etc"
    4. XML“大小”列平均约为 15k
    5. 运行后DBCC DROPCLEANBUFFERS，完成以下查询需要 20 - 25 秒：SELECT TOP 1000 * FROM TABLE
    6. 正在扫描聚集索引
    7. 聚集索引上的碎片接近 0%

    我们认为我们知道的：

    1. 这些查询之外没有其他磁盘活动。你确定吗？即使没有其他用户查询，是否有后台操作发生？是否有运行在同一台机器上的 SQL Server 外部进程可能占用一些 IO？可能没有，但仅根据提供的信息尚不清楚。
    2. 正在返回 15 MB 的 XML 数据。这个数字的依据是什么？从 1000 行乘以每行 15k XML 数据的平均值得出的估计值？还是对该查询接收到的内容进行编程聚合？如果这只是一个估计，我不会依赖它，因为 XML 数据的分布可能与简单平均所暗示的方式不同。

    3. XML 压缩可能会有所帮助。您将如何在 .NET 中进行压缩？通过GZipStream或DeflateStream类？这不是一个零成本的选择。它肯定会大量压缩一些数据，但它也需要更多的 CPU，因为您每次都需要一个额外的过程来压缩/解压缩数据。该计划还将完全消除您的以下能力：

      • .nodes通过、.value、.query和.modifyXML 函数查询 XML 数据。

      • 索引 XML 数据。

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

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

    这些信息虽然有帮助，但还远远不够。有很多因素会影响查询性能，因此我们需要更详细地了解正在发生的事情。

    我们不知道，但需要：

    1. 为什么性能SELECT *很重要？这是您在代码中使用的模式吗？如果是这样，为什么？
    2. 只选择 XML 列的性能如何？如果您只这样做，统计数据和时间是什么：SELECT TOP 1000 XmlColumn FROM TABLE;？

    3. 返回这 1000 行所需的 20 到 25 秒中有多少与网络因素有关（通过网络获取数据），又有多少与客户端因素有关（呈现大约 15 MB 加上其余的非XML 数据在 SSMS 中的网格中，或者可能保存到磁盘）？

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

      但是，正如@PaulWhite 在此 DBA.StackExchange Q & A 中所指出的，在访问相同的 LOB 数据时，逻辑读取不同，我自己在 PasteBin 上发布的其他研究（用于测试 LOB 读取的各种场景的 T-SQL 脚本） , LOB 在SELECT, SELECT INTO, SELECT @XmlVariable = XmlColumn,SELECT @XmlVariable = XmlColumn.query(N'/')和之间的访问不一致SELECT @NVarCharVariable = CONVERT(NVARCHAR(MAX), XmlColumn)。所以我们的选择在这里有点有限，但这里是可以做的：

      1. 通过在运行 SQL Server 的服务器上（在 SSMS 或 SQLCMD.EXE 中）执行查询来排除网络问题。
      2. 通过转到查询选项 -> 结果 -> 网格并检查“执行后丢弃结果”选项来排除 SSMS 中的客户端问题。请注意，此选项将阻止所有输出，包括消息，但仍可用于排除 SSMS 为每行分配内存然后在网格中绘制它所花费的时间。
        或者，您可以通过 SQLCMD.EXE 执行查询并将输出定向到无处通过：-o NUL:。
    4. 是否有与此查询关联的等待类型？如果是，那等待类型是什么？

    5. 返回的列的实际数据大小是多少？如果“TOP 1000”行包含不成比例的大部分数据，则整个表中该列的平均大小并不重要。如果您想了解 TOP 1000 行，请查看这些行。请运行以下命令：XMLXML

      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;
    6. 确切的表架构。请提供完整 CREATE TABLE的声明，包括所有索引。
    7. 查询计划？那是你可以发布的东西吗？该信息可能不会改变任何东西，但最好知道它不会改变，而不是猜测它不会并且是错误的;-)
    8. 数据文件上是否存在物理/外部碎片？虽然这在这里可能不是一个很大的因素，但由于您使用的是“消费级 SATA”而不是 SSD 甚至超昂贵的 SATA，次优排序扇区的影响将更加明显，尤其是当这些扇区的数量需要阅读的内容增加了。

    9. 以下查询的确切结果是什么：

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

    更新

    我突然想到我应该尝试重现这种情况，看看我是否遇到类似的行为。所以，我创建了一个包含几列的表（类似于问题中的模糊描述），然后用 100 万行填充它，XML 列每行大约有 15k 数据（见下面的代码）。

    我发现SELECT TOP 1000 * FROM TABLE第一次在 8 秒内完成，之后每次 2-4 秒（是DBCC DROPCLEANBUFFERS的，在每次运行SELECT *查询之前执行）。而且我几年前的笔记本电脑并不快：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, 1, @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 ...', 10, 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, Col3, Col4 FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;

/*
Scan count 1, logical reads 21,    physical reads 1,     read-ahead reads 4436,
              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% 的表页是 LOB_DATA

    我的表有 100 万行，每行都有超过 15k 的 XML 数据，并sys.dm_db_index_physical_stats显示有 200 万个 LOB_DATA 页。剩下的 10% 将是 222k IN_ROW 数据页，但我只有其中的 11,630 个。所以再一次，我们需要更多关于实际表模式和实际数据的信息。

    • 11

  2. 我是否认为 LOB_DATA 页面会导致扫描速度缓慢，这不仅是因为它们的大小，还因为 SQL Server 无法有效地扫描聚集索引

    是的，读取未存储在行中的 LOB 数据会导致随机 IO 而不是顺序 IO。此处用于了解它为什么快或慢的磁盘性能指标是随机读取 IOPS。

    LOB 数据存储在树结构中，其中聚集索引中的数据页指向具有 LOB 根结构的 LOB 数据页，而 LOB 根结构又指向实际的 LOB 数据。SQL Server 在遍历聚集索引中的根节点时，只能通过顺序读取的方式获取行内数据。要获取 LOB 数据，SQL Server 必须转到磁盘上的其他位置。

    我想如果您更改为 SSD 磁盘，您不会因此受到太大影响，因为 SSD 的随机 IOPS 远高于旋转磁盘。

    拥有这样的表结构/数据模式是否合理？

    是的，它可能是。取决于这张桌子为你做了什么。

    通常，当您想要使用 T-SQL 查询 XML 时，SQL Server 中的 XML 性能问题会发生，当您想要在 where 子句或联接中的谓词中使用 XML 中的值时更是如此。如果是这种情况，您可以查看属性提升或选择性 XML 索引，或者重新设计表结构，将 XML 分解为表。

    我试过压缩

    十多年前，我在一个产品中做过一次，从那以后我就后悔了。我真的很怀念无法使用 T-SQL 处理数据，所以如果可以避免的话，我不会向任何人推荐。

    • 10

相关问题