Check Whether Update Statistics Required
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 28 Aug 2008
Many times I have been involved in writing scheduled batch tasks that manipulate large data volumes. Common types of tasks are archiving and migration, but ETL tasks can also deal with large data volumes.
Often the source/destination for the data is an existing table that is subject to ongoing changes. This can cause the performance of the batch to vary wildly, especially in data archiving tasks where deletions can invalidate index usage due to the volume of changes.
During a recent data migration project, rather than blindly issue an update statistics command for each iteration, I wrote this small function to allow the update statistics command to be invoked only when necessary.
The threshold for change is set at 10% as I found this to work for the task at the time, but it can easily be modified.