Is auto update statistics good For OLTP

  • saum70

    Right there with Babe

    Points: 739

    Hi,

    I wanted to know if setting Auto Update Statitics to TRUE will be good for OLTP environment. Millions of records are inserted in the transactional tables with 1000s of records within a second. What I am concerned ia thw lockimg of the objects, updating the stats thereby giving timeout error or eny other issues. How about Auto Update Statisics Async. Please guide

  • Eddie Wuerch

    SSChampion

    Points: 12259

    saum70 - Wednesday, August 29, 2018 11:58 AM

    Hi,

    I wanted to know if setting Auto Update Statitics to TRUE will be good for OLTP environment. Millions of records are inserted in the transactional tables with 1000s of records within a second. What I am concerned ia thw lockimg of the objects, updating the stats thereby giving timeout error or eny other issues. How about Auto Update Statisics Async. Please guide

    As a DBA of many multi-TB OLTP databases that crank through a billion+ transactions per day, I say: yes, absolutely to both Auto-Update Statistics and Auto-Update Statistics Async. I've run with both for years enabled on SQL2008R2 and a year and a half on SQL2016.

    I'm assuming you're running SQL 2008, and a large database. If so, I encourage you to review this Microsoft Support article on Trace Flag 2371 and AUTO_UPDATE_STATS: https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • saum70

    Right there with Babe

    Points: 739

    Hi Eddie,

    Thanks for your immediate response. Just to let you know that after reading your reply and confirming with the same in our environment, we are going ahead with all the three statistics i.e. Auto Update, Auto Create and Auto Update Async. We also confirmed that no blocking took place. Hopefully we should not face any issue in production environment.

    Regards,
    Saumik Vora

Viewing 3 posts - 1 through 3 (of 3 total)

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