SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameter issue


Parameter issue

Author
Message
Latheesh NK
Latheesh NK
SSC Eights!
SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)

Group: General Forum Members
Points: 927 Visits: 2164
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.
Ronald H
Ronald H
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 620
28 views, no replies... I think that shows you probably should explain a bit better your question, with an example if possible.

Ronald Hensbergen

Help 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
Latheesh NK
Latheesh NK
SSC Eights!
SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)

Group: General Forum Members
Points: 927 Visits: 2164
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.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7771 Visits: 9971
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Latheesh NK
Latheesh NK
SSC Eights!
SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)

Group: General Forum Members
Points: 927 Visits: 2164
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.
Latheesh NK
Latheesh NK
SSC Eights!
SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)

Group: General Forum Members
Points: 927 Visits: 2164
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/
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3760 Visits: 2904
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
mahen83
mahen83
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 23
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search