SQL Server Performance – Troubleshooting Slow Stored Procedures

performancequery-performancesql serversql-server-2016

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.

Best Answer

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.

Specifics

The faulty plan reports these table variables with an estimated 130 billion rows.

The part of the plan you are referring to is:

As you can see, it is the Table Spool that is estimated to produce ~130 billion rows; the table variable emits only 198,411.

The sort and spool combination is designed to optimize repeated scans, by caching the result from one iteration of the nested loop join and replaying the saved result on the next iteration if the correlated parameter(s) have not changed. The sort ensures any potential duplicates arrive together, since the spool only caches the most recent result. The estimate from the spool is the total number of rows (198,411 from the table variable * 653,969 iterations).

The useful predicate relating the rows from the sort with the table variable is stuck on the nested loops left outer join iterator:

Looking at this in conjunction with the output columns from the table variable, we can conclude that an index on the table variable on PatientID, FirstTestDate would almost certainly eliminate this problem.

An analysis of sub_PSTRules could remove the index and table spools seen there, though these are not having much of an effect on performance at this stage:

Nevertheless, it is wasteful to have SQL Server build a temporary nonclustered index each time, then throw it away at the end. The missing (filtered) index is likely:

CREATE INDEX give_me_a_good_name
ON dbo.sub_PSTRules
    (SubscriberSID, CinicSID, OfficeSID)
INCLUDE
    (PSTQuestionGroupSID)
WHERE
    OfficeSID IS NULL;

Best Answer

First insert

Second insert

Related Solutions

SQL Server – Why Query Runs Slower in Stored Procedure Than Query Window

SQL Server – Inconsistent Execution Plan for Stored Procedure

Specifics

Related Question