none
Is there away we can differ auto stats update during ETL process? RRS feed

  • Question

  • Is there away we can differ the auto stats update for one particular table during its ETL?
    We have auto create and auto update statistics on for the database. We have a table with 10M rows and 300 columns and have lots of statistics on it. We load this table on nightly basis with truncate and insert with minimal-logging. I noticed it still take a longer to load, further digging into it found heavy usage of tempdb during the ETL thereby I narrowed to find it might be doing stats update. I dropped all stats on the table and ran the load which performed in half the time. Then again this table got stats build up - seems it is queried for multiple ways heavily. I m trying to think if I can differ stats update during the ETL job so that I can cover it within the time window? I m not allowed to touch any global setting.

    Thanks


    Mahesh

    Friday, November 3, 2017 1:25 AM

All replies

  • Hi Mahesh,

    Per your post, it seems that you don't want to update statistics of the table automatically during running the ETL job. For this purpose, you may set the asynchronous statistics update option to ON.

    With asynchronous statistics updates, queries compile with existing statistics even if the existing statistics are out-of-date; The Query Optimizer could choose a suboptimal query plan if statistics are out-of-date when the query compiles. Queries that compile after the asynchronous updates have completed will benefit from using the updated statistics.

    For more information, please see AUTO_UPDATE_STATISTICS_ASYNC(the content is under the heading "AUTO_UPDATE_STATISTICS_ASYNC")

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 3, 2017 2:23 AM
    Moderator