I've been tasked by the dev manager as a DBA to look into performance tuning the code as attached in the link provided below.
Basically, this is a stored proc that uses a CTE and then joins the CTE with various tables. However, the stored proc execution is surpassing way more than 9 minutes and I need to basically bring the execution time down as much as possible either by rewriting the code or adding indexes.
Looking at the actual execution plan it seems that the most expensive operation seems to be "sort" operation incurring 97% of the overall cost. We have a non clustered index on the sorted column but the sort order is asc and the code does a desc.
I am thinking of changing the sort order in the index to desc but again it uses a
row_number() function which requires an order by to be explicit.
Is there a way I can reduce the cost or rewrite the query or maybe add an index or two to reduce the execution time?
;WITH cte_MaxStatus as ( SELECT QueuedTxUpdateID, ObjectID, QueuedAt, ErrorMessage,fkSfUpdateStatus, fkSfUpdateAction, ObjectType, ProcessedAt, rn = ROW_NUMBER() OVER(PARTITION BY ObjectType, ObjectID ORDER BY QueuedAt DESC) FROM dbo.SfQueuedTxUpdates ) SELECT MOT.ObjectName,/*mot.Description, */ ObjectID, QueuedAt, ErrorMessage, ua.SfUpdateAction, us.SfUpdateStatus, cte_MaxStatus.ProcessedAt FROM cte_MaxStatus LEFT JOIN dbo.SfUpdateAction UA ON cte_MaxStatus.fkSfUpdateAction = ua.SfUpdateActionID LEFT JOIN dbo.SfUpdateStatus US ON cte_MaxStatus.fkSfUpdateStatus = US.SfUpdateStatusID LEFT JOIN DBZ.dbo.ModuleObjectTypes MOT ON cte_MaxStatus.ObjectType = MOT.ObjectTypeID WHERE fkSfUpdateStatus <> 3 AND cte_MaxStatus.rn = 1