Getting Around Parameter Sniffing in SQL Server 2005

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" ?

---

Actually, you need to assign a default variable to the @fromdate_copy field that you declare, so that when the query engine looks at the query itself, it bases a plan on the value that is 'hard-coded' - but instead, when the query actually gets executed, it gets executed with the value being passed in and switched..

Ken Henderson (the Guru himself) explained this in great detail: http://blogs.msdn.com/b/khen1234/archive/2005/06/02/424228.aspx

If you can, read his books - they offer a plethora of information about sql server internals: http://www.amazon.com/Gurus-Guide-Server-Architecture-Internals/dp/0201700476/ref=pd_bxgy_b_text_c

I'm not sure if he has anything written for the newer versions, but some of the fundamentals haven't changed that much...