Obviously I could be wrong but thought the downside of Parameter sniffing could mean the same plan being used for two queries that return different size result sets. i.e the plan is built on a SEEK so all queries use a SEEK where a SCAN might be more optimal depending on what parameter is passed in?
Yes, that's correct
In my case the query plans are totally different.
Is this possible with parameter sniffing?
You have one plan when the query is compiled and executed with the same parameter value and a different one when the plan is compiled with one parameter value and executed with a different one.
Hence, you have a situation where the query is executed with a plan appropriate for a different parameter value (where the compile and execute parameters are different. That's bad parameter sniffing.
The fact that the plans are not staying in cache long and getting compiled often and hence you're seeing different plans at different times is another matter
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass