提高 CTE 性能

有沒有辦法提高遞歸 CTE 的性能，如下所示，我不確定當連接使用 ROW_NUMBER 時是否可以在連接上添加索引？

DECLARE @File_Name VARCHAR(8000),
       @Disk VARCHAR(5)
SET @File_Name = 'MARSQLUTILITY,AdventureWorksDW_Data'
SET @Disk = 'I:'
--Code to pull out deltas between collected IO stats.
;WITH IOPS   ([IO_STALL]
          ,[IO_STALL_READ_MS]
          ,[IO_STALL_WRITE_MS]
          ,[NUM_OF_READS]
          ,[NUM_OF_WRITES]
          ,[SIZE_ON_DISK_MB]
          ,[DBNAME]
          ,[NAME]
          ,[FILE_ID]
          ,[DB_FILE_TYPE]
          ,[DISK]
          ,[FILE_LOCATION]
          ,[TIMESTAMP]
          ,[ROW])
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]
FROM dbo.DISKIOPS 
)

--Need to divide by the number of operations in that timeframe to get average wait time per operation.
--SELECT MAX(([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES))
SELECT [IO1].[TIMESTAMP],
      [IO1].[NAME],
     ([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) AS Avg_Stall_Per_Operation
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)
WHERE IO1.NAME = IO2.NAME
--Need to make sure not dividing by 0 when there has been no operations
AND (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) > 0
AND IO1.Disk = @Disk

CTE 不允許您添加索引，它們的行為類似於視圖，但它們不是數據庫中的持久對象，因此它們不能具有索引（與視圖不同）。

如果瓶頸是 JOIN 操作，您應該使用臨時表並在您將使用的列上添加索引以加快 JOIN 操作。

您正在處理這裡的規範化問題。不是向每一行添加時間戳，而是創建一個包含標識列和時間戳列的父表，然後在插入 DISKIOPS 表時使用該標識值。這樣，您就可以保留一個“行號”，並且不需要即時計算一個。現在，不能保證身份是連續的，但在您的案例中它可能會一直如此。

如果您無法更改表佈局，請在 DISKIOPS(FILE_LOCATION,TIMESTAMP) 上放置一個聚集索引，這應該有助於該查詢的性能。

此外，正如@MartinSmith 已經提到的，加入 FILE_LOCATION，而不是 NAME。雖然在您的情況下它可能在邏輯上等效，但只有 FILE_LOCATION 上的連接才能使用該索引。

最後，不要使用 where 子句來防止“被零除”，而是使用以下命令：

([IO2].[IO_STALL] - [IO1].[IO_STALL]) / NULLIF((IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES),0)

這會導致值為 NULL 而不排除整行，更重要的是不會阻止索引使用。

引用自：https://dba.stackexchange.com/questions/46070