March 15, 2004 at 10:32 am
We are managing some databases containing tables into which up to 100 000 rows are inserted weekly and got complaints about performance of some queries. We now decided to run update statistics every evening. Does one need to also run sp_Recompile and sp_refreshview or are these automatically refreshed when update stats is run?
Also, is it recomended to turn ON the db option Auto create stats so that we can see where there is a lack of stats on some tables? We currently have auto create stats and auto update stats turned off.
March 15, 2004 at 11:19 am
Yes, You should turn on the atuo create stats and on nightly basis run the update statistics job. You do not need to run sp_refreshview if you are including the tables and view in part of the update statistics job.
sp_recompile is not require, because SQL Server recompiles stored procedures automatically when it is advantageous to do so. As long as you have the most updated statistic, SQL will determine that for you.
DBA/OLAP Developer -
San Francisco, CA
March 16, 2004 at 6:23 am
Question: if I set up a job to run sp_updatestats, will this significantly impact performance of my server or network? I'm scheduling the job to run at 2 am when nobody is on the system, but our tape backup job runs at night and that's more important.
Dana
Connecticut, USA
Dana
March 22, 2004 at 11:15 pm
I would turn Auto Stats OFF and have a job scheduled to run at a quiet time to update them. If Auto stats is on it could be running while your users are querying your database leading to slower execution times.
Dana, I would schedule your updatestats job to run before your backup. If you run it at the same time as your backup, both jobs will probably take longer and in the event of a restore, your backup will not have up to date statistics.
Cheers,
Angela
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply