sql-server – LOB_DATA,慢速表扫描和一些I / O问题

我有一个相当大的表,其中一列是 XML数据,XML条目的平均大小约为15千字节.所有其他列都是常规的int,bigints,GUID等.为了得到一些具体的数字,假设该表有一百万行,大小约为15 GB.

我注意到,如果我想选择所有列,那么这个表选择数据的速度很慢.当我做

SELECT TOP 1000 * FROM TABLE

从磁盘读取数据大约需要20-25秒 – 即使我没有对结果进行任何排序.
我使用冷缓存运行查询(即在DBCC DROPCLEANBUFFERS之后).这是IO统计结果：

Scan count 1,logical reads 364,physical reads 24,read-ahead reads
7191,lob logical reads 7924,lob physical reads 1690,lob read-ahead
reads 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是高度冗余的,我可以(在c#app中)将XML从20KB压缩到~2.5KB并将其存储在VARBINARY列中,从而防止使用LOB数据页.这在我的测试中加速了两倍.

解决方法

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：.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!