Ask the Expert

Questions about plan stability

Plan stability is a major concern for me right now. Frequently when I update stats, a plan(s) changes and I get into hot water. I'm investigating stored outlines, but I have two concerns and they are: (1) I have a lot of current plans that are terrible and I don't want to lock them in with the others. (2) It's my understanding that for every cursor parsed, using stored outlines, a table must be hit to determine if an outline exists for it. I'm worried about the overhead of this process.

    Requires Free Membership to View

I don't have much experience with stored outlines, but with respect to question 1, you can store outlines just for selected SQL via:
CREATE OUTLINE outline [FOR CATEGORY category] ON statement
To gauge the overhead of using stored outlines, set up a trace of SQL that uses them, and compare it to a trace of the same SQL without using stored outlines. Remember that the lookup of a stored outline may be less expensive than constructing a new optimizer plan.

This was first published in December 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: