August 29, 2018 at 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
August 29, 2018 at 4:57 pm
saum70 - Wednesday, August 29, 2018 11:58 AMHi,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
September 20, 2018 at 12:06 am
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply