July 2, 2025 at 2:04 pm
Hi,
we are working on low latency OLTP system. About 100000 rows per second gets inserted into one table. for better performance when querying the data on real time basis or when one of the components or entire system restarts, we thought to keep auto create and update statistics ON.
Kindly give your inputs if it is prudent to keep it ON.
Regards,
Saumik
July 2, 2025 at 4:30 pm
My recommendation is to leave it on. Statistics are used to build execution plans. If the statistics are stale or missing, then execution plans can be inefficient.
The auto-update updates the statistics based on percentage of data changed after it gets above a certain threshold. Depending on your bulk insert process (do you truncate then reload the data for example), it may make more sense to update statistics manually once the bulk insert completes.
On top of this, if you are looking for performance tweaks, which side do you want them on? The data load OR querying the data while the bulk insert is happening? I ask because there are tricks to both sides but they both come with limitations. For the data load, using minimal logging will help but it impacts your RPO. For data querying, indexes can help but will hurt bulk load times and isolation levels can help reduce blocking at the cost of seeing either snapshot data (and increased disk usage) or uncommitted data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 3, 2025 at 7:28 am
Hello Brian!
The bulk insert is continues. The table gets truncated only at the end of the business date. Insertion of the data is most important. Our requirement is that just in case if one of the component needs to be restarted, it reads data from this table which has millions of records. The stored procedure returns around 60 columns with those records. And there are couple of procedures that refer this heavy loaded table. These procedures are very minimalistic and we have done all the optimization related process.
Since component restart at earliest possible time is priority, we thought to keep Auto Statistics update ON but it should not block the insertion or give any latency.
Thanks and Regards,
Saumik
July 3, 2025 at 12:31 pm
Did you test having
ALTER DATABASE [YourDb] SET AUTO_UPDATE_STATISTICS_ASYNC ON
ref: Alter Database SET options
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply