Posted by wizofaus on 01/12/07 03:18

Erland Sommarskog wrote:

> When you set a database to forced parameterisation, SQL Server will
> auto-parameterise all statements (with some exceptions documented in
> Books Online); normally it only auto-parameterise very simple queries.
> In the case of Mgmt Studio it's reallly a go-faster switch.
>
> So dismissing caching of parameterised queries can be a serious mistake.
> But it is certainly true that there are situations where parameter
> sniffing can be a problem. If it is possible for you to tell in the
> appliocation "this is an odd value that needs a special plan", then
> you can modify the query text by adding a redudant condition like
> "AND 1 = 1". Actually as you have found, changing "BY" to "by" or even
> adding extra spaces help. This is because the lookup in the cache
> is done on a hash without first collapsing spaces or parsing the
> query text.
>
Thanks...one of the most helpful replies I've had on usenet for some
time now!

The problem is that it's pretty hard for me to know that a value is
"odd". In this case, like I said the query in this case is

SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0

Where @0 is actually a value from a list that is obtained from
elsewhere (not the database). It loops through this list, calling the
same query for each one.

Now, I suppose I could first do

SELECT Count(*) FROM MyTable WHERE MyKey = @0

and determine if the number was very low, and if so, de-parameterize it
or add a space or whatever, but then this second query would
potentially suffer from the same problem.

I suppose another alternative is to build another query first

SELECT MyKey, Min(MyValue) FROM MyTable GROUP BY MyKey

then use this to obtain the minimum value for each key, but there's
only so much time I can spend rewriting queries to side-step quirky
performance issues (the application has many many ad-hoc queries,
nearly all parameterized on the same key, so they are all prone to the
same problem).

BTW, this is under SQL server 2000. I've yet to determine if this
particular problem does actually exist under 2005, which is what we're
using for the production environment. Will definitely do that before I
waste too much more time on it.

[Back to original message]