Home Forums SQL Server 2005 Administering Log Backups don't keep up during weekend maintenance RE: Log Backups don't keep up during weekend maintenance

  • So back to the question about whether stats are being updated on indexes that were just rebuilt. The stats proc in use runs the 2 statements below (slightly modified here to make it more clear ). Now if modifiedRows shown below is changed by an index rebuild then we could be updating stats unnecessarily. Index updating occurs before stats.

    -- Make the queue of indexes to update:

    IF OBJECT_ID('tempdb..##updateStatsQueue') IS NOT NULL

    DROP table ##updateStatsQueue;

    SELECT schemas.name AS table_schema,

    tbls.name AS table_name,

    i.name AS index_name,

    i.id AS table_id,

    i.indid AS index_id,

    i.groupid AS groupid,

    i.rowmodctr AS modifiedRows,

    ( SELECT MAX(rowcnt)

    FROM sysindexes i2

    WHERE i.id = i2.id

    AND i2.indid < 2 ) AS rowcnt,

    STATS_DATE(i.id, i.indid) AS lastStatsUpdate,

    'False' AS Processed

    INTO ##updateStatsQueue

    FROM sysindexes i

    INNER JOIN SYSOBJECTS AS tbls ON i.id = tbls.id

    INNER JOIN SYSUSERS AS schemas ON tbls.uid = schemas.uid

    INNER JOIN INFORMATION_SCHEMA.TABLES AS tl ON tbls.name = tl.table_name

    AND schemas.name = tl.table_schema

    AND tl.table_type = 'BASE TABLE'

    WHERE i.indid > 0

    AND table_schema <> 'sys'

    AND i.rowmodctr > 0

    AND ( SELECT MAX(rowcnt)

    FROM SYSINDEXES i2

    WHERE i.id = i2.id

    AND i2.indid < 2 ) > 0

    go

    SELECT top 1 * FROM ##updateStatsQueue WHERE processed = 'False'

    order by abs(modifiedrows)/( cast( rowcnt as decimal ) + 0.01 ) desc,lastStatsUpdate