By far, the single biggest causes of parameter sniffing is when SQL Server has to:

  • Choose between an index seek + key lookup versus a table scan, or
  • Choose between two different indexes on the same table, or
  • Choose which table to process first in a join

Let’s see which ones we can reduce with index expansion and tuning.

Demo Script