Execution Plan Lost - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Execution Plan Lost

Author

Topic

williammoore0
Starting Member

1 Post

Posted - 2014-07-16 : 15:20:36

New to the site. I'm looking for assistance with an issue that I've had now for months. My issue is that about once a week, I have a stored procedure that appears to loose its "good" execution plan. This is just an assumption. I'm aware of parameter sniffing however, I'm not sure if its the culprit. My application's database server will be fine, then the CPU will spike, causing the system to basically come to a halt. If I look at expensive queries (in SQL Monitor), the same stored procedure always presents itself. If I open the SP (right-click and select Modify) and then execute the SP, the CPU spike goes down.

Any assistance on an approach to resolve this issue or to identify without a doubt the culprit of the issue would be greatly appreciated.

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]
@containerID INT=NULL,
@prefaceID INT=NULL,
@errorMsg NVARCHAR(2000) OUTPUT
AS
BEGIN
SET NOCOUNT ON;

SET @errorMsg = ''

SELECT a.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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-16 : 15:23:45

Unless you save your plan, the plan is subject to be removed when some conditions are met.
It's not random but there are rules for what causes the plan to be removed.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

tm
Posting Yak Master

160 Posts

Posted - 2014-07-23 : 10:41:50

The link might help ..

[url]http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources