|
|
Posted by Henrik Fyhn on 07/07/06 14:30
I've seen this before and acutally it's quite common.
The key to your problem is different execution plans.
A SP can have several copies of a execution plan.
a) Different SET statements to the connection
b) You don't call the SP from QA with proper owner prefix (e.g. dbo)
c) In a multitple CPU environment you will have one scheduler (UMS) for
each SPID, and you might experience that you get the same exection plan
until your thread is closed.
Make sure that you update the statistics whenever you experience such
problems. The stats are stored in server, not in the databases.
So what do you do?
If a procedure gets slow, you can recompile the procedure with
sp_recompile. If this doesn't help, use DBCC FREEPROCCACHE and run the
procedure agin.
SP's are often recompiled in an OLTP environment. Common reason is
change in statistics. Sometimes the optimizer makes a poor choice in
execution plan due to variance in the parameteres it recevies when
recompiling. It's smart to look into what parameters are sent to the
procedure and see if there are great changes. Also pay attention to
complex procedures with if-else and case-statements. Keep it simple!
Regards,
Henrik
*** Sent via Developersdex http://www.developersdex.com ***
Navigation:
[Reply to this message]
|