同样的查询+参数+每次清空计划缓存，直接写成T-SQL和写存储过程执行计划不一致的原因

Question

如图，单表查询，唯一涉及的字段CustomerId上有索引，客观条件都是一样的，每次也清空了缓存，参数也一样，全表统计信息也fullscan重建过

两个问题

1，为什么同样的查询，用T-SQL和将这个T-SQL封装成存储过程传参调用，执行计划就不一样了呢？

2，看截图2，为什么在编译值确定的情况下，依然用不到索引，在编译值确定的情况下(CustomerId = @p_CustomerId or @p_CustomerId is null)这种写法对编译生成执行计划的影响在哪里

Answer 1

Sounds parameter sniffing related. You can verify it by replacing parameter with local variable in sp.

Answer 2

Sounds parameter sniffing related. You can verify it by replacing parameter with local variable in sp.

already "EXEC DBCC FREEPROCACHE" everytime,this case is very simple,you could test in your local and verify by yourself

Answer 3

You let me test your problem MYSELF? 

Answer 4

You let me test your problem MYSELF? 

no,no,no,U misunderstand me

I mean that's not parameter sniff problem,I will test myself.

thank u very much

in addition,that's true  local variable problem,another question:why this " (CustomerId = @p_CustomerId or @p_CustomerId is null)"  way use index scan plan rather than index seek?

Answer 5

我建议分开写

if @p_CustomerId is null

select ...

else

select ...

你这种写法确实可能影响性能的。编译值确定，也只是计算执行成本，不会用这个值去判断逻辑表达式的。

---

想不想时已是想，不如不想都不想。

Answer 6

我建议分开写

if @p_CustomerId is null

select ...

else

select ...

你这种写法确实可能影响性能的。编译值确定，也只是计算执行成本，不会用这个值去判断逻辑表达式的。

---

想不想时已是想，不如不想都不想。

这种catch all queries的查询条件的解决方法还是有的，主要是我问的那种方式，为什么抑制了索引，没有找到一个完美的解释

后面也找到了，参考这个http://www.sommarskog.se/dyn-search-2008.html#BasicStaticSQL。

里面The History of Forced Recompilation 这一段有非常清楚的解释

因为研究这个问题我延伸了一下WITH RECOMPILE和OPTION(RECOMPILE)

http://www.cnblogs.com/wy123/p/6262800.html

谢谢大版主！