优化SQL查询以避免哈希匹配(聚合)

Question

我有一个需要7个minutes+才能返回结果的SQL查询。我正在尝试尽可能多地优化，执行计划在散列匹配(聚合)上损失了82%的时间。我已经做了一些搜索，看起来使用"EXISTS“将有助于解析，但我还没有弄清楚查询的语法来使其工作。下面是查询：

select dbo.Server.Name,
                dbo.DiskSpace.Drive,
                AVG(dbo.DiskSpace.FreeSpace) as 'Free Disk Space',
                AVG(dbo.Processor.PercentUsed) as 'CPU % Used',
                AVG(dbo.Memory.PercentUtilized) as '% Mem Used'

                from Server
                join dbo.DiskSpace on dbo.Server.ID=DiskSpace.ServerID
                join dbo.Processor on dbo.Server.ID=Processor.ServerID
                join dbo.Memory on dbo.Server.ID=dbo.Memory.ServerID

                where
                dbo.Processor.ProcessorNum='_Total' 
                    and dbo.Processor.Datetm>DATEADD(DAY,-(1),(CONVERT (date, GETDATE()))) 
                    and (      dbo.Server.Name='qp-ratking' 
                            or dbo.Server.Name='qp-hyper2012' 
                            or dbo.Server.Name='qp-hyped' 
                            or dbo.Server.Name='qp-lichking')
                Group By dbo.server.name, Dbo.DiskSpace.Drive
                Order By Dbo.Server.Name, dbo.DiskSpace.Drive;

如何使用EXISTS减少/消除连接？或者，如果有更好的优化方法，我也准备好了。谢谢

Answer 1

一位同事分解了查询，并将数据分成较小的块，因此对连接返回的数据没有那么多的处理。它将返回时间缩短到不到1秒。新建查询：

WITH tempDiskSpace AS
(
SELECT dbo.Server.Name
      ,dbo.DiskSpace.Drive
      ,AVG(dbo.DiskSpace.FreeSpace) AS 'Free Disk Space'

FROM dbo.DiskSpace
      LEFT JOIN dbo.Server ON dbo.DiskSpace.ServerID=Server.ID

WHERE dbo.DiskSpace.Datetm>DATEADD(DAY,-(1),(CONVERT (date, GETDATE())))
AND (dbo.Server.Name='qp-ratking'
      OR dbo.Server.Name='qp-hyper2012'
      OR dbo.Server.Name='qp-hyped'
      OR dbo.Server.Name='qp-lichking')

GROUP BY Name, Drive
)
,tempProcessor
AS
(
SELECT dbo.Server.Name
      ,AVG(dbo.Processor.PercentUsed) AS 'CPU % Used'

FROM dbo.Processor
      LEFT JOIN dbo.Server ON dbo.Processor.ServerID=Server.ID

WHERE dbo.Processor.Datetm>DATEADD(DAY,-(1),(CONVERT (date, GETDATE())))
AND dbo.Processor.ProcessorNum='_Total'
AND (dbo.Server.Name='qp-ratking'
      OR dbo.Server.Name='qp-hyper2012'
      OR dbo.Server.Name='qp-hyped'
      OR dbo.Server.Name='qp-lichking')

GROUP BY Name
)
,tempMemory
AS
(
SELECT dbo.Server.Name
      ,AVG(dbo.Memory.PercentUtilized) as '% Mem Used'

FROM dbo.Memory
      LEFT JOIN dbo.Server ON dbo.Memory.ServerID=Server.ID

WHERE dbo.Memory.Datetm>DATEADD(DAY,-(1),(CONVERT (date, GETDATE())))
AND (dbo.Server.Name='qp-ratking'
      OR dbo.Server.Name='qp-hyper2012'
      OR dbo.Server.Name='qp-hyped'
      OR dbo.Server.Name='qp-lichking')

GROUP BY Name
)

SELECT tempDiskSpace.Name, tempDiskSpace.Drive, tempDiskSpace.[Free Disk Space], tempProcessor.[CPU % Used], tempMemory.[% Mem Used]
FROM tempDiskSpace
LEFT JOIN tempProcessor ON tempDiskSpace.Name=tempProcessor.Name
LEFT JOIN tempMemory ON tempDiskSpace.Name=tempMemory.Name
ORDER BY Name, Drive;

谢谢你所有的建议。

Answer 2

我会从检查索引开始。连接中使用的所有键是否都定义为primary keys？或者他们至少有索引吗？

然后，Processor和Server上的其他索引可能会有所帮助：

create index idx_Processor_ProcessorNum_Datetm_ServerId on ProcessorNum(ProcessorNum, Datetm, ServerId);
create index idx_Server_Name_ServerId on Server(Name, ServerId)

Answer 3

这条语句看起来结构合理，没有看到很大的优化空间，只要满足如下要求

1. 检查索引碎片并确保所有索引都得到维护
2. 检查如果统计数据是最新的
3. 如果脏的准备就绪是可接受的，则值得考虑在表上应用(NOLOCK)。
4. 如果查询允许声明变量，则将DATEADD移出筛选器语句，如下所示可能是有益的。

希望这能有所帮助。

-- Assuming Variables can be declared see the script below. 
-- I made a few changes per my coding standard only to help me read better.

声明@dt_Yesterdate日期

SET @dt_Yesterdate = DATEADD(DAY，-(1)，CONVERT (DATE，GETDATE()

SELECT s.Name,
       ds.Drive,
       AVG(ds.FreeSpace) AS 'Free Disk Space',
       AVG(P.PercentUsed) AS 'CPU % Used',
       AVG(m.PercentUtilized) AS '% Mem Used'
FROM Server s
     JOIN dbo.DiskSpace AS ds
         ON s.ID = ds.ServerID
     JOIN dbo.Processor AS p
         ON s.ID = p.ServerID
     JOIN dbo.Memory AS m
         ON s.ID = m.ServerID
WHERE P.ProcessorNum = '_Total'
  AND P.Datetm > @dt_Yesterdate
  AND s.Name IN ('qp-ratking', 'qp-hyper2012', 'qp-hyped','qp-lichking')
GROUP BY s.name, ds.Drive
ORDER BY s.Name, ds.Drive;