Parameter issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Parameter issue

Parameter issue

Rate Topic

Display Mode

Topic Options

Author

Message

sqlzealot-81

Posted Friday, December 17, 2010 2:27 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 730, Visits: 1,740

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

Ronald H

Posted Friday, December 17, 2010 2:58 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, November 30, 2012 1:52 AM
Points: 1,116, Visits: 602

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

sqlzealot-81

Posted Friday, December 17, 2010 8:23 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 730, Visits: 1,740

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

Jeffrey Williams 3188

Posted Friday, December 17, 2010 11:51 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319, Visits: 9,217

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

sqlzealot-81

Posted Friday, December 17, 2010 7:19 PM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 730, Visits: 1,740

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

sqlzealot-81

Posted Tuesday, December 21, 2010 6:12 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 730, Visits: 1,740

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

sjimmo

Posted Tuesday, December 21, 2010 6:37 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:34 AM
Points: 2,865, Visits: 2,472

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

mahen83

Posted Thursday, October 11, 2012 10:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, October 13, 2012 8:30 AM
Points: 4, Visits: 14

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 »

Permissions