June 22, 2008 at 8:21 pm
Hello all,
I'm using a SQL Server 2000, and I'm having the following problem:
After running the sp_updatestats procedure the performance its reduced. For example, a query that usually takes a few seconds runs for 5 or 6 minutes.
I ran a index rebuild, and then the response times are within normal ones. But when the sp_updatestats ran, the performance was decreased.
Now I am not running the update stats and it is working fine.
Is it possible that the sp_updatestats breaks the indexes or something?
I don't know why this is happening,
Do you have any suggestion?
Thanks in advance
June 22, 2008 at 9:48 pm
sp_updatestats, by default - updates statistics with a default sampling rate. You can try using the resample option, but that will only work if an auto update has not occurred since the last time you updated stats, since the auto update also uses a default sampling rate.
When you rebuild an index - the statistics are updated with a full scan and that is most likely the reason you are seeing good performance after an index rebuild.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 23, 2008 at 8:01 am
Hi, thanks for your reply.
I have many tables with auto stats activated
Maybe I should do the following:
1)Disable autostats.
2)rebuild indexes once time
3)Schedule a weekly job that update statistics with full scan.
What do you think?
Thanks
June 23, 2008 at 9:15 am
1) It is only a rare situation where you should turn off auto stats features
2) I would have a job that runs at an appropriate interval (which depends on your data modification patterns for each table) and does a manual stats update using full scan or some other high sampling rate.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 24, 2008 at 8:32 am
Now, I have a maintenance plan that rebuild the indexes, so if the rebuild process update the stats with full scan, there is no need to run the sp_updatestats
Thanks
December 11, 2008 at 11:17 am
Is this statement correct. If you have a maintenance process that rebuilds indexes and then another process that will calculate and rebuild stats, should autostats be turned off?
December 12, 2008 at 2:21 am
TheSQLGuru (6/23/2008)
1) It is only a rare situation where you should turn off auto stats features
can you explain the rare situation you are referring to?
We have our auto stats turned off and have automated jobs to keep them up to date as having the stats of a table with over 100mil rows updating during work hours is not an option.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
April 30, 2009 at 12:30 pm
hi i have a related question,
i have a stored procedure which according to table fragmentation does a rebuild index to only those tables, is it necessary to run the sp_updatestats for the other tables that the indexes were not rebuild?
April 30, 2009 at 1:10 pm
is your auto update statistics on... then I think no need to update the statistics manually...
if you are already having reindex job which is running depending upon the fragmentation... you are good?
Are you facing any performance issue on your system?
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
April 30, 2009 at 7:47 pm
I had the maintenance job to rebuild but it was taking around 6 hours to run so instead I created a stored procedure to reindex/reorg based of fragmentation level and then a second job to run update stats, this is a production db highly used standard edition, what is your opinion?
May 1, 2009 at 8:54 am
Hi,
See It all depends, for how many database, how big is the database(s), how much is the activity
generally I would go as follows:
If its a big database
>> I would reindex once in a week.
>> Daily defrage.
>> Daily update stats.
>>Full Backup weekly.
>>Deferencial bkp daily.
>>Tlog bkp hourly.
Its all depends on the database and transactions... also how do you have your maintaince window... also before implementing test it completley.
HTH.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
May 5, 2009 at 1:44 pm
thank you, i read in some articles that using the rebuild indexes from the maintenance task wizard is not good in prod heavily used dbs with the standard edition, is that true?
May 5, 2009 at 1:54 pm
thank you, i read in some articles that using the rebuild indexes from the maintenance task wizard is not good in prod heavily used dbs with the standard edition, is that true?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply