ScottPletcher (12/3/2012)
Hmm, something seems odd.SQL should be able to easily do "simple parameterization" -- the old name, "auto-parameterization", seems clearer to me -- on that query. That is, SQL should automatically convert the query to make the value in the WHERE clause a parameter so a shareable plan is created.
Are you changing the case (upper/lower) of the query each time?(??)
Or making some other noticeable change to the query each time?
But if there were significant changes then it's likely the hash for the query (which is what this must be using) would be different. They're the same.
You could try forced parameterization. You could also try using Optimize for Ad Hoc. You certainly sound like a candidate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning