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.


1 Answer 1


First insert

The actual plan for the first insert statement reveals some things you should improve:

First insert

  1. 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.
  2. 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.
  3. 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);
  4. 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):

    Scan properties

  5. 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:

Plan fragment

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.

  • that definitely took care of it. A proc that took over 5 minutes down to 2 seconds.
    – Shellz
    Commented Aug 14, 2017 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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