Stored Procedure taking over 10 seconds

I have this stored procedure that is taking over 10 seconds to run. It returns a data set (list of files) to the application. Any suggestions on how to speed it up?

Details about the plan. It looks like query 14 in the plan takes 98% relative to the batch. Inside query 14 is a sort that is 62% of query 14.

I'm using SQL Server 2016. Estimated query plan.

Here is the actual plan. Took 5 min. and 21 seconds to run.

First insert

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'));.

Second insert

The problematic part that jumps out at me is:

This corresponds to the code:

(select count(1)
     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 ParentFileId.

StackExchange Database Administrators Q#183128, answer score: 7