Period(*). I have two motives here. One – I liked the title and I’ve been having fun with titles lately (A couple weeks ago it was the true story of a SAN Failure with “Yes, Virginia, SANs Can Fail“). Two – it’s a good reminder and a couple recent conversations brought it up so I figured I’d talk about an old feature that first came out in SQL Server 2005 – Plan Guides and has been improved upon since – Like the introduction of the ability to “freeze” a plan starting in SQL Server 2008.
Why Do I Want to Keep My Existing Query Plan?
Where I see and deploy plan guides is often with interesting vendor databases. You know the kind. The kind of vendor I write blog posts whining about (It’s a mean title and sounds harsh but really I love ISVs, it’s a tough love). Their code isn’t great, their support is worse and they’ve done some interesting things in their queries. Perhaps you can’t modify their stored procedures, perhaps you can’t modify indexes, perhaps they’ve put a really bad hint into their query that you don’t like based on research and analysis.. That’s why you want to use a plan guide in general.
Why do you want to freeze a particular plan?
I’ll be honest, I’ve not done this much, but the times I have done it were with vendor apps experiencing parameter sniffing gone bad. I won’t get into all the nitty gritty of this concept – the linked post on Simple Talk is a great deep dive there. But parameter sniffing can be a good thing. It can also be a bad thing. Where it is a bad thing in code that I or a client owns, the solution is normally to look at the approach to the query, to consider something different. Where it is someone else’s code and we really can’t make much of a change – I MIGHT want to keep my plan. So in this case, I’ll want to find a good example of the plan and “freeze” it.
One recent example – a vendor application that had one particular query executed a whole lot. It was a health care electronic medical records system. Couldn’t change much in their code and 95% of the time when this particular query runs for the first time it “sniffs” the right parameters, compiles appropriately and every execution thereafter is fine. Yes the edge case may have a suboptimal plan but the impact isn’t felt by anyone.. 5% of the time, though, when stats are recreated, a restart happens, plans are invalidated for other reasons, a “bad” (it isn’t really bad.. but it has bad results) execution is the first one run and a plan is compiled for the parameters that aren’t very helpful. As that article points out there are lots of ways to fix this issue, but the best one for us at the time was to freeze a good plan. I wanted to keep my plan, and they didn’t have stored procedures for me to play with – Java came up with the SQL code and sent it across.
Their data was relatively consistent with how it was added, the skew didn’t grow much over time and the problem occurred too often where the poor plan was chosen and panic mode was struck when the nurses, receptionists and doctors started calling with “Everything is slow working with patient visit data!!!” I didn’t want to go here first in this example. I wanted to work on the real problem. But through this vendor? I wasn’t about to work on the real problem anytime soon. I knew that things were stable in terms of the symptoms and the way these tables operate, so it was an okay thing to do because the good (stability and quiet support phones) outweighed the calculated risk (data skew making the frozen plan bad someday). Plus we documented the risk and watch it from time to time.
Is this the first place I go for a client when I’m doing workload tuning with Linchpin People? No, but it is good to have plenty of tools and the knowledge of which ones to use when. These were introduced for a reason and they can sometimes be helpful.
How Do You Freeze a Plan?
Using the sp_create_plan_guide_from_handle procedure. The books online article linked gives a good example. Rather than take you through the code here, I recommend that example to you.
The procedure, for a single statement per batch is basically (again you can see some script examples in the article linked above from MSDN/books online):
- Find the plan you like in the plan cache
- Create a plan guide with this procedure, specifying that plan that you like
- Test it out and confirm you are good.
I said you can keep your query plan if you liked your query plan, I suppose I should apologize now before publishing the post. There are actually, as it turns out some exceptions and cases where a plan guide just won’t work. You still are getting the best possible plan here, so the title of the post isn’t terribly misleading.
You should validate your plan guide with the sys.fn_validate_plan_guide ( plan_guide_id ) function to see if it is valid and deal with the error messages returned. Perhaps there is a hint in an older plan referencing an index that doesn’t exist any longer and this can cause it to become invalid. Perhaps you’ve made a typo in the syntax for creating the plan guide.
You can see your plan guides getting used successfully or not (or not used at all) with SQL Trace/Profiler and the plan guide successful/unsuccessful events. Or you can see this with extended events for a more lightweight view of things if you are monitoring a busier system – my rule of thumb is no profiler in prod, trace? Sometimes, Extended Events? Preferred with 2012 and above and even some 2008 needs. If you are just playing around in a test/dev environment? Fire up profiler and don’t tell anyone I suggested it.
One more caveat.. Sometimes you may not really want to force a plan to always be used. Imagine your data changes, imagine something gets changed in the vendor side and they fix the problem. Imagine the data skew changes for your client and now the bad plan would have been the good plan most of the time. This is, effectively, a hint – in fact its sort of more than a hint in a lot of ways.
So this should never be your first place to look and if problems exist with this query in the future and you’ve frozen a plan? That should be the first place you look. This serves a real purpose, but use it with understanding.
I share this not to say plan guides are the ultimate band-aid – but to offer a reminder about their existence.