Stored procedure becomes slow every couple of days

Question

I am facing an issue on SQL Server in which my stored procedure becomes slow after couple of days.

Below is the sample of my stored procedure.

Could this be a caching issue on the server side? Can I increase the server's cache size to resolve the problem?

Normally the stored procedure returns data in one second.

@START_VALUE int=null,
    @END_VALUE int=null
    @UID NVARCHAR(MAX)=null,
    AS
    BEGIN

  SELECT
    dbo.TABLE1.ID, 
    ROW_NUMBER()  OVER (ORDER BY TABLE1.UPDATED_ON desc) AS RN,   
    CONVERT(VARCHAR(10), dbo.TABLE1.DATE, 101) AS TDATE,
    CATEGORY = (
            SELECT TOP 1 COLUMN1
            FROM TABLE5 CT1
            WHERE TABLE1.CATEGORY = CT1.CATEGORY_ID
       ), 
    TYPETEXT = (
            SELECT TOP 1 COLUMN1
            FROM TABLE6 CT1
            WHERE TABLE1.TYPE = CT1.TYPE_ID
       ),
    IMAGE = STUFF(( SELECT DISTINCT ',' + CAST(pm.C1 AS varchar(12))
                    FROM TABLE2 pm
                    WHERE pm.ID = TABLE1.ID AND pm.C1 IS NOT NULL AND pm.C1 <> '' 
                    FOR XML PATH('')),
                  1, 1, '' ) INTO #tempRecords       
  FROM dbo.TABLE1 
  WHERE ((@UID is null OR  dbo.TABLE1.ID = @UID )
  ORDER BY TABLE1.UPDATED DESC      

  SELECT @count = COUNT(*) FROM #tempRecords;

  SELECT *, CONVERT([int],@count) AS 'TOTAL_RECORDS'
  FROM #tempRecords 
  WHERE #tempRecords.RN BETWEEN CONVERT([bigint], @START_VALUE) AND CONVERT([bigint], @END_VALUE)      

END

GO
'

Debugging performance can be a challenge. When it becomes slow, does it stay slow or just run slow for a specific query? The `execution plan` could be caching a bad plan from the parameters being passed in. Take a look at this posting on `parameter sniffing` -- it might be helpful: http://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server — sgeddes, Aug 02 '16 at 13:03
@sgeddes when it becomes slow, it becomes slow for particular inputs only. for some of the inputs it works faster. — Hemal, Aug 02 '16 at 13:06
try to avoid the #temp , large result means , slow execution, find a way around it — Arun Prasad E S, Aug 02 '16 at 13:07
please post the execution plan for both smaller inputs and faster inputs — TheGameiswar, Aug 02 '16 at 13:08
While I generally avoid this as it recreates the query plan each time, sometimes I've had no other choice. Take a look at this post: http://stackoverflow.com/questions/20864934/option-recompile-is-always-faster-why — sgeddes, Aug 02 '16 at 13:09
The query seems to have a few syntax errors. This undermines my confidence in the code presented being representative of the problem. — John Bollinger, Aug 02 '16 at 13:24
This is very likely due to execution plans that are not optimized with the current parameters. One option might be to use OPTION RECOMPILE in you procedure. — Sean Lange, Aug 02 '16 at 13:39

score 0 · Answer 1 · answered Aug 02 '16 at 13:08

A few performance tips:

1) @UID is null OR dbo.TABLE1.ID = @UID --> this is bad because you'll have one execution plan when UID is null and when it's not. Build a dynamic sql query and you'll get 2 execution plans.

2) Update stats in a maintenance plan.

3) Check index fragmentation.

4) Try to do the same thing without using a temp table.

5) Try to avoid castings.

Stored procedure becomes slow every couple of days

1 Answers1