主页 / dba / 问题 / 227288
Accepted
Frederik Vanderhaegen
Asked: 2019-01-17 06:10:17 +0800 CST

由于 varchar(max)，将溢出排序到 tempdb

  • 772

在 32GB 的服务器上，我们运行 SQL Server 2014 SP2，最大内存为 25GB，我们有两个表，在这里您可以找到两个表的简化结构：

CREATE TABLE [dbo].[Settings](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceId] [int] NULL,
    [typeID] [int] NULL,
    [remark] [varchar](max) NULL,
    CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Resources](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceUID] [int] NULL,
 CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

具有以下非聚集索引：

CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[Resources]
(
    [resourceUID] ASC
)

CREATE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Settings]
(
    [resourceId] ASC,
    [typeID] ASC
)

数据库配置为compatibility level120。

当我运行此查询时，有溢出到tempdb. 这就是我执行查询的方式：

exec sp_executesql N'
select r.id,remark
FROM Resources r
inner join Settings on resourceid=r.id
where resourceUID=@UID
ORDER BY typeID',
N'@UID int',
@UID=38

如果不选择该[remark]字段，则不会发生溢出。我的第一反应是，溢出是由于嵌套循环运算符上的估计行数少所致。

所以我将 5 个日期时间和 5 个整数列添加到设置表并将它们添加到我的选择语句中。当我执行查询时，没有发生溢出。

为什么溢出仅在[remark]选择时发生？这可能与这是一个varchar(max). 我该怎么做才能避免溢出到tempdb？

添加OPTION (RECOMPILE)到查询没有区别。

sql-server performance

3 个回答

  1. Best Answer

    这里将有几种可能的解决方法。

    您可以手动调整内存授予，但我可能不会走那条路。

    在获取最大长度列之前，您还可以使用 CTE 和 TOP 降低排序。它看起来像下面这样。

    WITH CTE AS (
SELECT TOP 1000000000 r.ID, s.ID AS ID2, s.typeID
FROM Resources r
inner join Settings s on resourceid=r.id
where resourceUID=@UID
ORDER BY s.typeID
)
SELECT c.ID, ca.remark
FROM CTE c
CROSS APPLY (SELECT remark FROM dbo.Settings s WHERE s.id = c.ID2) ca(remark)
ORDER BY c.typeID

    概念验证 dbfiddle在这里。样本数据仍然会受到赞赏！

    如果您想阅读 Paul White 的精彩分析，请阅读此处。

    • 10

  2. 为什么只有在选择 [remark] 时才会发生溢出？

    当您包含该列时会发生溢出，因为您没有为正在排序的大型字符串数据获得足够大的内存授予。

    您没有获得足够大的内存授予，因为实际行数比估计行数多 10 倍（实际行数 1,302 对估计行数 126）。

    为什么估值偏了？为什么 SQL Server 认为 dbo.Settings 中只有一行的 aresourceid为 38？

    这可能是一个统计问题，您可以通过运行来检查DBCC SHOW_STATISTICS('dbo.Settings', 'IX_Test')并查看该直方图步骤的计数。但执行计划似乎表明统计数据尽可能完整和最新。

    由于统计数据没有帮助，您最好的选择可能是查询重写 - Forrest在他的回答中已经涵盖了这一点。

    • 7

  3. 在我看来，where查询中的子句给出了问题，并且是低估计的原因，即使OPTION(RECOMPILE)使用了也是如此。

    我创建了一些测试数据，最后提出了两个解决方案，将ID字段存储resources在变量（如果它始终是唯一的）或临时表中，如果我们可以有多个ID。

    基地测试记录

    SET NOCOUNT ON
DECLARE @i int= 1;
WHILE @i <= 10000
BEGIN
INSERT INTO [dbo].[Settings]([resourceId],[typeID],remark)
VALUES(@i,@i,'KEPT THESE VALUES OUT BECAUSE IT WOULD CLUTTER THE EXAMPLES, VALUES OVER 8000 Chars entered here'); -- 23254 character length on each value
INSERT INTO  [dbo].[Resources](resourceUID)
VALUES(@i);
SET @i += 1;
END

    插入“Seek”值，以获得与 OP 相同的近似结果集（1300 条记录）

    INSERT INTO  [dbo].[Settings]([resourceId],[typeID],remark)
VALUES(38,38,'KEPT THESE VALUES OUT BECAUSE IT WOULD CLUTTER THE EXAMPLES, VALUES OVER 8000 Chars entered here')
GO 1300

    更改兼容性和更新统计信息以匹配 OP

    ALTER DATABASE StackOverflow SET COMPATIBILITY_LEVEL = 120;
UPDATE STATISTICS settings WITH FULLSCAN;
UPDATE STATISTICS resources WITH FULLSCAN;

    原始查询

    exec sp_executesql N'
select r.id
FROM Resources r
inner join Settings on resourceid=r.id
where resourceUID=@UID
ORDER BY typeID',
N'@UID int',
@UID=38

    我的估计更糟，估计有一行，同时返回了 1300。就像 OP 所说的那样，我添加也没关系OPTION(RECOMPILE)

    需要注意的重要一点是，当我们去掉 where 子句时，估计是 100% 正确的，这是预期的，因为我们使用了两个表中的所有数据。

    我强制索引只是为了确保我们使用与先前查询中相同的索引，以证明这一点

    exec sp_executesql N'
select r.id,remark
FROM Resources r with(index([IX_UID]))
inner join Settings WITH(INDEX([IX_Test])) 
on resourceid=r.id
ORDER BY typeID',
N'@UID int',
@UID=38

    不出所料，不错的估计。

    那么，我们可以改变什么以获得更好的估计但仍然寻求我们的价值？

    如果 @UID 是唯一的，就像 OP 给出的示例一样，我们可以将id返回的单个resources变量放在一个变量中，然后使用 OPTION(RECOMPILE) 在该变量上查找

    DECLARE @UID int =38 , @RID int;
SELECT @RID=r.id from 
Resources r where resourceUID = @UID;

SELECT @uid, remark 
from Settings 
where resourceId = @uid 
Order by typeID
OPTION(RECOMPILE);

    这给出了 100% 准确的估计

    但是如果资源中有多个resourceUID怎么办？

    添加一些测试数据

    INSERT INTO Resources(ResourceUID)
VALUES (38);
go 50

    这可以通过临时表解决

    CREATE TABLE #RID (id int)
DECLARE @UID int =38 
INSERT INTO #RID
SELECT r.id 
from 
Resources r where resourceUID = @UID

SELECT @uid, remark 
from Settings  s
INNER JOIN #RID r
ON r.id =s.resourceId
Order by typeID
OPTION(RECOMPILE)

DROP TABLE #RID

    再次用准确的估计。

    这是用我自己的数据集 YMMV 完成的。

    用sp_executesql写的

    有一个变量

    exec sp_executesql N'
DECLARE  @RID int;
    SELECT @RID=r.id from 
    Resources r where resourceUID = @UID;

    SELECT @uid, remark 
    from Settings 
    where resourceId = @uid 
    Order by typeID
    OPTION(RECOMPILE);',
N'@UID int',
@UID=38

    带临时表

    exec sp_executesql N'

CREATE TABLE #RID (id int)

INSERT INTO #RID
SELECT r.id 
from 
Resources r where resourceUID = @UID

SELECT @uid, remark 
from Settings  s
INNER JOIN #RID r
ON r.id =s.resourceId
Order by typeID
OPTION(RECOMPILE)

DROP TABLE #RID',
N'@UID int',
@UID=38

    我的测试仍然 100% 正确估计

    • 4

相关问题