SQL 2005 Update Stats taking longer than backup.

  • 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:

  • 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.

  • 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.

  • 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

  • Yes, both are selected to "yes" in the options for that database.

  • 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.

  • 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?

  • How big are some of the tables? If you are doing a full scan, it could take awhile on fairly large tables.

  • How long does it take if you just run sp_updatestats?

  • I rather not try in that it my affect our production environment.

  • 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.

  • Are your tables highly fragmented?

  • The tables should be because the update stats runs each night.

  • 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.

  • 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 17 total)

You must be logged in to reply to this topic. Login to reply