Sql-server – MSSQL Stored procedure becomes slow after few days

sql server

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

Best Answer

I would look at your plan cache to see what's happening there. Could be a bad plan, could be multiple single use plans are being generated? Look closer at these DMVs, sys.dm_exec_cached_plans and sys.dm_exec_query_stats, they will give you more insight. This is a small query that I use:

USE master  
GO  
SELECT  
    t1.objtype AS ObjectType,  
    t1.cacheobjtype AS CacheType,  
    COUNT_BIG(*) AS TotalPlans,
    SUM(CAST(t1.size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS TotalSizeInMB,  
    AVG(CONVERT(bigint, t1.usecounts)) AS AvgUseCount,  
    SUM(CAST((CASE WHEN t1.usecounts = 1 THEN t1.size_in_bytes  
    ELSE 0  
    END) AS DECIMAL(18, 2))) / 1024 / 1024 AS SingleUseSizeInMB,  
    SUM(CASE WHEN t1.usecounts = 1 THEN 1  
        ELSE 0  
        END) AS CountOfSingleUsePlans  
FROM sys.dm_exec_cached_plans t1  
GROUP BY  
    t1.objtype,  
    t1.cacheobjtype  
ORDER BY 3 DESC
Related Question