Stored procedure was fine but then became extremly slow

Question

My stored procedure was taking around 10 seconds, but suddenly (for unknown reasons) it became so slow (taking 9 minutes).

I did not do any changes at all that may cause the delay.

I wonder if someone can tell why it is so slow.

Here is my query

SELECT 
    P.PtsID, P.PtsCode, P.PtsName, 
    FORMAT(P.DOB, 'dd/MM/yyyy') AS DOB, P.Gender, V.VisitID, V.VisitType, 
    FORMAT(V.VisitDate, 'dd/MM/yyyy') AS VisitDate,  
    FORMAT(V.DischargeDate, 'dd/MM/yyyy') AS DischargeDate, 
    R.RepID, R.RepDate, R.RepType, R.RepDesc
FROM
    Patients P 
INNER JOIN 
    Visits V ON P.PtsID = V.PtsID 
INNER JOIN 
    Reps R ON R.PtsID = P.PtsID AND R.VisitID = V.VisitID
WHERE 
    (P.Deleted = 0 AND V.Deleted = 0 AND R.Deleted = 0)
    AND (P.PtsName LIKE '%'+TRIM(@PtsName)+'%' OR TRIM(@PtsName) = '')
    AND (P.PtsCode LIKE '%'+TRIM(@PtsNo)+'%' OR TRIM(@PtsNo) = '')
    AND (R.RepText LIKE '%'+TRIM(@RepText)+'%' OR TRIM(@RepText) = '')
    AND (TRIM(@RepCode) = '' OR R.RepID IN (SELECT RepID 
                                            FROM tags 
                                            WHERE tag = 'XXX' 
                                              AND Deleted = 0
                                              AND code IN (SELECT value 
                                                           FROM string_split(@RepCode,','))))

and this is the execution plan

When I execute the script as an ad-hoc query, not as stored procedure, it is very fast.

Edit

Here is my actual execution plan:

https://www.brentozar.com/pastetheplan/?id=ByPLltcZD

Thanks

Can you please paste your actual execution plan to https://www.brentozar.com/pastetheplan/ ? — marc_s, Aug 07 '20 at 07:02
Your statistics/estimates are completly wrong, see `900 of 2 - 45.000%` , Can it be [parameter sniffing](https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/) ? Please share the exection plan as @marc_s suggested. Your query might not be sargable (like % %) . Changing the IN to EXISTS may improve performance a little. — Preben Huybrechts, Aug 07 '20 at 07:10
Answered here : https://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure — Cyril G., Aug 07 '20 at 07:21
@CyrilG. Thanks mate, that solved the problem :) put it as an answer I will mark it — asmgx, Aug 07 '20 at 07:31
Answered here : https://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure — Cyril G., Aug 07 '20 at 07:36

Preben Huybrechts · Accepted Answer · 2022-02-10T06:19:41.277

So purely bassed on the execution plan.

Statistics

In your exection plan you can see the actual numbers and estimates are far apart. This can have multiple causes.

Out of date statistics and indexes. Try rebuilding indexes and updating statistics
- Updating stats: EXEC sp_updatestats (this might take some time and resources, so if its a production server, do it out of the office hours / batch job windows.)
Parameter sniffig can also cause wrong estimations. You can expirment with OPTION(RECOMPILE) or OPTION(OPTIMIZE FOR UNKNOWN) to test if this is the case.
201 Bucket Problem

Query optimization

When you write IN(subquery) and you have a key lookup, like you have. You are going to have a bad time. For every row returned from the index (NonClusteredIndex-code) the engine needs to access the clustered index to retrieve the RepID one-by-one = Painfully slow when it's multiple rows (In your case: 430.474.500 rows).

You can change this by using EXISTS, in your example:

SELECT 
    P.PtsID, P.PtsCode, P.PtsName, 
    FORMAT(P.DOB, 'dd/MM/yyyy') AS DOB, P.Gender, V.VisitID, V.VisitType, 
    FORMAT(V.VisitDate, 'dd/MM/yyyy') AS VisitDate,  
    FORMAT(V.DischargeDate, 'dd/MM/yyyy') AS DischargeDate, 
    R.RepID, R.RepDate, R.RepType, R.RepDesc
FROM
    Patients P 
INNER JOIN 
    Visits V ON P.PtsID = V.PtsID 
INNER JOIN 
    Reps R ON R.PtsID = P.PtsID AND R.VisitID = V.VisitID
WHERE 
    (P.Deleted = 0 AND V.Deleted = 0 AND R.Deleted = 0)
    AND (P.PtsName LIKE '%'+TRIM(@PtsName)+'%' OR TRIM(@PtsName) = '')
    AND (P.PtsCode LIKE '%'+TRIM(@PtsNo)+'%' OR TRIM(@PtsNo) = '')
    AND (R.RepText LIKE '%'+TRIM(@RepText)+'%' OR TRIM(@RepText) = '')
    AND (TRIM(@RepCode) = '' OR EXISTS (SELECT 1
                                            FROM tags 
                                            WHERE tag = 'XXX' 
                                              AND Deleted = 0
                                              AND tags.RepId = r.RepId
                                              AND code IN (SELECT value 
                                                           FROM string_split(@RepCode,','))))

Index optimizations

If you are still suffering from the Key lookup you may want to change the index, so it also has RepId or include it.

You still have 2 other key lookups. You could also solve this with an INCLUDE on the indexes but only if it makes sense. (Can they be used for other queries, is the current query executing frequently, ...)

Doing the trims and concatenation and storing them in a separate variable may give you some minor improvements.

Wait stats

The following wait stats where also included in the execution plan.

<WaitStats>
  <Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="1018" WaitCount="2694600"/>
  <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="514" WaitCount="159327"/>
  <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="63" WaitCount="5"/>
  <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="25" WaitCount="14639"/>
</WaitStats>

RESERVED_MEMORY_ALLOCATION_EXT and MEMORY_ALLOCATION_EXT but there is no issue with wait stats.

score 1 · Answer 2 · answered Aug 07 '20 at 12:38

1

As mentioned in another post: SQL Server: Query fast, but slow from procedure

You can use the following workaround

Slow: SET ANSI_NULLS OFF

Fast: SET ANSI_NULLS ON

answered Aug 07 '20 at 12:38

Cyril G.

1,879
2
5
19

Stored procedure was fine but then became extremly slow

2 Answers2

Statistics

Query optimization

Index optimizations

Wait stats