July 7, 2009 at 11:27 am
SQL Gurus:
I have a aproximatly 25 GB SQL 2005 database that takes 41 minutes to make a full backup and 46 minutes to update the statistics (all existing statistics).
Is this correct? Should I be updating each night?
Your help will be apprecieated.
Dave :crazy:
July 7, 2009 at 12:12 pm
huh, that is a little strange. How are you performing the stats update?
I would probably just ensure that the database options 'auto update stats' and 'auto create stats' are enabled. for the most part SQL does a pretty good job of maintaining statistics itself.
July 7, 2009 at 12:12 pm
huh, that is a little strange. How are you performing the stats update?
I would probably just ensure that the database options 'auto update stats' and 'auto create stats' are enabled. for the most part SQL does a pretty good job of maintaining statistics itself.
July 7, 2009 at 12:20 pm
It had me scratching my head too. They are being performed throught the maintenance plan. I have it set to update user databases "all existing stats". I'll check if update auto stats is currently enabled.
Thanks,
Dave
July 7, 2009 at 12:25 pm
Yes, both are selected to "yes" in the options for that database.
July 7, 2009 at 12:29 pm
How many tables in the database? I have a database with over 50,000 tables and the update stats takes awhile even though many of the tables are empty.
July 7, 2009 at 12:38 pm
There are no more than 200 tables. I believe what is causing the update stat to take unusually long time to run stems from the "auto update stats" and "create stats" options.
Am I in the ball park?
July 7, 2009 at 12:56 pm
How big are some of the tables? If you are doing a full scan, it could take awhile on fairly large tables.
July 7, 2009 at 12:56 pm
How long does it take if you just run sp_updatestats?
July 7, 2009 at 12:59 pm
I rather not try in that it my affect our production environment.
July 7, 2009 at 1:01 pm
Some of the tables are fairly big I'm assuming if it a 25 gb database. I am assuming it is doing a full scan. It doesn't state what type of update it does in the maintenance plan.
July 7, 2009 at 1:26 pm
Are your tables highly fragmented?
July 7, 2009 at 1:28 pm
The tables should be because the update stats runs each night.
July 7, 2009 at 2:10 pm
Not necessarily ... stats can update regardless of the fragmentation levels, and regardless of whether the stats need to be updated.
To generate stats the engine needs to read the table, which may not be in contiguous disk locations - the drive head will have to keep moving around if the table is fragmented. In contrast a database backup just sweeps through the database files. This is a very simplified explanation, but it could account for the stats taking much longer; also bear in mind that there is more involved in stats updates than just copying pages to another file.
You might want to look at the scripts section of this site to find one that will identify whether your tables need defragmentation. You should also consider whether you really need to update all stats nightly ... if you have autoupdate stats on then they will be accurate to a degree, which is usually sufficient. If not then identify which ones need more frequent updates and just do those as appropriate.
July 7, 2009 at 2:16 pm
Thanks, Matt. I've already taken the update stat out of the maintenance plan. However, I want to research the cause of it.
Thanks again.
Dave
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply