--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.
--Taken from http://sqlserverplanet.com/dba/update-statistics
DECLARE @MaxDaysOld INT
DECLARE @SamplePercent INT
DECLARE @SampleType nvarchar(50)
SET @MaxDaysOld = 10
SET @SamplePercent = 25 --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
FROM sys.stats st WITH (nolock)
WHERE DATEDIFF(DAY, ISNULL(STATS_DATE(object_id, st.stats_id),1), GETDATE()) > @MaxDaysOld
ORDER BY ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))
DECLARE @MaxRecord INT
DECLARE @CurrentRecord INT
DECLARE @TableName nvarchar(255)
DECLARE @StatName nvarchar(255)
DECLARE @SQL nvarchar(MAX)
DECLARE @SampleSize nvarchar(100)
SET @MaxRecord = (SELECT MAX(RowNum) FROM #OldStats)
SET @CurrentRecord = 1
SET @SQL = ''
SET @SampleSize = ISNULL(' WITH SAMPLE ' + CAST(@SamplePercent AS nvarchar(20)) + ' ' + @SampleType,N'')
WHILE @CurrentRecord <= @MaxRecord
BEGIN
SELECT
@TableName = os.TableName
,@StatName = os.StatName
FROM #OldStats os
WHERE RowNum = @CurrentRecord
SET @SQL = N'UPDATE STATISTICS ' + @TableName + ' [' + @StatName + ']' + @SampleSize + ';'
PRINT @SQL
EXEC sp_executesql @SQL
SET @CurrentRecord = @CurrentRecord + 1
END
-- Clears the procedure cache for the entire server
DBCC FREEPROCCACHE;
-- Update all usage in the database
DBCC UPDATEUSAGE (0);
