查询性能调优

寻求帮助以提高此查询性能。

SQL Server 2008 R2 Enterprise，最大 RAM 16 GB，CPU 40，最大并行度 4。

SELECT DsJobStat.JobName AS JobName
    , AJF.ApplGroup AS GroupName
    , DsJobStat.JobStatus AS JobStatus
    , AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) AS ElapsedSecAVG
    , AVG(CAST(DsJobStat.CpuMSec AS FLOAT)) AS CpuMSecAVG 
FROM DsJobStat, AJF 
WHERE DsJobStat.NumericOrderNo=AJF.OrderNo 
AND DsJobStat.Odate=AJF.Odate 
AND DsJobStat.JobName NOT IN( SELECT [DsAvg].JobName FROM [DsAvg] )         
GROUP BY DsJobStat.JobName
, AJF.ApplGroup
, DsJobStat.JobStatus
HAVING AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) <> 0;

执行消息，

(0 row(s) affected)
Table 'AJF'. Scan count 11, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DsAvg'. Scan count 2, logical reads 1926, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DsJobStat'. Scan count 1, logical reads 3831235, physical reads 85, read-ahead reads 3724396, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
      CPU time = 67268 ms,  elapsed time = 90206 ms.

表的结构：

-- 212271023 rows
CREATE TABLE [dbo].[DsJobStat](
    [OrderID] [nvarchar](8) NOT NULL,
    [JobNo] [int] NOT NULL,
    [Odate] [datetime] NOT NULL,
    [TaskType] [nvarchar](255) NULL,
    [JobName] [nvarchar](255) NOT NULL,
    [StartTime] [datetime] NULL,
    [EndTime] [datetime] NULL,
    [NodeID] [nvarchar](255) NULL,
    [GroupName] [nvarchar](255) NULL,
    [CompStat] [int] NULL,
    [RerunCounter] [int] NOT NULL,
    [JobStatus] [nvarchar](255) NULL,
    [CpuMSec] [int] NULL,
    [ElapsedSec] [int] NULL,
    [StatusReason] [nvarchar](255) NULL,
    [NumericOrderNo] [int] NULL,
CONSTRAINT [PK_DsJobStat] PRIMARY KEY CLUSTERED 
(   [OrderID] ASC,
    [JobNo] ASC,
    [Odate] ASC,
    [JobName] ASC,
    [RerunCounter] ASC
));

-- 48992126 rows
CREATE TABLE [dbo].[AJF](  
    [JobName] [nvarchar](255) NOT NULL,
    [JobNo] [int] NOT NULL,
    [OrderNo] [int] NOT NULL,
    [Odate] [datetime] NOT NULL,
    [SchedTab] [nvarchar](255) NULL,
    [Application] [nvarchar](255) NULL,
    [ApplGroup] [nvarchar](255) NULL,
    [GroupName] [nvarchar](255) NULL,
    [NodeID] [nvarchar](255) NULL,
    [Memlib] [nvarchar](255) NULL,
    [Memname] [nvarchar](255) NULL,
    [CreationTime] [datetime] NULL,
CONSTRAINT [AJF$PrimaryKey] PRIMARY KEY CLUSTERED 
(   [JobName] ASC,
    [JobNo] ASC,
    [OrderNo] ASC,
    [Odate] ASC
));

-- 413176 rows
CREATE TABLE [dbo].[DsAvg](
    [JobName] [nvarchar](255) NULL,
    [GroupName] [nvarchar](255) NULL,
    [JobStatus] [nvarchar](255) NULL,
    [ElapsedSecAVG] [float] NULL,
    [CpuMSecAVG] [float] NULL
);

CREATE NONCLUSTERED INDEX [DJS_Dashboard_2] ON [dbo].[DsJobStat] 
(   [JobName] ASC,
    [Odate] ASC,
    [StartTime] ASC,
    [EndTime] ASC
)
INCLUDE ( [OrderID],
[JobNo],
[NodeID],
[GroupName],
[JobStatus],
[CpuMSec],
[ElapsedSec],
[NumericOrderNo]) ;

CREATE NONCLUSTERED INDEX [Idx_Dashboard_AJF] ON [dbo].[AJF] 
(   [OrderNo] ASC,
[Odate] ASC
)
INCLUDE ( [SchedTab],
[Application],
[ApplGroup]) ;

CREATE NONCLUSTERED INDEX [DsAvg$JobName] ON [dbo].[DsAvg] 
(   [JobName] ASC
)

执行计划：

https://www.brentozar.com/pastetheplan/?id=rkUVhMlXM

得到答复后更新

非常感谢@Joe Obbish

您对这个关于 DsJobStat 和 DsAvg 之间的查询的问题是正确的。关于如何加入而不是使用 NOT IN 的内容并不多。

确实有你猜到的桌子。

CREATE TABLE [dbo].[DSJobNames](
    [JobName] [nvarchar](255) NOT NULL,
 CONSTRAINT [DSJobNames$PrimaryKey] PRIMARY KEY CLUSTERED 
(   [JobName] ASC
) ); 

我试过你的建议，

SELECT DsJobStat.JobName AS JobName
, AJF.ApplGroup AS GroupName
, DsJobStat.JobStatus AS JobStatus
, AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) AS ElapsedSecAVG
, Avg(CAST(DsJobStat.CpuMSec AS FLOAT)) AS CpuMSecAVG 
FROM DsJobStat
INNER JOIN DSJobNames jn
    ON jn.[JobName]= DsJobStat.[JobName]
INNER JOIN AJF 
    ON DsJobStat.Odate=AJF.Odate 
    AND DsJobStat.NumericOrderNo=AJF.OrderNo 
WHERE NOT EXISTS ( SELECT 1 FROM [DsAvg] WHERE jn.JobName =  [DsAvg].JobName )      
GROUP BY DsJobStat.JobName, AJF.ApplGroup, DsJobStat.JobStatus
HAVING AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) <> 0;   

执行讯息：

(0 row(s) affected)
Table 'DSJobNames'. Scan count 5, logical reads 1244, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DsAvg'. Scan count 5, logical reads 2129, physical reads 0, read-ahead reads 24, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DsJobStat'. Scan count 8, logical reads 84, physical reads 0, read-ahead reads 83, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AJF'. Scan count 5, logical reads 757999, physical reads 944, read-ahead reads 757311, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 21776 ms,  elapsed time = 33984 ms.

执行计划：https ://www.brentozar.com/pastetheplan/?id=rJVkLSZ7f