3

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using 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
3

CTEs don't allow you to add indexes, they behave similarly to views but they aren't a persistent object in the database so they can't have indexes (unlike views).

If the bottle neck is the JOIN operation you should resort to a temp table and add an index on the column you will be using to speed up the JOIN operation.

2

your are dealing with a normalization issue here. Instead of adding a timestamp to each row, create a parent table with an identity column and the timestamp column and then use that identity value when inserting into the DISKIOPS table. That way you have a "row number" persisted and do not need to calculate one on the fly. Now, identity is not guaranteed to be continuous but in your use case it probably always will be.

If you can't change the table layout put a clustered index on DISKIOPS(FILE_LOCATION,TIMESTAMP) That should help with the performance of that query.

Also, as @MartinSmith already mentioned, join on FILE_LOCATION, not on NAME. While it might be logically equivalent in your case, only the join on FILE_LOCATION will e able to use that index.

Finally, instead of the where clause to prevent a "division by zero", use this:

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

That causes the value to be NULL without excluding the entire row and more importantly without preventing index usage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.