September 6, 2006 at 4:47 pm
Recently I noticed strange things with SQL and I hope that someone could explain me what is the couse of that. By the way, production server have 4 HT processors.
For example let say that I have some procedure that takes some number of input parameters:
create procedure spTest @x int=0, @y int=0, @z int=0
as
..... some code here ...
When I execute that sp it takes for example 30 minutes to execute and SQL prepare execution plan on the way that there is no parallel execution so I have only one thread opened for that SPID.
But, when I write procedure like this:
create procedure spTest @x1 int=0, @y1 int=0, @z1 int=0
as
declare @x int,@y int,@z int
set @x=@x1
set @y=@y1
set @z=@z1
..... same code from first example ...
or like this if I dont have input parameters
create procedure spTestas
declare @x int,@y int,@z int
set @x=0
set @y=0
set @z=0
..... same code from first example ...
execution takes less than a minute and there is parallel execution of procedure.
So, does anybody knows why SQL makes different execution plans for first and second example?
Regards
September 6, 2006 at 6:12 pm
From my understanding, if you have a stored procedure that takes parameters one of two things will happen.
1. If you do not modify the parameters in the procedure, then the optimizer can work with them fine.
2. If you DO modify the parameters in the procedure, then the optimizer has issues and can't.
I may be wrong here, as I am going from an incomplete statement I recently overheard. But this does explain what your seeing.
Anyone else, please provide your more knowledgable answer!
I am humble enough as not to think I have the correct one, just my thoughts......
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 8, 2006 at 4:43 pm
Well, you are right about SQL optimizer and parameters, but I don't modify them in procedure and that is what I don't understand. I think that there is some bug in SQL. First I thought that there is difference becouse variables declared in procedure and parameters are not in same memory space but that will be difference of maybe 5%. But what is strange to me is that there are totally different execution plans. I take some test and procedure with parameters is more that 100 times slower than prosedure that involves locally declared variables. Finally I rewrite procedure so on the begining I declare local variables and assign them values from parameters and everithing work fine, but still I dont understand why SQL could not make good execution plans. Then I thought that maybe somewhere is problem with this procedure so I take another one and rewrite it and also I got improvments in time. From now I will write procedures on this way but still I would like to know if this is bug in SQL or I am doing something wrong.
Thanx.
September 8, 2006 at 5:01 pm
I'm sure if you post your procedure, someone on here could give yo a better answer and perhaps find the problem.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 9, 2006 at 9:15 pm
just search for "parameter sniffing" on these forums
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply