How to Update Statistics
-
Posted on June 20, 2009 by Derek Dieter
There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table. [cc lang=”sql”] EXEC sp_updatestats [/cc] The other way, is to use the UPDATE STATISTICS command. This command gives much better granularity of control: [cc lang=”sql”] — Update all statistics on a table UPDATE STATISTICS Sales.SalesOrderDetail — Update a specific index on a table UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail — Update one column on a table specifying sample size UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT [/cc] Using update statistics can give you the granularity of control to only update the out of date statistics, thus having less impact on your production system. The following script updates all out of date statistics. Set the @MaxDaysOld variable to the number of days you will allow the statistics to be out of date by. Setting the @SamplePercent variable to null will use the SQL Server default value of 20,000 rows. You can also change the sample type to specify rows or percent. [cc lang=”sql”] DECLARE @MaxDaysOld int DECLARE @SamplePercent int DECLARE @SampleType nvarchar(50) SET @MaxDaysOld = 0 SET @SamplePercent = NULL –25 SET @SampleType = ‘PERCENT’ –‘ROWS’ BEGIN TRY DROP TABLE #OldStats END TRY BEGIN CATCH SELECT 1 END CATCH SELECT RowNum = ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1)) ,TableName = OBJECT_SCHEMA_NAME(st.object_id) + ‘.’ + OBJECT_NAME(st.object_id) ,StatName = st.name ,StatDate = ISNULL(STATS_DATE(object_id, st.stats_id),1) INTO #OldStats […]
Continue reading ...
