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 statementTo 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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation