Hi! I'm preparing an implementation based on Ola Hallengrens IndexOptimize procedure. When it comes to updating statistics, I'm trying to find out some details about how Auto Update Statistics works in SQL Server. I want to decide whether to use NORECOMPUTE or not for some tables and, and if it's worthwhile spending resources on using FULLSCAN when updating (will SQL Server ruin the statistics by automatically updating statistics with a much lower sample rate?) Therefore I've been searching for two answers: - What's the default sample rate when SQL Server performs automatic update of statistics? - **WHEN** will SQL Server perform an automatic update of statistics? Environment is SQL Server 2005 and SQL Server 2008 (not R2).
The automatic update will occur, in general, when 20% of your data has been changed. For very large tables - that's a lot of data. If you're scheduling at a reasonable interval you may actually preempt the auto updates and not have to worry whether SQL Server chooses a different scan rate or not - I can't recall it at the moment. I haven't found updating statistics to be overly resource intensive nor time consuming so you may be over-thinking this :)
SQL Server updates statistics when a new rows is inserted to an empty table. When a table with < 500 rows gets 500 or more rows\changes. for table with more than 500 records, if 500 + 20% of the row changes. Missed to mention, Rebuilding an index will also update statistics.
I've made some tests about auto update stats and realized that auto update stats is not triggered based on the insert of the data. It's triggered by the select of the data when 20% of the data has changed, but there are some cases when it's not triggered, like when you are using a cached execution plan. You can use the [attached script] to find out when the stats are updated or not. Try change the procedure by removing the recompile statement and execute. You can also do the insert, select and get the stats date in the while loop to see the differences. If you don't use the recompile statement, you'll get a plan that thinks you only have one record instead of 100 000 records. :