May 17, 2005 at 8:01 am
Am I correct in the following?
To ensure a database is fully optimised and ready for use, the following operations need to be carried out in sequence.
1. Execute "DBCC (REINDEX)" on each table in Db.
2. Execute "update statistics" on each table in Db.
3. Execute "sp_recompile" on each table in Db.
After doing this, I can be assured that:
1. All indexes are rebuilt
2. The statistics on each table are regenerated and up to date.
3. Each procedure will have had it's query plan regenerated with the most to date update index/stats information and so will execute as efficiently as it possible for it to do.
Comments / suggestions welcome!
Tom.
May 17, 2005 at 8:07 am
> 3. Each procedure will have had it's query plan regenerated with the most to date update index/stats information and so will execute as efficiently as it possible for it to do.
No, that is not correct. If you execute sp_recompile against a table then all procedures that reference that table will be recompiled next time they are executed.
Also, optimization is not a one-time job. Is there a specific scenario behind this question?
May 17, 2005 at 9:25 am
I had a sending failure, so if this appears twice please ignore the repeat.
I should have stated 3 more carefully. Yes, I am allowing for the recompile to happen the first time the sp is executed against the table.
I plan to break my optimisation plan into 2 parts, a one a week full optimisation of everything, and a smaller daily optimisation of a subset, basically all the crucial bottleneck tables in my various Dbs.
I wanted to ensure that my understanding of optimisation is correct, that if I execute the 3 steps above in sequence, whatever database tables I execute them on will be as optimised as it possible for them to be on the server side.
After I am assured of this, I will start to look at the application procedures....
Tom.
May 17, 2005 at 1:42 pm
> I wanted to ensure that my understanding of optimisation is correct, that if I execute the 3 steps above in sequence, whatever database tables I execute them on will be as optimised as it possible for them to be on the server side.
Yes, you are about fully correct in that. If you have that kind of maintenance window I would also suggest you make sure that the update stats samples all rows in the table. Of course if you are missing an index that would help some queries, or if you have too many indexes, you will not "be as optimised as it is possible", but for the existing structures this should do it.
May 17, 2005 at 2:05 pm
I've never used the "update statistics" command beyond just that.
How do I ensure that it samples all the rows in the table?
Tom.
May 17, 2005 at 3:19 pm
UPDATE STATISTICS tablename WITH FULLSCAN
Note however that I am only recommending this since I get the impression you have a huge maintenance window. On a large table this will take some time, but so will recreating the indexes for it. Giving performance advice without having seen the scenario is nigh impossible, only tips and ideas can be given.
May 18, 2005 at 1:26 am
Thanks for the clarifications Chris.
Tom.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply