Hard code vs Variable Query

TBoesken 2014-03-18 12:27:56

Hi All!
When I run this particular query with a hard coded value, I get the results back instantly. When I use a local variable, I get the results in about 10 seconds. My logical page reads go through the roof (180k vs 2) for a client table. Does anyone have any suggestions on how to deal with this? I think the issue is with parameter sniffing but in this case it is not turning out to be my friend 🙂

Dave Ballantyne 2014-03-18 12:33:22
There a few options around this issue:
OPTIMIZE FOR query hint
RECOMPILE query hint.

OPTIMIZE FOR will build a plan based on the specified values rather than those at build time.

RECOMPILE will do the same, but naturally recompile on each call, this will naturally degrade performance. The recompile time doesnt sound as if it is a issue here though YMMV

Aaron Bertrand 2014-03-18 16:06:05
Recompile will only degrade performance overall if the compile time is significant. If it adds 10 ms to every call and saves the occasional query from running 10 seconds, I'd say that's a win (it takes a lot of +10ms to offset that 10s).
Edward Norris 2014-03-18 12:35:50
First post to the forum, so I'm not used to how people operate…
When you run it in SSMS do you specificy dbcc dropcleanbuffers(); to clear all of the cache?

You also have a tremendous number of scan's happening…I'm not sure how big those tables are, but that could always contribute.

I have to admit, that this has always been a parameter sniffing issue whenever I encountered it. If it's a stored procedure, every once in awhile I have to specify a "with recompile" to get around it, when the standard parameter sniffing tricks do not work.

Aaron Bertrand 2014-03-18 16:07:05
You probably want OPTION (RECOMPILE) on the statement, not WITH RECOMPILE at the object level. There is a big difference in modern versions of SQL Server.
JasonRHall 2014-03-18 12:37:42
Maybe try OPTION (RECOMPILE)? It's hard to say with the anonymized plan, but that's one way to test your param sniffing theory.