Unable to use forced parameterization when deploying plan guides in SQL Server 2005
I was working a case last week where I wanted to instate some fairly simple plan guides for a query that had picked a bad plan on a particular deployment of an application. I was thinking it was going to be an easy process. The query in question executed several tens of thousands of times, and had 2 parameters being generated by the application. On the server I was analyzing the performance had degraded due to a bad plan, but I had a trace from another server using the same application, where I had captured the good plan. This ought to have been a classic case for a plan guide (since I couldn’t change the SQL batch coming from the application, and the server had been managed properly so I couldn’t address the issue by simple update stats or some such). Anyway it drove me mad for a few hours as I couldn’t get it to work, so thought I would share, what turned out to be, the simple reason why :-)
I needed to do 2 things:
1. Create a plan guide which forced parameterization of the query, since I had noted it wasn’t being parameterized by default.
2. Create a plan guide which forced a specific plan on the parameterized form of the query
Both of these are fairly standard steps, step1 is documented here:
Specifying Query Parameterization Behavior by Using Plan Guides
and step 2 is documented here:
I’ve done this many times before, but whatever I did, I couldn’t get step 1 to fly, so I couldn’t go any further. After much double checking of what I was doing (including trying to set forced parameterization at a database level), I decided to take a step back and check out the pre-requisites of forced parameterization at a database level. Books Online listed exceptions where parameterization could not be forced, so I went back and checked them all, even though my SQL batch seemed extremely simple. I found that there were 2 problems:
a) Deprecated query constructs
b) Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF
It turned out that the connections that the application was using were issuing SET ANSI_NULLS statements manually, just after they connected. This statement itself it deprecated meaning that parameterization was a no no, and ironically it meant that doubly since ANSI NULLS OFF also stops this occurring.
A little something to watch out for.