rdrunner
asked on
Stored procedure... Parameter Problem... 500 Points :)
Hi
right now i am very confused.... We have a SP that does a lot of work and the exact SQL is irrelevant. Let me post the Headder 1st...
ALTER procedure ts_pts_freigabelist2 (@Von Datetime,@Bis Datetime, @NL Integer ) as
.....
This procedure returns between 100-2000 for an office(= @NL) between the 2 dates (@von = from) and (@bis = till) The procedure started to "misbehave" and the execution times kinda grew extremely. The regular timespan is 1-5 Seconds and when it misbehaves its like 30 Seconds to 10 Minutes!!! (yuck!!!)
I updated all statistics for all relevant indexes... No go...
I recompiled this SP... No go...
I set it to "with recompile"... No go...
Then i copied the whole SQL and assigned the values by hand in the QA...
(like this)
declare @Von Datetime,@Bis Datetime, @NL Integer
set @von = 'Jun 1 2004 12:00:00:000AM'
set @bis = 'Jun 30 2004 12:00:00:000AM'
set @NL = 11
(....)
Oh Wonder it worked... Slightly confused i changed the SP to work like this...
ALTER procedure ts_pts_freigabelist2 (@Von1 Datetime,@Bis1 Datetime, @NL1 Integer ) as
declare @Von Datetime,@Bis Datetime, @NL Integer
set @von = @von1
set @bis = @bis1
set @NL = @nl1
It absolutly makes no sense for me but it works... I am confused now... Please someone enlighten me why this actually works This small change makes the sp choose the right indexes again but i have no clue why... and i want to understand why this (senseless) change works
Ill Add another 500 P question for someone explaining it to me
right now i am very confused.... We have a SP that does a lot of work and the exact SQL is irrelevant. Let me post the Headder 1st...
ALTER procedure ts_pts_freigabelist2 (@Von Datetime,@Bis Datetime, @NL Integer ) as
.....
This procedure returns between 100-2000 for an office(= @NL) between the 2 dates (@von = from) and (@bis = till) The procedure started to "misbehave" and the execution times kinda grew extremely. The regular timespan is 1-5 Seconds and when it misbehaves its like 30 Seconds to 10 Minutes!!! (yuck!!!)
I updated all statistics for all relevant indexes... No go...
I recompiled this SP... No go...
I set it to "with recompile"... No go...
Then i copied the whole SQL and assigned the values by hand in the QA...
(like this)
declare @Von Datetime,@Bis Datetime, @NL Integer
set @von = 'Jun 1 2004 12:00:00:000AM'
set @bis = 'Jun 30 2004 12:00:00:000AM'
set @NL = 11
(....)
Oh Wonder it worked... Slightly confused i changed the SP to work like this...
ALTER procedure ts_pts_freigabelist2 (@Von1 Datetime,@Bis1 Datetime, @NL1 Integer ) as
declare @Von Datetime,@Bis Datetime, @NL Integer
set @von = @von1
set @bis = @bis1
set @NL = @nl1
It absolutly makes no sense for me but it works... I am confused now... Please someone enlighten me why this actually works This small change makes the sp choose the right indexes again but i have no clue why... and i want to understand why this (senseless) change works
Ill Add another 500 P question for someone explaining it to me
ASKER
Both response times where measured on a live system with about 500 users online and at about the same time.
Requesting the same data did not change anything so its also not a difference between chached and not cached data. Doing the querry in the QA will allways give me the "good" response times. The querry is also running at
"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
so its not that badly affected by locks. Both querries have the exact same querry text just once executed "plain" and once executed as "SP"
Hope this makes sense...
Requesting the same data did not change anything so its also not a difference between chached and not cached data. Doing the querry in the QA will allways give me the "good" response times. The querry is also running at
"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
so its not that badly affected by locks. Both querries have the exact same querry text just once executed "plain" and once executed as "SP"
Hope this makes sense...
ASKER
P.s.:
Hehe ok DarthMod... Wont post 1 K Questions again... Sorry about that but this drove me really nutz and i was working the whole day to pinpoint this freaking problem (This almost stopped our Main Production DB). Now i really want to know WHY this fix is working... :)
Hehe ok DarthMod... Wont post 1 K Questions again... Sorry about that but this drove me really nutz and i was working the whole day to pinpoint this freaking problem (This almost stopped our Main Production DB). Now i really want to know WHY this fix is working... :)
If you run the SQL that the proc runs outside of the proc with your values coded (not as parms) does the query run fast as expected? Try and let us know.
Sometimes you'll get screwed up plans with parms simply because SQL is guessing at the optimal plan.
What does the query plan look like when you execute the proc and it behaves slowly?
Sometimes you'll get screwed up plans with parms simply because SQL is guessing at the optimal plan.
What does the query plan look like when you execute the proc and it behaves slowly?
ASKER
If i run it with "coded" values it runs fine...
The querry plan is "huge" thats why i omitted the SQL here... (I querry several Tables and use several functions and the plan has like 40+ items) The thing i am really wondering about is how can i tell the Server to pick the same plan it uses when i "just" execute the querry...
Its currently back to a working condition but i have no clue WHAT i did... and i usually would slap someone if i see code like this ;)
ALTER procedure ts_pts_freigabelist2 (@Von1 Datetime,@Bis1 Datetime, @NL1 Integer ) as
declare @Von Datetime,@Bis Datetime, @NL Integer
set @von = @von1
set @bis = @bis1
set @NL = @nl1
Why is this forcing the Server to choose a different plan?
The querry plan is "huge" thats why i omitted the SQL here... (I querry several Tables and use several functions and the plan has like 40+ items) The thing i am really wondering about is how can i tell the Server to pick the same plan it uses when i "just" execute the querry...
Its currently back to a working condition but i have no clue WHAT i did... and i usually would slap someone if i see code like this ;)
ALTER procedure ts_pts_freigabelist2 (@Von1 Datetime,@Bis1 Datetime, @NL1 Integer ) as
declare @Von Datetime,@Bis Datetime, @NL Integer
set @von = @von1
set @bis = @bis1
set @NL = @nl1
Why is this forcing the Server to choose a different plan?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmmm...
Ill have to check on that. But this sounds exactly like my problem.
I dont want to force indexes on this querry since its well... kinda huge and the server usually gets it right so i guess i have to stick with this ugly 'set @von = @von1' solution.
If anyone knows some other good links about this then drop a line in here please... Ill leave this question open for a short while.
Thanks arbert.
P.s: Nice to see some other ppl are also using googels cache to get some "out of Date" Information.
Ill have to check on that. But this sounds exactly like my problem.
I dont want to force indexes on this querry since its well... kinda huge and the server usually gets it right so i guess i have to stick with this ugly 'set @von = @von1' solution.
If anyone knows some other good links about this then drop a line in here please... Ill leave this question open for a short while.
Thanks arbert.
P.s: Nice to see some other ppl are also using googels cache to get some "out of Date" Information.
Ya, since dforums changed their site about a month ago, it has been tough to get some good articles.....
Ya, I agree--forcing the query plan with a hint isn't a good idea. We've encountered this problem several times and actually resorted to dynamic sql--sometimes the local variable approach works--sometimes it doesn't.
Brett
Ya, I agree--forcing the query plan with a hint isn't a good idea. We've encountered this problem several times and actually resorted to dynamic sql--sometimes the local variable approach works--sometimes it doesn't.
Brett
I had a crazy thought about how to force recompile when WITH RECOMPILE does not work.
What if you change your procedure to do this:
ALTER procedure ts_pts_freigabelist2 (@Von Datetime,@Bis Datetime, @NL Integer ) as
select 1 colxyz into #txyz
SELECT ...... FROM #txyz, tbl2, .... WHERE colxyz = 0 AND ... rest of criteria
I tried it with a simple select with a parameterized query and SQL Profiler shows the procedure is getting recompiled every time it is invoked.
What if you change your procedure to do this:
ALTER procedure ts_pts_freigabelist2 (@Von Datetime,@Bis Datetime, @NL Integer ) as
select 1 colxyz into #txyz
SELECT ...... FROM #txyz, tbl2, .... WHERE colxyz = 0 AND ... rest of criteria
I tried it with a simple select with a parameterized query and SQL Profiler shows the procedure is getting recompiled every time it is invoked.
ASKER
My SP was already changed to use the variable aproach... and this helped...
I only posted since i wanted to know WHY this works. This was just extremely confusing for me.
I only posted since i wanted to know WHY this works. This was just extremely confusing for me.
ASKER
To recap... (In case the google link stops working ;) )
Cause :
Parameter sniffing causes a suboptimal (cough) execution plan... Can happen when a SP calls an inline function for example (my case) or executes another SP. Also assigning default values which are NOT normal for the SP might cause this (@von datetime = NULL) or changing this value if it is null inside the SP...
Solutions to this problem :
- with recompile (Might not work allways)
- assign the values to local variabels (Also might not work)
- Use dynamic SQL with execsql
Cause :
Parameter sniffing causes a suboptimal (cough) execution plan... Can happen when a SP calls an inline function for example (my case) or executes another SP. Also assigning default values which are NOT normal for the SP might cause this (@von datetime = NULL) or changing this value if it is null inside the SP...
Solutions to this problem :
- with recompile (Might not work allways)
- assign the values to local variabels (Also might not work)
- Use dynamic SQL with execsql
Yep, that about sums it up. Even with the recompile on the proc, we had very little luck with getting the correct plan (on some procs). That's the only reason we resorted to DynamSQL--it still was tons faster than using the wrong plan though....
Brett
Brett
Brett, I don't have any procedures to test with but the next time you run across one will you try my temp table idea and let me know what it does? It's not intuitive why a programmer would put that code in, but dynamic sql probably adds more complexity than that.
Yep, I have one I can go out and try it on (as soon as I find it :) )
why dont u just drop it and recreate, my problem was solve like that once.
if the response times are with the single user accessing the machine, need to dig it further...