0

I would like to know if we can update the statistics of an index that has been reorganized, using the IndexOptimize script. Indeed, an index that is reorganized needs to have its statistics updated. Is there a way to use IndexOptimize script to update the statistics of this reorganized index only? For instance, is there a flag to recognize the reorganized index in order to update the statistics on this flagged index? Thanks.

2

I take it that you are referring to Ola Hallengren's scripts.

I can't find any parameter combo that would do what you ask. It is a pretty reasonable thing to ask for, though. IndexOptimize is pretty much separate in the handling of defrag vs updstats. Your options, as far as I can see:

  • Modify the proc yourself. It is only TSQL, in the end. Add an UPDATE STATISTICS after it does index reorg.
  • Create a branch on GitHub and see if it is merged into the main bransch. Now others can enjoy your effort (like we all are enjoying Ola's effort).
  • Send an email to Ola and see if he feels like doing the change.
  • Schedule a separate job for statistics - but you won't get the coupling to the previous reorg.
  • Choose a different maint solution.
  • Measure whether you actually gain anything from defrag, and potentially focus on statistics update instead.

Possibly some more, but those are the ones I can think of right now. :-)

| improve this answer | |
  • Thank you very much for your precious advices :) – Nathalie Oct 22 at 7:04
0

Statistics are automatically updated when an index is rebuilt via ALTER INDEX ... REBUILD, however they are not automatically updated when an index is reorganized via ALTER INDEX ... REORG. Of note, ALTER INDEX ALL ... will only automatically update statistics for manually-created indexes - no system-created statistics objects get updated.

The Microsoft Docs says this about statistics:

When an index is created or rebuilt, statistics are created or updated by scanning all the rows in the table. However, starting with SQL Server 2012 (11.x), statistics are not created or updated by scanning all the rows in the table when a partitioned index is created or rebuilt. Instead, the Query Optimizer uses the default sampling algorithm to generate these statistics. To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

When an index is reorganized, statistics are not updated.

I have a script that can be used to intelligently update statistics on my blog. This is the script:

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:    2019-04-16
        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
        1.2        Excluded columnstore indexes
    */
    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
            AND NOT EXISTS ( --exclude columnstore indexes since they cannot have stats updates
                SELECT 1
                FROM sys.indexes i
                WHERE i.object_id = st.object_id
                    AND i.index_id = st.stats_id
                    AND (
                        i.type_desc = N'CLUSTERED COLUMNSTORE'
                        OR 
                        i.type_desc = N'NONCLUSTERED COLUMNSTORE'
                        )
                )
    ) 
    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 blog post has further details about the table you need to create, and has a SQL Server Agent job that you can configure to run on a schedule to automate the stats update process.

If you are using Ola Hallengren's IndexOptimize script, it automatically looks after statistics maintenance, if you add the UpdateStatistics = 'ALL' parameter.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.