July 16, 2014 at 1:32 pm
My application's database has a single stored procedure (out of hundreds) that seems to loose its "good" execution plan weekly. How can I prevent my stored procedure from loosing the execution plan after it has a "good" one stored?
The stored procedure is executed multiple times a day, every day. My issue is this, about once a week, when the stored procedure is executed, the CPU usage spikes and remains spiked until I run an ALTER on the stored procedure. Afterwards, the CPU usage on this server is fine.
When I view the Activity Monitor, the stored procedure is listed as an expensive query. When I view the Execution Plan, I see that it is doing an index scan. When the stored procedure is running without issue, the stored procedure is doing an index seek.
I have a maintenance plan that runs nightly. Part of the plan is to rebuild/reorganize indexes. Also, it's worth mentioning that the ContainerQuestions (referenced below) is my largest table and is over 54GB. It has a composite key that is the PKs from the ContentContainers and Questions table.
The stored procedure in question is below:
CREATE PROCEDURE [dbo].[SPName]
@containerIDINT=NULL,
@prefaceIDINT=NULL,
@errorMsgNVARCHAR(2000) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @errorMsg = ''
SELECTa.pk_Algorithms_ID,
a.IsPrefaceOwned,
a.fk_Algorithms_QuestionPrefaces_ID,
a.fk_Algorithms_Questions_ID,
a.QuestionPrefaceReferenceCount,
a.VariableName,
a.AlgorithmType,
a.FunctionParameters,
a.AlgorithmTitle,
a.AlgorithmDescription,
a.AlgorithmText,
a.AlgorithmReturnType,
a.AlgorithmInternalReturnType,
a.WasCalculated,
a.DecimalPlaces,
a.FixedDecimalPosition,
a.HideLeadingZeros,
a.LastCalculatedValue,
a.FormattedResults,
a.DateCreated,
a.HasImageData,
a.ImageData,
a.MathML,
a.XHTML,
1 AS NeedsRecalc
FROM dbo.Algorithms a
INNER JOIN dbo.Questions b
ON b.pk_Questions_ID = a.fk_Algorithms_Questions_ID
INNER JOIN dbo.ContainerQuestions cq
ON cq.fk_ContainerQuestions_Questions_ID = b.pk_Questions_ID
WHERE b.fk_QuestionInfo_QuestionPrefaces_ID = @prefaceID
AND cq.pk_ContainerQuestions_ContentContainers_ID = @containerID
AND a.IsTemporary = 0
IF @@ROWCOUNT = 0
BEGIN
SET @errorMsg = 'no records found'
RETURN 10
END
SET @errorMsg = 'success'
RETURN 0
END
July 16, 2014 at 9:07 pm
Two things come to my mind, parameter sniffing or stale statistics. You may need to rebuild statistics on the table(s) accessed by the stored procedure. Not saying it will fix the problem but it is one possibility.
July 17, 2014 at 11:17 am
Definitely sounds like a parameter sniffing issue. The reason the ALTER works is because that causes a recompile at the next execution of the procedure. Updating statistics or rebuilding an index on a table accessed by the procedure will also cause a recompile.
The way I explain the parameter-sniffing problem is using the example of a phone book in the US. If I'm searching for John Xavier, I can find him pretty quickly because there is probably only a handful of Xaviers in the phone book because it is indexed on Last Name, First Name. So in SQL Server a non-clustered index seek with a key/bookmark lookup to get the phone number is probably a great way to find him. If I'm looking for John Smith it will take me a long time to find him because there will likely be pages of Smiths and a bunch named John. So in SQL Server an index seek with a bookmark lookup will likely slower than a table/clustered index scan. So in SQL Server if my query is compiled looking with a Last Name parameter of Xavier and then I search for Smith, I'm no longer getting a good plan for that query.
The common ways to fix that are:
1. Indexing. If I can define a non-clustered index that will cover the query, I'll probably solve the problem.
2. Query hints. Identify the specific query in the procedure that is causing the issue and add OPTION (RECOMPILE) or OPTIMIZE FOR(). Here's the BOL page for query hints, http://msdn.microsoft.com/en-us/library/ms181714(v=sql.105).aspx
3. Dynamic SQL using sp_executesql can possibly help if you are passing multiple optional parameters
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2014 at 1:45 pm
Does running rebuild/reorganize on an index cause any execution plans that were reliant upon that index to recompile the next time the stored procedure is run?
July 17, 2014 at 2:00 pm
wmoore 54341 (7/17/2014)
Does running rebuild/reorganize on an index cause any execution plans that were reliant upon that index to recompile the next time the stored procedure is run?
Rebuild definitely does because it also triggers a statistics update which is one of the things that triggers a recompile. I don't think a reorganize causes a recompile because that does not trigger a statistics update. There's a list of things that can cause a recompile in this BOL article, http://msdn.microsoft.com/en-us/library/ms179294.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply