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
Improve this question
2
  • 2
    The first thing that comes to mind is Parameter Sniffing. Commented Aug 11, 2016 at 10:08
  • Can you post the "actual" execution plan from a "slow" run? Commented Aug 11, 2016 at 11:29

1 Answer 1

Reset to default
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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.