我在 SQL Server 2016 EE 实例上有一个 550GB 的相对较大的数据库,在操作系统可用的总 128GB RAM 中,最大内存限制为 112GB。该数据库的最新兼容性级别为 130。开发人员抱怨以下查询在单独执行时会在 30 秒的可接受时间内执行,但当他们大规模运行进程时,相同的查询会同时执行多次跨多个线程,这是他们观察到执行时间受到影响并且性能/吞吐量下降的时候。有问题的 T-SQL 是:
select distinct dg.entityId, et.EntityName, dg.Version
from DataGathering dg with(nolock)
inner join entity e with(nolock)
on e.EntityId = dg.EntityId
inner join entitytype et with(nolock)
on et.EntityTypeID = e.EntityTypeID
and et.EntityName = 'Account_Third_Party_Details'
inner join entitymapping em with(nolock)
on em.ChildEntityId = dg.EntityId
and em.ParentEntityId = -1
where dg.EntityId = dg.RootId
union all
select distinct dg1.EntityId, et.EntityName, dg1.version
from datagathering dg1 with(nolock)
inner join entity e with(nolock)
on e.EntityId = dg1.EntityId
inner join entitytype et with(nolock)
on et.EntityTypeID = e.EntityTypeID
and et.EntityName = 'TIN_Details'
where dg1.EntityId = dg1.RootId
and dg1.EntityId not in (
select distinct ChildEntityId
from entitymapping
where ChildEntityId = dg1.EntityId
and ParentEntityId = -1)
实际执行计划显示以下内存授予警告:
图形执行计划可以在这里找到:
https://www.brentozar.com/pastetheplan/?id=r18ZtCidN
以下是此查询涉及的表的行数和大小。最昂贵的运算符是对 DataGathering 表上的非聚集索引的索引扫描,考虑到与其他表相比的大小,这是有意义的。我理解为什么/如何需要内存授予,我认为这是由于查询的编写方式需要多个排序和哈希运算符。我需要的建议/指导是如何避免内存授予、T-SQL 和重构代码不是我的强项,有没有办法重写这个查询以提高性能?如果我可以将查询调整为单独运行得更快,那么希望这些好处会转移到大规模运行时,也就是性能开始受到影响的时候。很高兴提供更多信息,并希望从中学到一些东西!
更新 3 个表的统计信息后:
UPDATE STATISTICS Entity WITH FULLSCAN;
UPDATE STATISTICS EntityMapping WITH FULLSCAN;
UPDATE STATISTICS EntityType WITH FULLSCAN;
...执行计划改进了一些:
https://www.brentozar.com/pastetheplan/?id=rkVmdkh_4
不幸的是,“过度授权”警告仍然存在。
Josh Darnell 友好地建议将查询重新分解为以下内容,以避免他在某个运算符上发现的并行性受到抑制。重构查询引发错误“消息 4104,级别 16,状态 1,第 7 行无法绑定多部分标识符“et.EntityName”。” 我该如何解决这个问题?
DECLARE @tinDetailsId int;
SELECT @tinDetailsId = et.EntityTypeID
FROM entitytype et
WHERE et.EntityName = 'TIN_Details';
select distinct dg1.EntityId, et.EntityName, dg1.version
from datagathering dg1 with(nolock)
inner join entity e with(nolock)
on e.EntityId = dg1.EntityId
where dg1.EntityId = dg1.RootId
and e.EntityTypeID = @tinDetailsId
and dg1.EntityId not in (
select distinct ChildEntityId
from entitymapping
where ChildEntityId = dg1.EntityId
and ParentEntityId = -1)
UNION ALL
select distinct dg.entityId, et.EntityName, dg.Version
from DataGathering dg with(nolock)
inner join entity e with(nolock)
on e.EntityId = dg.EntityId
inner join entitytype et with(nolock)
on et.EntityTypeID = e.EntityTypeID
and et.EntityName = 'Account_Third_Party_Details'
inner join entitymapping em with(nolock)
on em.ChildEntityId = dg.EntityId
and em.ParentEntityId = -1
where dg.EntityId = dg.RootId
这可能对内存授予情况没有帮助(希望额外的统计信息更新会对此有所帮助),但我注意到此查询中禁止了并行性。查看计划的这一部分:
由于嵌套循环连接的外侧只有一行,因此所有 900k 行都集中到一个线程上。所以尽管这个查询在 DOP 8 运行,这部分计划是完全串行的。这包括排序。这是该类型的 XML:
如果可能的话,请考虑避免连接到 EntityType,而只是抓住该 Id 并用它过滤 Entity 表。这将允许它只是对实体表的索引扫描的谓词,希望允许并行性并加快执行速度。
像这样的东西:
然后您可以在查询的下半部分引用它,从而消除连接:
您可能希望对
EntityName
查询顶部的“Account_Third_Party_Details”做同样的事情,因为它有同样的问题 - 行数是原来的两倍。PS:与手头的主题完全无关,我注意到你
nolock
对这个查询中的所有表都有提示。确保您了解这一点的含义。查看有关该主题的漂亮博客文章:坏习惯: Aaron Bertrand
将NOLOCK 无处不在