DB Options: - AUTO UPDATE STATS / ASYNC
In this video we are going to talk about auto update statistics. This is a feature in the database engine that automatically triggers a stat update when data has been updated beyond a threshold. In previous versions of SQL server this thresh hold was 500 rows followed by every 20% increase in the count of rows. Naturally this logic is flawed for large tables since it will increase the lag between stats updates. A more aggressive trigger has been implemented since 2016 where the square root for the count of rows multiplied by 1000 is used. This makes sure that the delays are fewer because the stats are updated in regular intervals.
Before we continue let’s look at why the stats need to be updated. The execution plan needs to know how many rows it is likely to fetch for a query in order to size memory , use the correct join hint , order decide to perform scan vs seek and many more. So it’s critical to have up to date counts. Think of it like money in your wallet. It makes sense to know exactly how much you have in it before you decide to go shopping.
Need more information? Visit https://www.enabledbusinesssolutions.com
To know more about Jayanth Kurup visit http://www.jayanthkurup.com
Jayanth is a Microsoft SQL Server trainer / consultant with over 15 years’ experience. He has worked with companies like Microsoft, IBM, HCL, Wipro, Well Fargo, Zeomega and many more. He is based out of Bangalore and a very active member of the SQL Server Community.
In this channel he covers complex topics related MS SQL Server , PowerBI , Azure , AWS , Powershell, SSIS , SSAS , SSRS , Machine Learning , AI , etc
You can also reach Jayanth via his LinkedIn profile below www.linkedin.com/in/jayanth-kurup
music from HAAWK