Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parameter issue Expand / Collapse
Author
Message
Posted Friday, December 17, 2010 2:27 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 731, Visits: 1,997

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.
Post #1036392
Posted Friday, December 17, 2010 2:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,127, Visits: 603
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
Post #1036413
Posted Friday, December 17, 2010 8:23 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 731, Visits: 1,997
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.
Post #1036564
Posted Friday, December 17, 2010 11:51 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 4,388, Visits: 9,506
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1036676
Posted Friday, December 17, 2010 7:19 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 731, Visits: 1,997
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.
Post #1036826
Posted Tuesday, December 21, 2010 6:12 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 731, Visits: 1,997
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/
Post #1037661
Posted Tuesday, December 21, 2010 6:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
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
Post #1037667
Posted Thursday, October 11, 2012 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 12, 2014 4:22 AM
Points: 4, Visits: 15
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
Post #1371645
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse