过多的排序内存授予

数据库 | 2021-01-05 | 编程黑洞网 | 0条评论 | 677 人阅读

为什么这个简单的查询会被授予这么多的内存？ >

#1 楼

这是SQL Server中的一个错误（从2008年到2014年）。

我的错误报告在这里。谓词，但为该排序授予的内存是根据预过滤器基数估计错误地计算的。被下推到扫描运算符中。现在，授予排序的内存是基于筛选器输出的估计基数，而不是基于扫描的正确基数：

SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T 
WHERE 
    T.FilterMe = 567
ORDER BY 
    T.SortMe
OPTION (QUERYTRACEON 9130); -- Not for production systems!

在生产系统中，将需要采取步骤来避免计划形状出现问题（将过滤器推入扫描中，另一列进行排序）。一种方法是在过滤条件上提供索引和/或提供所需的排序顺序。

授予的排序只有928KB：在以下SQL Server x64 Developer Edition版本上测试并确认错误： -override“>

2014   : 12.00.2430 (RTM CU4)
2012   : 11.00.5556 (SP2 CU3)
2008R2 : 10.50.6000 (SP3)
2008   : 10.00.6000 (SP4)

此问题已在SQL Server 2016 Service Pack 1中修复。发行说明包括以下内容：

VSTS错误号8024987
具有下推谓词的表扫描和索引扫描往往高估了对父运算符的内存授予。
Microsoft SQL Server 2016 (SP1) - 13.0.4001.0 (X64) Developer Edition
Microsoft SQL Server 2014 (SP2-CU3) 12.0.5538.0 (X64) Developer Edition

两种CE型号。

#2 楼

从SQL 2012开始，您可能会发现SerialRequiredMemory与SerialDesiredMemory之间存在较大差异，例如：
该查询有点粗糙且可以使用，但是确实从我的SQL Server 2014开发人员框中获取了过多的排序查询，比率为975.47，还有其他一些令人大跌眼镜的计划。 “正常”比率（至少从我的有限测试中得出）似乎是〜1。

HTH

#3 楼

感谢您的所有帮助。我以为我会发送上述查询的更新版本，认为对我们有用。

-- Search plan cache for Memory Grant issues
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

-- Collect more info about the plan here if required, eg usecounts, objtype etc, 
SELECT IDENTITY( INT, 1, 1 ) rowId, query_plan, db = DB_NAME(CAST(pa.value AS int))
INTO #tmp
FROM sys.dm_exec_cached_plans cp WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
    OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa 
    WHERE pa.attribute = 'dbid' 
GO

;WITH cte AS
(
SELECT
    rowId,
    query_plan,
    m.c.value ('@SerialRequiredMemory', 'INT' ) AS SerialRequiredMemory,
    m.c.value ('@SerialDesiredMemory', 'INT' ) AS SerialDesiredMemory,
    db
FROM #tmp t
    CROSS APPLY t.query_plan.nodes ( '//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]' ) m(c)
), cte2 AS (
SELECT *,
    CAST( CAST( SerialDesiredMemory AS DECIMAL(10,2) ) / CAST( SerialRequiredMemory AS DECIMAL(10,2) ) AS DECIMAL(10,2) ) Desired_to_Required_ratio
FROM cte
)
SELECT TOP 20
    rowId,
    query_plan,
    SerialRequiredMemory SerialRequiredMemory_KB,
    SerialDesiredMemory SerialDesiredMemory_KB,
    CAST( SerialRequiredMemory / 1024. AS DECIMAL(10,2) ) SerialRequiredMemory_MB,
    CAST( SerialDesiredMemory / 1024. AS DECIMAL(10,2) ) SerialDesiredMemory_MB,
    Desired_to_Required_ratio,
    db
FROM cte2
WHERE Desired_to_Required_ratio > 100
ORDER BY Desired_to_Required_ratio DESC