Statistics update job

SQL Server statistics are extremely important for good query performance since they provide invaluable guidelines that allow the query optimizer to pick an optimal plan. Out-of-date or non-existent statistics objects can cause excessive I/O, poor memory utilization, and spills to tempdb, which eventually result in poor client performance.

By default, SQL Server automatically manages statistics objects by updating them on-the-fly when it detects a statistically significant portion of the data in the affected table has been modified. For many databases, this functionality works wonders, however for larger tables where the number of modifications doesn’t trigger the automatic stats updates, you may want to manage stats updates pro-actively. This post provides some T-SQL code and a SQL Server Agent job that can be used to maintain statistics.

I use a stored procedure that is marked as a system-object to enable it to be called from the context of any user database. The stored procedure code:

USE master;
IF EXISTS (
    SELECT 1
    FROM sys.schemas s
        INNER JOIN sys.objects o ON s.schema_id = o.schema_id
    WHERE s.name = 'dbo'
        AND o.name = 'dba_update_all_stats'
    )
BEGIN
    DROP PROCEDURE dbo.dba_update_all_stats;
END
GO
CREATE PROCEDURE dbo.dba_update_all_stats
(
    @min_hours_since_last_update int = 0
    , @show_cmd bit = 1
    , @debug_only bit = 0
)
AS
BEGIN
    /*
        Updates statistics for all objects in the current database.
        Date:    2015-11-25
        By:        Max Vernon

        NOTE: Stored Proc must be created in the [master] database, 
        and must be marked as a "system procedure" to enable proper
        functionality.

        EXEC sys.sp_MS_marksystemobject dbo.dba_update_all_stats; 

        Version History:
        1.0        Initial Version
        1.1        Reduced noise messages

    */
    SET NOCOUNT ON;
    SET ANSI_WARNINGS ON;

    DECLARE @cmd nvarchar(MAX);
    DECLARE @ObjectID int;
    DECLARE @StatsID int;
    DECLARE @SchemaName sysname;
    DECLARE @ObjectName sysname;
    DECLARE @StatsName sysname;
    DECLARE @StatsDate datetime;
    DECLARE @StartTime datetime;
    DECLARE @EndTime datetime;
    DECLARE @Rows int;
    DECLARE @RowsSampled int;
    DECLARE @ModificationCounter int;
    DECLARE @DatabaseName sysname;
    DECLARE @Action varchar(50);
    SET @DatabaseName = DB_NAME();

    SET @cmd = '';

    DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY
    FOR 
    WITH StatsDetails AS
    (
        SELECT ObjectID = o.object_id
            , SchemaName = sch.name
            , ObjectName = o.name
            , StatsName = st.name
            , StatsID = st.stats_id
            , StatsDate = dsp.last_updated
            , Rows = dsp.rows
            , RowsSampled = dsp.rows_sampled
            , ModificationCounter = dsp.modification_counter /* number of modifications to the stat's 
                                                                leading column that have been modified
                                                                since the last stats update
                                                             */
            , UpdateStatement = 'UPDATE STATISTICS ' + quotename(sch.name) 
                + '.' + quotename(o.name) 
                + '(' + quotename(st.name) + ') WITH FULLSCAN' 
                + CASE WHEN st.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'
        FROM sys.schemas sch 
            INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
            INNER JOIN sys.stats st ON o.object_id = st.object_id
        OUTER APPLY sys.dm_db_stats_properties(o.object_id, st.stats_id) dsp
        WHERE o.is_ms_shipped = 0
    ) 
    SELECT sd.ObjectID
        , sd.StatsID
        , sd.SchemaName
        , sd.ObjectName
        , sd.StatsName
        , sd.UpdateStatement
        , sd.StatsDate
        , sd.Rows
        , sd.RowsSampled
        , sd.ModificationCounter
    FROM StatsDetails sd
    ORDER BY sd.SchemaName
        , sd.ObjectName
        , sd.StatsID;

    OPEN cur;

    FETCH NEXT FROM cur
    INTO @ObjectID
        , @StatsID
        , @SchemaName
        , @ObjectName
        , @StatsName
        , @cmd
        , @StatsDate
        , @Rows
        , @RowsSampled
        , @ModificationCounter;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @show_cmd = 1
        BEGIN
            PRINT ('Processing Stats: ' + @SchemaName + '.' + @ObjectName + '.' + @StatsName);
        END
        SET @Action = '';
        IF COALESCE(@Rows, 0) = 0
        BEGIN
            /* 
                sys.dm_db_stats_properties may return NULL values if the stats
                have never been updated.
                Get the current row count for the object.
            */
            SET ANSI_WARNINGS OFF;
            SELECT @Rows = SUM(ips.record_count)
            FROM (
                SELECT record_count = ips.record_count
                FROM sys.dm_db_index_physical_stats(DB_ID(), @ObjectID, NULL, NULL, 'SAMPLED') ips
                UNION ALL SELECT 0 /* ensure NULL is never returned to @Rows */
            ) ips
            SET ANSI_WARNINGS ON;
        END
        IF COALESCE(@Rows, 0) > 0 
            AND ((@StatsDate IS NULL) OR DATEDIFF(HOUR, @StatsDate, GETDATE()) > @min_hours_since_last_update)
        BEGIN
            SET @StartTime = GETDATE();

            /* 
                only update the statistics if rows have been modified.
               @ModificationCounter will be NULL if the statistics
               object has never been updated via UPDATE STATISTICS
            */

            IF @show_cmd = 1 AND COALESCE(@ModificationCounter, 1) > 0 
            BEGIN
                PRINT (CHAR(9) + 'Last updated ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');
                PRINT (CHAR(9) + 'Modifications since last stats update: ' + CONVERT(VARCHAR(50), @ModificationCounter));
                PRINT (CHAR(9) + 'Will update stats using:');
                PRINT (CHAR(9) + @cmd);
            END

            IF COALESCE(@ModificationCounter, 1) = 0 
            BEGIN
                SET @Action = 'ZeroModificationsSinceLastUpdate';
                IF @show_cmd = 1
                BEGIN
                    PRINT (CHAR(9) + 'Zero modifications since last update on ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');
                END
            END

            IF @debug_only = 0 AND COALESCE(@ModificationCounter, 1) > 0 
            BEGIN
                EXEC sp_executesql @cmd;
                SET @Action = 'Updated';
            END
            SET @EndTime = GETDATE();

            IF @debug_only = 0
            BEGIN
                SELECT @StatsDate = COALESCE(dsp.last_updated, GETDATE())
                    , @Rows = COALESCE(dsp.rows, 0)
                    , @RowsSampled = COALESCE(dsp.rows_sampled, 0)
                    /*
                        Leave this out since it will be zero at this point.  
                        The value from prior to executing UPDATE STATISTICS
                        shows the number of rows modified since the last time
                        stats were updated, and is useful to save in the 
                        dbo.StatsHistory table; the below code clobbers that
                        value.
                        , @ModificationCounter = COALESCE(dsp.modification_counter, 0)
                    */
                FROM sys.schemas sch 
                    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
                    INNER JOIN sys.stats st ON o.object_id = st.object_id
                OUTER APPLY sys.dm_db_stats_properties(o.object_id, st.stats_id) dsp
                WHERE sch.name = @SchemaName
                    AND o.name = @ObjectName
                    AND st.name = @StatsName;

                INSERT INTO master.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
                VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
            END
            ELSE
            BEGIN
                SET @Action = 'DebugMode';
                INSERT INTO master.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
                VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
            END
        END
        ELSE
        BEGIN
            IF COALESCE(@Rows, 0) = 0
            BEGIN
                IF @show_cmd = 1 
                BEGIN
                    PRINT (CHAR(9) + 'Contains zero rows.  Stats will not be updated.');
                END
                SET @Action = 'ContainsZeroRows';
                INSERT INTO master.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
                VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
            END
            ELSE
            BEGIN
                IF @show_cmd = 1 
                BEGIN
                    PRINT (CHAR(9) + 'Last updated ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');
                    PRINT (CHAR(9) + 'SKIPPING');
                END
                SET @Action = 'UpdatedTooRecently';
                INSERT INTO master.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
                VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
            END
        END

        IF @show_cmd = 1 
        BEGIN
            PRINT (''); /* add a blank line between output rows */
        END    

        FETCH NEXT FROM cur
        INTO @ObjectID
            , @StatsID
            , @SchemaName
            , @ObjectName
            , @StatsName
            , @cmd
            , @StatsDate
            , @Rows
            , @RowsSampled
            , @ModificationCounter;
    END

    CLOSE cur;
    DEALLOCATE cur;
END
GO
EXEC sys.sp_MS_marksystemobject @objname = 'dbo.dba_update_all_stats', @namespace = NULL; 
GO

The procedure above stores statistics history in a table named dbo.StatsHistory. That table is defined as:

CREATE TABLE dbo.StatsHistory
(
    StatsHistoryID int NOT NULL
        CONSTRAINT StatsHistory_X1_pkc
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StatsHistoryDate datetime NOT NULL
        CONSTRAINT StatsHistory_StatsHistoryDate_DC
        DEFAULT (GETDATE())
    , DatabaseName sysname NOT NULL
    , SchemaName sysname NOT NULL
    , ObjectName sysname NOT NULL
    , StatsName sysname NOT NULL
    , UpdateDate datetime NULL
    , UpdateDurationMs int NULL
    , Rows int NOT NULL
    , RowsSampled int NULL
    , ModificationCounter int NULL
    , Action varchar(50) NOT NULL
) WITH (COMPRESSION = PAGE)
ON [PRIMARY];

This code will create a SQL Server Agent job to run the stats update job:

USE [msdb]

BEGIN TRANSACTION;
DECLARE @ReturnCode int;
SET @ReturnCode = 0;
IF NOT EXISTS (
    SELECT name 
    FROM msdb.dbo.syscategories 
    WHERE name = N'Database Maintenance' 
        AND category_class=1
    )
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
END

DECLARE @jobId binary(16);

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name = N'Update Stats'
    , @enabled = 1
    , @notify_level_eventlog = 3
    , @notify_level_email = 2
    , @notify_level_netsend = 0
    , @notify_level_page = 0
    , @delete_level = 0
    , @description = N'No description available.'
    , @category_name = N'Database Maintenance'
    , @owner_login_name = N'sa'
    , @notify_email_operator_name = N'DBA'
    , @job_id = @jobId OUTPUT;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
    , @step_name = N'Run UPDATE STATISTICS'
    , @step_id = 1
    , @cmdexec_success_code = 0
    , @on_success_action = 3
    , @on_success_step_id = 0
    , @on_fail_action = 2
    , @on_fail_step_id = 0
    , @retry_attempts = 0
    , @retry_interval = 0
    , @os_run_priority = 0
    , @subsystem = N'TSQL'
    , @command = N'EXEC dbo.dba_update_all_stats;'
    , @database_name = N'<database_name_here>' --modify this to point to your database
    , @flags = 0;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
    , @start_step_id = 1;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION
GOTO EndSave

QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
EndSave:
GO
</database_name_here>