Stored proc takes more time on production
-
Hi,
Why a procedure which takes a few seconds to execute on our development or staging environment, can - sometimes - take more than 30 seconds on the production environment. Can anybody give any idea/suggestions how we could continue our investigations here? and any sequence of steps like looking if statistics are updated etc..
thanks in advance
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
I think you can check a few things:
1. Lock
2. Profiler
3. Execution Plan
It depends on what is in your stored proc.
Leo
-
How about there is simply a lot more data in production? But yes, it is of course possible that production is not optimized, but that is always a problem you must be on top of.
-
One other possibility is that production is mutliprocessor and development is single processor. If you have table variables, you can have problems in a multiprocessor environment as they don't allow parallel processing but the query optimiser doesn't realise this and gives an inefficient plan in consequence.
-
You might want to compare your objects in DEV with production. Sometimes, an index might be missing that could slow performances. Checking out the hardware (and what's running on them) between the two servers might be worthwhile.
Check out the execution plans in both DEV and PROD too.
-
DBCC FREEPROCCACHE
A.J.
DBA with an attitude
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply