February 8, 2006 at 6:07 am
Hi friends... i am writing you for another doubt...
I have a developer which requires the execute of sp_recompile for all the SP in his database every day or four times per week. He saids that running this recompilation all the performance problems was solved. I am sure this is not true, but i dont understand a lot SQL and iam a newbie with this. I want to explain that this issue is a problem of code and not of the SQL engine. A lot of databases run in this server (over 100, with more load) without any problems.
So, i know that you must run sp_recompile when you make a lot of modifications over tables and structures that a SP uses. is this Ok?
When is necessary to run Sp_recompile? When not? when can cause performance problems? I mean, when you run sp_recompile, all the SP were marked for recompilation on next run, in runtime (is Ok, no?) So, this can produce some performance problems for the overhead of recompiling all SP??
hanks for help...
February 8, 2006 at 10:38 am
sp_recompile can be necessary when the first run of the stored procedure is with atypical values which may be bad for subsequent runs.
sp_recompiles only targets one object. All objects that depend on it will be recompiled on the next run.
Normally sql server is intelligent enough to recompile given the statistics are up to date.
Is the option auto create statistics turned on for the database?
You can use
for a first review of the quality of the stored procedures.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply