在下面的帖子中,JD 提出我有一个性能不佳的查询。
我在 SQL Server 2019 标准版上运行此查询(查询计划是在开发版中生成的)
让我们在这里看一下:
INSERT INTO [dbo].[tbl_Planning_Operational_Data_Exploded] (
[ScenarioID]
,[CompanyID]
,[OperationalAccountID]
,[CurrencyID]
,[CustomerID]
,[ItemID]
,[CalendarDate]
,[Amt]
,[PlanningOperationalDataActualTransactionAttributeValueExplodedID]
)
SELECT ats.[ScenarioID]
,pode.[CompanyID]
,pode.[OperationalAccountID]
,pode.[CurrencyID]
,pode.[CustomerID]
,pode.[ItemID]
,pode.[CalendarDate]
,SUM(pode.[Amt]) AS Amt
,'00000000-0000-0000-0000-000000000000' AS [PlanningOperationalDataActualTransactionAttributeValueExplodedID]
FROM #ActualThroughScenarios ats WITH (NOLOCK) --Mini 100 records
INNER JOIN [dbo].[tbl_Core_Scenarios] cs WITH (NOLOCK) ON cs.ScenarioID = ats.ScenarioID --Mini 100 records
AND cs.ScenarioTypeID IN (
2
,3
)
INNER JOIN [dbo].[tbl_Core_Scenarios] csActuals WITH (NOLOCK) ON csActuals.FiscalYear = cs.FiscalYear --Mini 100 records
AND csActuals.ScenarioTypeID = 1
INNER JOIN [dbo].[tbl_Planning_Operational_Data_Exploded] pode ON pode.ScenarioID = csActuals.ScenarioID -- Huge up to 300 million records
INNER JOIN [dbo].[tbl_Core_Fiscal_Date] cfd WITH (NOLOCK) ON pode.CalendarDate = cfd.CalendarDate --Mini 1000 records
WHERE cfd.FiscalPeriod <= cs.ActualsThrough
AND cs.ActualsThrough > 0
GROUP BY ats.[ScenarioID]
,pode.[CompanyID]
,pode.[OperationalAccountID]
,pode.[CurrencyID]
,pode.[CustomerID]
,pode.[ItemID]
,pode.[CalendarDate]
查询计划生成:https://www.brentozar.com/pastetheplan/? id=Sk69AQ-As
基本上这个查询非常简单,我有一个非常大的表“Exploded”,我需要对其进行分段,对它们进行分组并修改“ScenarioID”,然后将它们重新插入到同一个表中。
我可以优化或转移所有小表的索引策略,但在数据库的其他部分(未显示有很多)向“Exploded”表添加索引非常昂贵,我宁愿不添加任何额外的索引那张桌子
正如在上面的链接帖子中提到的,这个查询可以运行可以运行得非常慢,因为它会生成一个非常大的哈希匹配,我认为这是通过这样做的组,但我需要组依据,那个和求和是什么的关键部分我在这里做:
这会溢出到 TempDB 并受 TempDB 速度的严重影响。鉴于我对相关表的约束,有什么方法可以改进上面的查询吗?
这里的目标是获取您想要在临时表中暂存的数据,然后将其转储到永久表中。
部分问题是您在插入表的同时还在从中提取数据的查询中引用它。处理多行时,完全并行的计划通常比在单个线程上执行所有操作更理想。由于并行交换的放置等原因,您可能在这里没有得到一个。
尽管基数估计在大部分计划中都令人沮丧,但它的估计子树成本应该足够高以符合并行计划的条件,但您没有得到一个。我们没有得到串行计划的明确原因,因此我们可以假设该决定是基于成本的。
我认为您根本不需要更改索引,但您应该尝试一些事情。这是我会尝试的,下面有评论。
如果您使用的是 Enterprise/Developer Edition,我会尝试涉及批处理模式执行,因为它往往更适合大型聚合。不过,它在标准版中严重受限,并行计划中有两个线程的限制。如果那是您正在使用的,那么限制可能会适得其反。
查看旧版 Cardinality Estimator 是否改进了连接之间的行估计。这可能足以不需要其他提示,但您必须在本地进行测试。
通过未记录的跟踪标志 8649 强制执行并行执行计划。您可能不需要先将查询转储到 #temp 表中,但这里是为了完整性。如果您没有调整跟踪标志的权限,您可以将此提示替换为
USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')
.使用连接提示来避免在并行执行计划中获得 Merge Join,因为并行 Merge Join 是一个不可原谅的错误。
为查询请求此服务器上可用的全部内存授权。如果最大授权远远超过最终查询计划的需求,您可以将其降低到较低的百分比。我不知道其他规定的更改会带来什么,但它看起来肯定比当前查询计划中的溢出要高。
最后一点,不清楚为什么在 SQL Server 2019 上启用跟踪标志 1117 和 1118,因为它们的行为从 SQL Server 2016 开始成为默认行为。