April 7, 2004 at 6:59 am
Hello all,
I recently upgraded a server from 7 to 2000, using the upgrade wizard.
Now All my databses have 'auto create statistics' and 'auto update statistics' set to on however I am experinceing performance problems and when I check the for the last time statistics were updated a value of null is recieved ie never created. Any ideas?
Thanks
Shiraz
April 8, 2004 at 12:40 am
From memory, I think you have to drop and recreate all indexes, otherwise performance will be poor when you transfer a db from 7 to 2000
April 8, 2004 at 2:07 am
I have a weekly reindex job, is that not enough?
April 8, 2004 at 2:37 am
it won't hurt to manually update stats after rebuilding the indexes .. try sp_updatestats.
I only ever upgraded a sql server once - never again - I always do a new build and copy the databases to the the new build, by backup/restore or attach/detattach.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 8, 2004 at 2:38 am
btw. the auto create and auto updatestats shouldn't do any harm but don't assume they negate the need to do a manual update
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 8, 2004 at 4:34 am
There is absolutely no fix rule as how often the db should be reindexed - all depends on what is the volume of inserts/updates taking place on a daily basis. Bear in mind that running DBCC dbreindex will hold locks for a long time and thus will block any running queries or updates. To find out how often this should be run and when is the best time to run this, you will need to monitor the daily activities on the database e.g. you can easily check and compare the statistics by DBCC showcontig (Find out more on BOL) It will be time-consuming but believe me - it is all worth it.
colin.leversuch-roberts is correct, it won't hurt to manually update stats after rebuilding the indexes. However, there is the argument for and against "'auto create statistics' and 'auto update statistics' set to ON'". Imagine the auto update takes place during peak hours, this will surely have impact on the performance. Once you have gathered enough information/data on your database typical daily activities, it may be worth considering scheduling the statistic updates during less busy hours, e.g. evening - just an idea.
In case you have not been informed, avoid running the tests on the production database.Test, test, test and experiment. Good luck!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply