November 25, 2015 at 11:06 pm
Hi All,
I have a scenario where my stored procedure which usually takes 5-6 min on average to execute sometime takes an 30-40 min to complete. On further investigation I found that this is due to fact that SP has used a bad execution plan due to plan caching (default behavior parameter sniffing). So I had to use SP_Recompile with the SP, to get this resolve.
This is a recurring issue and each time I have to recompile it.How can I stop this happening. Do I need to rewrite the whole logic of my stored procedure. I cant use recompile hint within query as this will further consume lot of CPU.
Please suggest any alternative options.
Thanks
Sam
November 25, 2015 at 11:34 pm
Are you using your parameters being sent to the procedure in queries directly, o do you assign them to local variables within the stored procedure?
Have a look at this article.. it explains the different types of sniffing and how you could address them.
November 25, 2015 at 11:40 pm
Can you post the procedure along with the two execution plans (as .sqlplan files)?
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
November 26, 2015 at 12:23 am
Justin Manning SA (11/25/2015)
Have a look at this article.. it explains the different types of sniffing and how you could address them.
I directly use the parameters in queries within stored procedure. I don't assign it to local variables first . If I do that in case will it prevent this issue from occurring.
November 26, 2015 at 12:47 am
Lets have a look at the info Gilla asked you to send?
November 26, 2015 at 1:19 am
sam 55243 (11/26/2015)
If I do that in case will it prevent this issue from occurring.
Maybe. And maybe it'll make things worse.
Does the Recompile hint add an unacceptable CPU overhead to your production server?
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
November 26, 2015 at 2:19 am
Are you allowed to modify the underlying procedure?
If you aren't allowed to modify it and you have Enterprise edition you can use sys.sp_create_plan_guide to start changing things. But it has a bunch of caveats and can (among other things) break later software upgrades. https://technet.microsoft.com/en-us/library/ms190417(v=sql.110).aspx
November 26, 2015 at 5:05 am
GilaMonster (11/26/2015)
sam 55243 (11/26/2015)
If I do that in case will it prevent this issue from occurring.Maybe. And maybe it'll make things worse.
Does the Recompile hint add an unacceptable CPU overhead to your production server?
Hi,
This SP is heavily used and mainly called from an OLTP application so I cant go ahead and add recompile hint. As I have very limited access to live database I couldn't share you the code/plan.
November 26, 2015 at 5:37 am
Ask the DBA for the code and the plans please. Without them there's minimal chance that we can help you.
Have you tried with recompile and is the overhead unacceptable? I've used it on heavily used procs before without problems.
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
November 26, 2015 at 12:39 pm
If it is parameter sniffing causing the problem then you can just alter the stored procedure to copy the parameters passed in into ones created within the SP.
Example:
GO
CREATE PROCEDURE mySPWithParameterSniffing
(
@Param1 varchar(10),
@Param2 int
)
AS
BEGIN
SELECT *
FROM myTable
WHERE Col1 = @Param1
AND Col2 = @Param2
END
GO
GO
CREATE PROCEDURE mySPWithNoParameterSniffing
(
@Param1 varchar(10)
@Param2 int
)
AS
BEGIN
DECLARE @Param1x varchar(10),
DECLARE @Param2x int
SELECT @Param1x=@Param1, @Param2x=@Param2
SELECT *
FROM myTable
WHERE Col1 = @Param1x
AND Col2 = @Param2x
END
GO
November 27, 2015 at 1:43 am
Jonathan AC Roberts (11/26/2015)
If it is parameter sniffing causing the problem then you can just alter the stored procedure to copy the parameters passed in into ones created within the SP.
Which may work or may result in other problems. It's not a guaranteed fix. (Well, it'll fix the parameter sniffing, but it can cause other performance problems due to the lack of parameter sniffing)
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply