0

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
2

1 Answer 1

1

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  

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.