August 12, 2015 at 2:39 am
Hi,
A strange issue is reported in production where one of stored proc is executing in variable time. Sometime with the same input it takes 5 sec and sometime more than 3 mins.
Dev told that while it is performing poor, they execute "DBCC FREEPROCCACHE" and it resolve the problem temporarily. as per mssql description, it clear cache that can lead to clear parsed plan. so if this is true it will take time to store more plans to serve from cache.It may not improve performance but in some cases can degrade performace.
Can you please guide me on what other areas should I expand my search to resolve this issue ?
August 12, 2015 at 2:49 am
It's probably parameter sniffing. Google that term if you want to learn more on your own, if you want help fixing the problem please post the stored procedure here, along with the execution plans (preferably fast and slow)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2015 at 2:52 am
thbaig (8/12/2015)
Hi,A strange issue is reported in production where one of stored proc is executing in variable time. Sometime with the same input it takes 5 sec and sometime more than 3 mins.
Dev told that while it is performing poor, they execute "DBCC FREEPROCCACHE" and it resolve the problem temporarily. as per mssql description, it clear cache that can lead to clear parsed plan. so if this is true it will take time to store more plans to serve from cache.It may not improve performance but in some cases can degrade performace.
Can you please guide me on what other areas should I expand my search to resolve this issue ?
Quick thought, NOT a good idea as there is no need to flush the entire cache. Start by finding out what is the actual problem before applying a blanket solution which affects everything on the server.
😎
If the problem turns out to be a bad execution plan for that particular procedure then I suggest either to invalidate the plan for that single procedure or force recompilation at the execution time.
August 12, 2015 at 3:15 am
Thank you Gail and Eirikur Eiriksson. I have enabled the trace on serve to capture execution plans and related details. So that I may able to compare slow and fast execution and related plans.
Gail with the initial googling , it seems that your doubts are closet.
I will keep posting with update.
Thanks
August 12, 2015 at 5:46 am
You can use DBCC FREEPROCCACHE to target a single execution plan. Something like this:
SELECT @PlanHandle = deqs.plan_handle
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.text LIKE 'CREATE PROCEDURE dbo.MergeSalesOrderDetail%'
IF @PlanHandle IS NOT NULL
BEGIN
DBCC FREEPROCCACHE(@PlanHandle);
END
GO
Obviously you'll need to edit the LIKE statement to look for your particular query or procedure.
However, note, this is not a long term fix for bad parameter sniffing. If you're getting that you need to explore the other methods such as OPTIMIZE FOR, RECOMPILE, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply