Update Out Of Date SQL Server Statistics Query

--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);