asked
Auto Update Statistics sample ratio
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).
I think it's less then 20%, because I've tried to update the stats manually with 20% and it takes longer time then automatic. But keep in mind that an index rebuild will also update the stats with 100% ratio. Rebuilding stats on a newly rebuilt index is contraproductive.
Now I have the answer to **WHEN** an auto update stats will occur. But my bigger concern is: Which sample ratio will SQL Server choose?
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 :)
When I look at the activities, I see loads and loads of CXPACKET waits. There's a suggestion to include MAXDOP parameter in UPDATE STATISTICS to avoid this - nine votes so far..
http://connect.microsoft.com/SQLServer/feedback/details/628971/add-maxdop-parameter-to-update-stats
That's what I thought too, but then I realized that UPDATE STATISTICS WITH FULLSCAN is doing a poor job on parallellizing and has no MAXDOP hint. So I'm ending up with UPDATE STATISTICS taking hours for a 50GB database.
Really!?! I'm using Ola's solution plus an additional step and I'm getting through 240 GB in 10 minutes... including index defragmentation.
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][1] 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. [1]:
http://ask.sqlservercentral.com/storage/temp/61-update+stats+test.txt
1 Person is following this question.
