InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 22:30:28 GMT20http://www.sqlservercentral.com/Forums/Topic1036392-391-1.aspxI could not understand in much detail about your problem. But I think, it is related to Distributed Partitioned vie.Please go through the below link:http://msdn.microsoft.com/en-us/library/aa175250(v=sql.80).aspxIt might help you.Regards,Mahendra JainThu, 11 Oct 2012 10:08:44 GMTmahen83http://www.sqlservercentral.com/Forums/Topic1036392-391-1.aspxHeres a link that might help you:http://furrukhbaig.wordpress.com/2007/08/22/parameter-sniffing/Tue, 21 Dec 2010 06:37:38 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic1036392-391-1.aspxI just wanted to share the below path too related the topic: http://www.simple-talk.com/sql/performance/sql-server-statistics-problems-and-solutions/Tue, 21 Dec 2010 06:12:17 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1036392-391-1.aspxI should thank you to route me to the right place.So the point is that should not try the parameters to a variable in a procedure until we see a reason for the same like parameter sniffing;which will lead the optimizer not to choose the best plan as the values are unknown to the optimizer.Am i correct? if so, I learnt a good lesson.Fri, 17 Dec 2010 19:19:07 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1036392-391-1.aspxGail has a couple of posts starting here: [url]http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]This should help you understand what you are seeing.Fri, 17 Dec 2010 11:51:50 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic1036392-391-1.aspxYes, I too guess so.... I was trying to explain the following:Say, i have a procedure procCreate proc proc(@param bigint)AS declare @param1 bigint Set @param1 = @param Select <colums> From tab A inner join tab2 B on A.<col> = B.<col1> inner join tab3 c on b.<col> = c.<col1> where tab2=@param1 When i see the execution plan it uses tab3 for custerd scanbut if am not using the variable @param1, it uses clustered seek as below.Create proc proc(@param bigint)AS --declare @param1 bigint --Set @param1 = @param Select <colums> From tab A inner join tab2 B on A.<col> = B.<col1> inner join tab3 c on b.<col> = c.<col1> where tab2=@paramSomewhere i read/watched vedios on parameter sniffing, there a resolution as setting the params to a variable within the procedure and use the variable for the further operation. As I experienced an odd behaviour, I dont know why?will break the same?Please let me wherei would have gone wrong. or is there someone experienced so.Appreciate your feedbacks.Fri, 17 Dec 2010 08:23:20 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1036392-391-1.aspx28 views, no replies... I think that shows you probably should explain a bit better your question, with an example if possible.Fri, 17 Dec 2010 02:58:43 GMTRonald Hhttp://www.sqlservercentral.com/Forums/Topic1036392-391-1.aspx When am assigning a parameter of a procedure to a variable and use tha variable throguh out the procedure, am seeing the plan is not an optimal one as it uses the scan for big tables. However, it uses seek directly with passed parameter. Just conflicting the parameter sniffing resolution like "Assign to a variable and use the variable further". Help me to understand the situation.Fri, 17 Dec 2010 02:27:38 GMTsqlzealot-81