sqldba20 (3/8/2011)
Dave, can you be more specific on which underlying design is wrong and which sort of problem I will be fighting?
Erm, this sort of problem you have posted about 😉
If you were to post the view definition we would easily be able to see this is an EAV (Entity , Attribute , Value) system. http://en.wikipedia.org/wiki/Entity-attribute-value_model
These are bad. Here's quite a nice write up on that http://weblogs.sqlteam.com/davidm/articles/12117.aspx. Thomas Kejser of sqlcat stated at sqlbits a while back,that a majority of performance issues they see (cant remember the exact number) are due to EAV designed systems.
Anyway back to the problem in hand....
Parameter sniffing goes some way to explaining what has happened here , but is not the whole story.
Parameter sniffing problems are caused when 'bad' values get applied to an existing plan in cache, causing that plan to run slow. Simplified, google for a more full explanation.
These plans are radically different, though both are 'good enough' and have similar io costs. The issue is probably the difference between estimated and actual rows returned. If you post the actual plans (as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ ) that will probably confirm that statement.
Why is there a difference between actual and estimated ? , first stop are statistics ,are they out of date ? , use UPDATE STATISTICS to rebuild them.
Post the View definition , table DDL and actual plans and ill see if i can expand further.