locked
update stats taking long time RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    does any one have any smart update stats script which is implemented in your environment.  I see lot of scripts online, but want to know somebody's personal experience who have used any script like this in their production environment and is running successfully.

    Currently we are running update stats with full scan on all tables and it takes huge amount of time to finish, I want to reduce this time.

    Wednesday, April 20, 2016 2:44 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    There are different opinions on using FULLSCAN. I for one have been burnt by sampled statistics why I tend to favour them. On the other hand, yes, they do take a long time. What you can consider is
    UPDATE STATISTICS WITH FULLSCAN, INDEX

    to only perform the update on indexes. This is a lot faster than updating statistics for non-indexed columns, not the least because SQL Server scans the table once for each column.

    You may also investigate on which tables you really need fullscan, but this is certainly tedious to find out.

    Wednesday, April 20, 2016 9:44 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    There are different opinions on using FULLSCAN. I for one have been burnt by sampled statistics why I tend to favour them. On the other hand, yes, they do take a long time. What you can consider is
    UPDATE STATISTICS WITH FULLSCAN, INDEX

    to only perform the update on indexes. This is a lot faster than updating statistics for non-indexed columns, not the least because SQL Server scans the table once for each column.

    You may also investigate on which tables you really need fullscan, but this is certainly tedious to find out.

    Wednesday, April 20, 2016 9:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Take a look at the maintenance solution by Ola Hallengren (https://ola.hallengren.com/) It's a solution that helps in backups, dbcc en index and statistics maintenance. It's free! easy to setup with a lot of tweaking possibilities.

    updating statistics on large table can take a long time and for larger tables it can be better to use a sample percentage. Are you updating all statistics on all tables? maybe you can do al little tweaking to skip certain tables, use a sample on the larger tables, and only update the changed statistics.

    Thursday, April 21, 2016 9:06 AM