none
update statistics takes too long

    Question

  • SQL Server 2008 R2

    One database is 1.08TB.

    I create "update  statistics" of this database from maintenace plan. Then run "update statistics" from maintenace plan.

    Update Statistics Task---

    Scan type:Full scan

    Update: All existing statistics

    "update  statistics" has been taking almost 10 hours. And "update  statistics" still running.

    What should I do? Will anything be hurt?

    Wednesday, January 01, 2014 1:06 AM

Answers

  • Kill it.  Nothing will be hurt.

    Change the full scan to a 1% sample for your largest table(s) and restart.

    Databases much over about 500gb are too big to do full scans on, and building from samples works just as well anyway.

    Josh


    Wednesday, January 01, 2014 1:57 AM
  • SQL Server 2008 R2

    One database is 1.08TB.

    I create "update  statistics" of this database from maintenace plan. Then run "update statistics" from maintenace plan.

    Update Statistics Task---

    Scan type:Full scan

    Update: All existing statistics

    "update  statistics" has been taking almost 10 hours. And "update  statistics" still running.

    What should I do? Will anything be hurt?

    Its quite possible for Update stats to take long time for 1 TB database. I would suggest you to run stats update for particular table instead or for Index.Running update stats for such huge database will always result in complexity.

    As suggested by Josh probably making sample 20 % would also be helpful

    You can also take help from Ola hellengren script for stats updation.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, January 01, 2014 4:48 AM
  • For such big databases I would recommend looking into Ola's great script to maintain statistics and indexes

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 01, 2014 6:16 AM
  • Yes, you can kill it without fear. As Josh says, you will be left without unupdated statistics.

    This query can give you some idea of the progress:

    SELECT o.name, s.name AS stats_name, isindex = CASE WHEN i.name IS NOT NULL THEN 1 ELSE 0 END,
           p.rows, stats_date(s.object_id, s.stats_id) AS stats_date
    FROM   sys.stats s
    LEFT   JOIN sys.indexes i ON s.object_id = i.object_id
                             AND s.name      = i.name
    JOIN    sys.objects o ON s.object_id = o.object_id
    JOIN   (SELECT object_id, rows = SUM(rows)
            FROM   sys.partitions
            WHERE  index_id IN (1, 0)
            GROUP  BY object_id) AS p ON p.object_id = o.object_id
    WHERE  o.type = 'U'
    ORDER  BY stats_date

    The function stats_date returns when a statistics last was updated. I've included the number of rows in the table to give some estimate how long a statistics update on that table takes. There is also a flag isindex - non-indexed statistics takes longer time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 01, 2014 10:27 PM

All replies

  • Kill it.  Nothing will be hurt.

    Change the full scan to a 1% sample for your largest table(s) and restart.

    Databases much over about 500gb are too big to do full scans on, and building from samples works just as well anyway.

    Josh


    Wednesday, January 01, 2014 1:57 AM
  • SQL Server 2008 R2

    One database is 1.08TB.

    I create "update  statistics" of this database from maintenace plan. Then run "update statistics" from maintenace plan.

    Update Statistics Task---

    Scan type:Full scan

    Update: All existing statistics

    "update  statistics" has been taking almost 10 hours. And "update  statistics" still running.

    What should I do? Will anything be hurt?

    Its quite possible for Update stats to take long time for 1 TB database. I would suggest you to run stats update for particular table instead or for Index.Running update stats for such huge database will always result in complexity.

    As suggested by Josh probably making sample 20 % would also be helpful

    You can also take help from Ola hellengren script for stats updation.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, January 01, 2014 4:48 AM
  • UPDATE STATISTICS doesn't causing blocking so it's OK to run as long as it's not causing IO bottleneck to your production workload. Ideally you should do it for the table where a lot of data is getting change. doing it on big static table won't do anything meaningful.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, January 01, 2014 5:02 AM
  • For such big databases I would recommend looking into Ola's great script to maintain statistics and indexes

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 01, 2014 6:16 AM
  • In addition to the recommendation to use Ola Hallengren's scripts: When you rebuild an index, statistics are updated with fullscan anyway, so there is little point to do it again.

    Now, what takes most of the time are the statistics for the non-indexed columns, as such statistics requires a full table scan. However, it is disputable whether these statistics are critical enough in general to warrant a full scan.

    The built-in maintenance plans don't have much of these considerations, and that is why you get your hands on Ola's scripts.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 01, 2014 9:37 AM
  • Hi,

    This "update  statistics"job created from maintenance plan is still running. It has been 24 hours.

    Want to double confirm with everybody:I can kill this "update  statistics"job and database will not have any hurt, correct? I have to be careful.

    Another question:

    It has been running for 24 hours. As 1.08TB database, full scan to update statistics--how long could it take? Any way for me to know how many hours left?Will "update statistics"job cause any permformance issue? if I still let it go until it is finished no matter how long it takes. 

    Thanks


    • Edited by bestrongself Wednesday, January 01, 2014 3:27 PM
    Wednesday, January 01, 2014 3:09 PM
  • It's hard to guess how long it will take, I saw one 500gb database where the default plan ran twelve hours and only finished about 10%, so you do the math. 

    As databases get larger you need to focus the updates where needed and learn about the sampling options.

    Killing the job will leave some old statistics in place, nothing worse - I hope even whichever table it is running when killed simply keeps the old ones, it doesn't leave them blank.

    Balmukund says it's all non-blocking so you could let it run, but it will slow everything else down by continually clearing the RAM buffers (unless it's smart and you have the Enterprise level scan optimizations?), and of course by simply consuming IO and CPU resources as well.  Or just wait and see what people say!

    Josh

    Wednesday, January 01, 2014 5:00 PM
  • "I hope even whichever table it is running when killed simply keeps the old ones, it doesn't leave them blank."

    If Leave them blank, what could happen?Anything will be hurt and anything I need to do?

    Wednesday, January 01, 2014 7:19 PM
  • "I hope even whichever table it is running when killed simply keeps the old ones, it doesn't leave them blank."

    If Leave them blank, what could happen?Anything will be hurt and anything I need to do?

    The worst that *could* happen is that the compiler/optimizer wouldn't have those statistics to look at and might not generate the best plans.

    The fix would be to recreate those statistics.

    But I'm reasonably sure it never happens, that everything is done wrapped in transactions, so that what happens is they are not "blank" they are just the old ones kept because the new ones never completed.  That *still* may not generate the best plans, but it's probably better than blank!

    (a further problem is that even brand new statistics on very large tables, do not always lead to the best plans, there can be granularity issues in teh data that get a lot worse as the scale of the database grows).

    It's all pretty robust.

    What  you really have is a bit of a project to figure out how this should be done going forward.  Rank the tables by size and activity, see what kind of index maintenance you are already doing independent of update statistics, and come up with update statistics scheduled appropriately.

    And use the sample option, and don't be afraid of using very small samples!  You can even check out the results, run for different sample sizes and display the results, copy them side by side into a spreadsheet, convince yourself it's the right thing to do.

    Josh

    Wednesday, January 01, 2014 7:38 PM
  • Yes, you can kill it without fear. As Josh says, you will be left without unupdated statistics.

    This query can give you some idea of the progress:

    SELECT o.name, s.name AS stats_name, isindex = CASE WHEN i.name IS NOT NULL THEN 1 ELSE 0 END,
           p.rows, stats_date(s.object_id, s.stats_id) AS stats_date
    FROM   sys.stats s
    LEFT   JOIN sys.indexes i ON s.object_id = i.object_id
                             AND s.name      = i.name
    JOIN    sys.objects o ON s.object_id = o.object_id
    JOIN   (SELECT object_id, rows = SUM(rows)
            FROM   sys.partitions
            WHERE  index_id IN (1, 0)
            GROUP  BY object_id) AS p ON p.object_id = o.object_id
    WHERE  o.type = 'U'
    ORDER  BY stats_date

    The function stats_date returns when a statistics last was updated. I've included the number of rows in the table to give some estimate how long a statistics update on that table takes. There is also a flag isindex - non-indexed statistics takes longer time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 01, 2014 10:27 PM