December 17, 2010 at 2:27 am
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.
December 17, 2010 at 2:58 am
28 views, no replies... I think that shows you probably should explain a bit better your question, with an example if possible.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
December 17, 2010 at 8:23 am
Yes, I too guess so....
I was trying to explain the following:
Say, i have a procedure proc
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=@param1
When i see the execution plan it uses tab3 for custerd scan
but 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=@param
Somewhere 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.
December 17, 2010 at 11:51 am
Gail has a couple of posts starting here: http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
This should help you understand what you are seeing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 17, 2010 at 7:19 pm
I 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.
December 21, 2010 at 6:12 am
I just wanted to share the below path too related the topic:
http://www.simple-talk.com/sql/performance/sql-server-statistics-problems-and-solutions/
December 21, 2010 at 6:37 am
Heres a link that might help you:
http://furrukhbaig.wordpress.com/2007/08/22/parameter-sniffing/
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
October 11, 2012 at 10:08 am
I 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).aspx
It might help you.
Regards,
Mahendra Jain
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply