The actual plan for the first insert statement reveals some things you should improve:
- I would add an
OPTION (RECOMPILE) hint to this query. This will allow the optimizer to produce a plan taking into account the runtime values of variables. This should simplify the plan quite a bit.
- Once the hint above is in place, you should ensure the Uploads table has an index with
ClientID, UploadCompleteDttm as the leading keys. This will provide the required sort order, and allow a seek when
@startdate_local is not null.
The index should also include
FileName, FileUploadID, ProcessErrorText to avoid lookups. An example index definition would be:
CREATE INDEX IndexName
ON fileManager.FileUploads (ClientID, UploadCompleteDttm)
INCLUDE (FileName, FileUploadID, ProcessErrorText);
The Clustered Index Scan on FilesReceived looks to be very expensive. Five and a half billion rows are tested (across multiple scans) to return around 5,000 rows. This cost is well hidden by the residual predicate (as shown in Sentry One Plan Explorer):
Add an index to FilesReceived keyed on
FileUploadID. If that index already exists, and the scan persists in the actual execution plan after the previous steps listed here have been applied, use a
FORCESEEK hint on the FilesReceived reference. You may also try
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));.
The problematic part that jumps out at me is:
This corresponds to the code:
from fileManager.FilesReceived fr
join fileManager.Tracking t on fr.FilesReceivedID = t.FilesReceivedID
where parentfileid = z.FilesReceivedID) as 'HasChildren'
The rest of the execution plan is quite large, but does not seem too terrible. I would still try
OPTION (RECOMPILE) again, for the same reasons as for the first query.
If that does not improve performance enough on its own: I would remove the code fragment shown above from the query, persist the result rows (only 5,239 for the example given) in a separate temporary table, then find the
HasChildren value as a separate step. I would also rewrite the final
INSERT to use an explicit
TOP (5) clause instead of using
SET ROWCOUNT 5.
Ensure the FilesReceived table has an index on