绕过 SQL Server 2005 中的参数嗅探

发布于 2024-11-09 21:46:27 字数 580 浏览 1 评论 0原文

我看到人们建议将参数复制到局部变量，以避免在存储过程中进行参数嗅探。假设你有

CREATE PROCEDURE List_orders_3 @fromdate datetime AS
 DECLARE @fromdate_copy datetime
 SELECT @fromdate_copy = @fromdate
 SELECT * FROM Orders WHERE OrderDate > @fromdate_copy

（我从 http://www.sommarskog.se/query-plan 得到这个-mysteries.html 但我需要更多细节才能完全理解它）。

但这实际上对查询计划缓存和查询计划优化器做了什么？如果优化器确实没有对 @fromdate_copy 做出任何假设，那么为什么它不会缓存最有可能进行全表扫描的计划（因为它没有做出任何假设，它怎么可能生成其他任何内容））？

这种技术基本上就像“没有输入会运行良好，但没有输入也会运行得很糟糕”？

原文

I have seen people suggest copying the parameters to a local variable to avoid parameter sniffing in a stored proc. Say you have

CREATE PROCEDURE List_orders_3 @fromdate datetime AS
 DECLARE @fromdate_copy datetime
 SELECT @fromdate_copy = @fromdate
 SELECT * FROM Orders WHERE OrderDate > @fromdate_copy

(I got this from http://www.sommarskog.se/query-plan-mysteries.html but I need more details to understand it fully).

But what does this actually do to the query plan cache and query plan optimizer? If it is true that the optimizer makes no assumptions about @fromdate_copy, then why is it that it won't cache a plan that is most likely going to be a full table scan (since it makes no assumptions, how could it generate anything else)?

Is this technique basically like a "no inputs will run well, but no input will run terribly either" ?

分享到QQ

分享到微博