Get best of the week

varchar（max）-varchar（max）和生产中

我最近参加了有关在nvarchar列中指定长度对性能的影响的讨论。双方的论点都是合理的，并且由于我有空闲时间，所以我决定进行一些测试。结果就是这个帖子。

剧透-不是那么简单。

所有测试均在SQL Server 2014 Developer Edition上进行，在SQL Server 2016上获得的结果大致相同（略有不同）。以下内容应与SQL Server 2005-2016相关（并且必须在2017/2019年进行测试，因为那里出现了Adaptive Memory Grants，这可以在某种程度上纠正这种情况）。

我们需要来自Erik Darling sp_pressure_detector的存储过程，该存储过程使您可以获取有关系统当前状态的大量信息，以及SQL Query Stress是一个非常酷的开源实用程序Adam Machanic / Erik Ejlskov Jensen，用于负载测试MS SQL Server。

我们在谈论什么呢


我要回答的问题是（n）varchar字段长度的选择是否会影响性能（此后只是varchar，尽管所有内容都与nvarchar相关），或者您可以使用varchar（max）而不是Steam，因为如果字符串长度<8000 （对于nvarchar为4000）字符，然后将varchar（max）和varchar（N）存储在行中。

烹饪架


create table ##v10  (i int, d datetime, v varchar(10));
create table ##v100 (i int, d datetime, v varchar(100));
create table ##vmax (i int, d datetime, v varchar(max));

我们创建了三个表，每个表包含三个字段，不同之处仅在于varchar的长度：10/100 / max。并用相同的数据填充它们：

;with x as (select 1 x union all select 1)
, xx as (select 1 x from x x1, x x2)
, xxx as (select 1 x from xx x1, xx x2, xx x3)
, xxxx as (
	select row_number() over(order by (select null)) i
		, dateadd(second, row_number() over(order by (select null)), '20200101') d
		, cast (row_number() over(order by (select null)) as varchar(10))  v 		
	from xxx x1, xxx x2, xxx x3
) --262144 
insert into ##v10			--varchar(10)
select i, d, v from xxxx;	

insert into ##v100			--varchar(100)
select i, d, v from ##v10;

insert into ##vmax			--varchar(max)
select i, d, v from ##v10;

结果，每个表将包含262144行。第一列（整数）包含从1到262145的非重复数字；d（日期时间）唯一的日期和v（varchar）-强制转换（我为varchar（10））。为了使其更接近真实生活，请在i上创建一个唯一的群集索引：

create unique clustered index #cidx10 on ##v10(i);
create unique clustered index #cidx100 on ##v100(i);
create unique clustered index #cidxmax on ##vmax(i);

走


首先，让我们看一下执行不同请求的计划。

首先，检查由varchar选择的字段不取决于其长度（如果在其中存储了<8000个字符）。我们包括一个有效的执行计划，并查看：

select * from ##v10 where v = '123';
select * from ##v100 where v = '123';
select * from ##vmax where v = '123';


奇怪的是，差异虽然很小，但仍然存在。带有varchar（max）的查询计划首先选择所有行，然后将其过滤掉，并且varchar（10）和varchar（100）在扫描聚簇索引时检查是否匹配。因此，扫描耗时几乎是原来的三倍-0.068秒，而varchar（10）则为0.022秒。

现在让我们看看如果仅显示varchar列并通过集群索引键选择数据会发生什么：

select * from ##v10  where i between 200000 and 201000;
select * from ##v100 where i between 200000 and 201000;
select * from ##vmax where i between 200000 and 201000;


这里的一切都很清楚-这些请求没有区别。

现在开始有趣的部分。在上一个请求中，我们只有1001行，现在我们要按非索引列对它们进行排序。我们尝试：

select * from ##v10  where i between 200000 and 201000 order by d;
select * from ##v100 where i between 200000 and 201000 order by d;
select * from ##vmax where i between 200000 and 201000 order by d;


哦，那是什么黄色？


好笑，即 该请求请求并接收了6.5 MB的RAM用于排序，并且仅使用了96 KB。如果有更多行，情况会变得更糟。好吧，让它不是1000，而是100000：


但是，这更加严重。此外，使用最小varchar（10）的第一个请求也对以下内容不满意：


左侧有最后一个请求的警告：已请求500兆字节，而仅使用9.5兆字节。右边是排序警告：请求了8840 KB，但还不够，另外还写入了360页（每个8 kb）并从tempdb中读取。

这里的问题是：WTF？

答案是SQL Server查询优化器如何工作。要对某些内容进行排序，您首先需要将某些内容存储到内存中。如何了解需要多少内存？通常，我们知道哪种类型的数据会占用多少空间。但是可变长度字符串呢？但是对他们来说更有趣。在为/哈希联接排序分配内存时，SQL Server认为它们平均已满。并为其分配内存为（大小/ 2）*预期的行数。但是varchar（max）最多可以存储2GB-要分配多少？ SQL Server认为将有varchar（8000）的一半-即每行大约4 kb。

有趣的是-这种内存分配不仅会导致varchar（max）出现问题，而且-如果精心选择了varchars的大小，使它们中的大多数都变满了一半，那么这也会导致问题。计划不同的问题，但同样严重。上图中有一个描述-SQL Server无法正确分配内存以对小的varchar进行排序，并使用tempdb来存储中间结果。如果tempdb位于慢速磁盘上，或者被其他请求主动使用，则这可能会成为瓶颈。

SQL查询压力


现在，让我们看看执行批量查询时会发生什么。运行SQL查询压力，将其连接到我们的服务器，并说要在50个线程中执行所有这些查询10次。

第一个查询的结果：




有趣的是，但没有索引，在搜索时，varchar（max）表现得比任何人都差，并且就迭代和整体运行时的处理器时间而言，它的确差得多。

sp_pressure_detector在这里没有显示任何有趣的内容，因此我不引用其输出。
第二个查询的结果：




一切都在这里-同样好。

现在开始有趣的部分。一个查询，对结果的千行进行排序：




事实证明，所有内容都与上一个请求完全相同-行不多，排序不会引起问题。

现在，最后一个查询对许多行进行了不合理的排序（我在其中添加了前1000个，以免拉出整个排序后的列表）：




这是sp_pressure_detector的输出：



他告诉我们什么？所有会话每个请求489 MB（用于排序），但其中只有22个具有足够的内存用于SQL Server，即使考虑到这22个会话每个仅使用9 MB！
总共有11 GB的可用内存，每个489.625分配了229个会话，而SQL Server只有258 MB的可用空间，他们还希望获得489个新的会话。我该怎么办？等到内存可用后再等待-他们等待，甚至没有开始运行。如果在会话中发出此类请求，用户将怎么办？太等了。

顺便说一句，请注意使用varchar（10）的图形-使用varchar（10）的请求要比使用varchar（100）的请求花费更长的时间-而且尽管我的tempdb在非常快的磁盘上，但事实如此。tempdb下的驱动器越差，查询运行就越慢。

SQL Server 2012/2014的单独说明
SQL Server 2012/2014 sort spills. char/nchar – spill’ tempdb. MS , , .

:

create table ##c6  (i int, d datetime, v char(6));
insert into ##c6 (i, d, v)
select i, d, v
from ##v10
select * from ##c6 where i between 100000 and 200000 order by d;


( ):

DBCC TRACEON (7470, -1);


, spill’ .

发现


在具有（n）个varchar列的查询中进行排序时要格外小心。如果仍然需要排序，则非常希望排序列具有索引。

请注意，为了获得排序，没有必要显式使用order by-例如，合并合并也可以使用order by。散列连接也可能出现与内存分配相同的问题，例如，使用varchar（max）：

select top 100 * 
from ##vmax v1
inner hash join ##v10 v2 on v1.i = v2.i


分配了2.5 GB的内存，已使用25 MB！

对我来说，主要结论是：（n）varchar列的大小-重要！如果大小太小，则可能会在tempdb中溢出；如果大小太大，则内存请求太大。如果存在排序，明智的做法是将varchar长度声明为平均记录长度* 2，对于SQL Server 2012/2014，甚至还要更多。

对我来说，出乎意料的结论是：varchar（max）包含少于8000个字符，实际上运行起来较慢，并且带有过滤器。我尚不知道如何解释-我会做更多说明。

为我提取奖金：几乎已经单击“发布”，我认为即使使用varchar（max），您也可以遇到“小varchar'a”问题。实际上，当存储在varchar（最大）中超过4000个字符（nvarchar为2000个字符）时，排序可能会成为问题。

insert into ##vmax(i, d, v)
select i, d, replicate('a', 4000) v
from ##v10;

select * from ##vmax where i between 200000 and 201000 order by d;



truncate table ##vmax;

insert into ##vmax(i, d, v)
select i, d, replicate('a', 4100) v
from ##v10;

select * from ##vmax where i between 200000 and 201000 order by d;


为什么我一开始就写道并非所有的事情都这么简单？因为，例如，在我的带有半死磁盘的家用笔记本电脑上，当对“小型” varchar进行排序时，tempdb溢出，导致这样的事实：与使用varchar（max）的类似请求相比，此类请求的执行速度较慢。如果您具有良好的硬件，它们可能不是问题，但您不应忘记它们。

更有意思的是，看看是否由于其他DBMS中的varchars太大/太小而出现任何问题。如果您有机会查看-我很高兴与您分享。

小奖金


不幸的是，使用查询计划缓存无法捕获此类问题。以下是缓存中计划的示例：,，其中没有警告。


More articles:


All Articles