Update Statistics script

  • I am looking for a script where I can update the statistics of the objects in a database if only the last stat updates date was before any specific date. Any help would be greatly appreciated.

    Thank you.

  • SET NOCOUNT ON

    DECLARE @StatsToUpdate TABLE ( StatName NVARCHAR(MAX) )

    DECLARE @StatUpdating NVARCHAR(MAX)

    INSERT INTO @StatsToUpdate

    ( StatName

    )

    SELECT 'UPDATE STATISTICS dbo.' + OBJECT_NAME + ' ' + index_name

    FROM ( SELECT TOP 1

    OBJECT_NAME(A.object_id) AS Object_Name ,

    A.name AS index_name ,

    STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,

    DATEDIFF(d, STATS_DATE(A.OBJECT_ID, index_id),

    GETDATE()) DaysOld

    FROM sys.indexes A

    INNER JOIN sys.tables B ON A.object_id = B.object_id

    WHERE A.name IS NOT NULL

    AND DATEDIFF(d, STATS_DATE(A.OBJECT_ID, index_id),

    GETDATE()) >= 0

    ) StatsThatAreTooOld

    WHILE ( SELECT COUNT(*)

    FROM @StatsToUpdate

    ) > 0

    BEGIN

    SET @StatUpdating = ( SELECT TOP 1

    statname

    FROM @StatsToUpdate

    )

    EXECUTE sp_executesql @StatUpdating

    PRINT 'Done ' + @StatUpdating

    DELETE FROM @StatsToUpdate

    WHERE StatName = @StatUpdating

    END

    Not the most elegant. Simply looks at the stats and in this case returns a list of stats more than 0 days old and then carrys out an update stats statement

    Would need a little tweaking to fully meet your needs probably and there are probably better ways to do this.

  • If you're running SQL Server 2008R2 SP2, or SQL Server 2012 SP1, you can use the new statistics DMF sys.dm_db_stats_properties. I have a query for it on my blog: http://www.sqlskills.com/blogs/erin/new-statistics-dmf-in-sql-server-2008r2-sp2/

    Otherwise, you'll have to roll through DBCC SHOW_STATISTICS or use STATS_DATE as the previous poster mentioned.

    Hope that helps!

    Erin

  • This is what I use to do that same thing. It is used in a SSIS package and the results are held in an object variable then I loop through that variable executing an update stats command.

    select o.object_id,o.name,s.stats_id,s.name

    from sys.objects o

    inner join sys.stats s on o.object_id = s.object_id

    where o.type = 'U'

    and stats_date(o.object_id,s.stats_id) <= getdate()-20

    order by o.object_id,s.stats_id

  • Execute following ---

    use dbname

    go

    exec sp_updatestats

    (this sp will detect and automatically update only require)

  • bangsql - Friday, January 25, 2013 11:15 AM

    Execute following ---use dbnamegoexec sp_updatestats(this sp will detect and automatically update only require)

    Too good to be true
    https://sqlperformance.com/2013/07/sql-statistics/statistics-updates

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply