Hard code vs Variable Query
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 🙂
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
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.