8

I've got a SQL Server 2017 (CU9) database that is exhibiting some performance related issues that I believe have to do with the index statistics. While troubleshooting I discovered that the statistics had not been updated (meaning DBCC SHOW_STATISTICS would return all NULL values).

I executed UPDATE STATISTICS on the table affected and verified that SHOW_STATISTICS returned actual values at 4:00PM yesterday. This morning at 8:00AM the statistics were again empty (returning NULL values).

The client does have a maintenance job scheduled to run daily at 4:00AM which reindexes for the database followed by an execution of sp_updatestats against the entire database. I've verified that the statistics get updated at 4:00AM with a profiler trace.

I'm at a loss as to why the statistics would be empty, is it the maintenance job running at 4:00AM? Is there a bug I'm not aware of on this version of SQL Server?

Thanks in advance for your assistance.

MORE INFO:

  • Auto Update Statistics is enabled.
  • Auto Update Statistics Asynchronously is disabled.
  • Auto Create Incremental Statistics is disabled.

Reindexing Script (Obfuscated):

USE DBNAME;
DECLARE @CERTENG_Lock INT
DECLARE @WebSite_Control_ProcessRunning_Lock INT
DECLARE @WebSite_Control_Disabled_Lock INT
DECLARE @LogMessage VARCHAR(1024)

SELECT @CERTENG_Lock = Lock FROM application.CERTENG_Lock

SELECT @WebSite_Control_Disabled_Lock = MAX(CAST(Disabled AS INT)), 
       @WebSite_Control_ProcessRunning_Lock = MAX(CAST(ProcessRunning AS INT)) 
  FROM application.WebSite_Control 
 WHERE Webname = 'Reports'

IF(@CERTENG_Lock = 0 AND @WebSite_Control_Disabled_Lock = 0 AND 
@WebSite_Control_ProcessRunning_Lock = 0)
BEGIN
    EXECUTE Dba.ReIndex
END

ELSE
BEGIN
SET @LogMessage = 'The reindex job did not run because the following locks were set: '
IF(@CERTENG_Lock = 1)
BEGIN
    SET @LogMessage = @LogMessage + 'The CERTENG_Lock was set to 1;'
END

IF(@WebSite_Control_Disabled_Lock = 1)
BEGIN
    SET @LogMessage = @LogMessage + 'The WebSite_Control_Disabled_Lock was set to 1;'
END

IF(@WebSite_Control_ProcessRunning_Lock = 1)
BEGIN
    SET @LogMessage = @LogMessage + 'The WebSite_Control_ProcessRunning_Lock was set to 1;'
END

INSERT INTO [Dba].[ReindexLog] ([LogMessage]) VALUES (@LogMessage)

END

DBA.Reindex

USE [Database]
GO
/****** Object:  StoredProcedure [Dba].[ReIndex]    Script Date: 12/20/2018 11:15:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Create procedure to perform reindexing

ALTER PROCEDURE [Dba].[ReIndex] (
    -- Only rebuild if fragmentation is above ___
    @REBUILD_FRAGMENTATION_THRESHOLD FLOAT = 30,
    -- Or only reorganize if fragmentation is above ___
    @REORG_FRAGMENTATION_THRESHOLD FLOAT = 10
    )
AS
SET NOCOUNT ON;

DECLARE @WorkingId BIGINT, @ReindexId BIGINT, @Sql VARCHAR(2000);
DECLARE @TableId INT, @IndexId INT;
DECLARE @ExecutionTime DATETIME

SET @ExecutionTime = GETDATE()

-------------Identify tables------------------------------------------------------------
TRUNCATE TABLE Dba.ReindexList;

-- List all the tables and their indexes in the database by the number of rows
-- in order to do the largest tables first.
INSERT INTO Dba.ReindexList (SchemaName, TableName, IndexName, TableId, IndexId, IndexType, NumberOfRows)
SELECT s.NAME AS [SchemaName], t.NAME AS [TableName], i.NAME AS [IndexName], i.object_id, i.index_id, i.type_desc, p.row_count
  FROM sys.schemas AS s
 INNER JOIN sys.tables AS t
    ON t.schema_id = s.schema_id
 INNER JOIN sys.indexes AS i
    ON i.object_id = t.object_id
 INNER JOIN sys.dm_db_partition_stats AS p
    ON p.object_id = i.object_id
   AND p.index_id = i.index_id
-- Ignore heaps because they can't be rebuilt or reorganized
 WHERE i.type_desc != 'HEAP'
    -- Skip individual schemas owned by domain accounts
   AND charindex('\', s.NAME) = 0
    -- Skip DBA schema
   AND s.NAME != 'Dba'
 ORDER BY p.row_count DESC, s.NAME, t.NAME, i.index_id;

----------------Check fragmentation---------------------------------------------------
DECLARE
    -- Separate table to keep track of only the indexes that need to be now
    @FragmentationWorkingList TABLE (ReindexId BIGINT NOT NULL PRIMARY KEY CLUSTERED);

INSERT INTO @FragmentationWorkingList (ReindexId)
SELECT r.ReindexId
  FROM Dba.ReindexList AS r
-- Skip fragmentation check for this specific index or table?
  LEFT JOIN Dba.ReindexSetting AS st
    ON st.DatabaseName = db_name()
   AND st.SchemaName = r.SchemaName
   AND st.TableName = r.TableName
   AND st.IndexName IS NULL
  LEFT JOIN Dba.ReindexSetting AS si
    ON si.DatabaseName = db_name()
   AND si.SchemaName = r.SchemaName
   AND si.TableName = r.TableName
   AND si.IndexName = r.IndexName
 WHERE r.IsFragmentationChecked = 'N'
   AND r.IsReindexed = 'N'
    -- Index setting overrides table setting if both are specified
   AND coalesce(si.SkipFragmentationCheck, st.SkipFragmentationCheck, 'N') = 'N'
 ORDER BY r.ReindexId;

SELECT @ReindexId = min(w.ReindexId)
  FROM @FragmentationWorkingList AS w;

WHILE @ReindexId IS NOT NULL
BEGIN
    -- Pull IDs into variables because the physical stats DM function can't
    -- cross-apply values from a JOIN.
    SELECT @TableId = r.TableId, @IndexId = r.IndexId
      FROM Dba.ReindexList AS r
     WHERE r.ReindexId = @ReindexId;

    -- Load the fragmentation for each index individually
    -- with duration-tracking so we can figure out whether or not
    -- this is really worthwhile.
    UPDATE Dba.ReindexList
       SET FragmentationCheckStartTime = getdate()
     WHERE ReindexId = @ReindexId;

    UPDATE r
       SET Fragmentation = p.avg_fragmentation_in_percent
      FROM Dba.ReindexList AS r
    -- Use LIMITED for fastest scan
     INNER JOIN sys.dm_db_index_physical_stats(db_id(), @TableId, @IndexId, NULL, 'LIMITED') AS p
        -- Should only return one row for this index
        ON 1 = 1
     WHERE r.ReindexId = @ReindexId;

    UPDATE Dba.ReindexList
       SET IsFragmentationChecked = 'Y', FragmentationCheckEndTime = getdate()
     WHERE ReindexId = @ReindexId;

    SELECT @ReindexId = min(w.ReindexId)
      FROM @FragmentationWorkingList AS w
     WHERE w.ReindexId > @ReindexId;
END

------------------------------Reindex------------------------------------
DECLARE
    -- Separate table to keep track of only the indexes that need to be now
    @ReindexWorkingList TABLE (
    -- Order differently based on row count and fragmentation
    WorkingId BIGINT NOT NULL identity(1, 1) PRIMARY KEY CLUSTERED, ReindexId BIGINT NOT NULL
    );

INSERT INTO @ReindexWorkingList (ReindexId)
SELECT r.ReindexId
  FROM Dba.ReindexList AS r
-- Skip fragmentation check for this specific index or table?
  LEFT JOIN Dba.ReindexSetting AS st
    ON st.DatabaseName = db_name()
   AND st.SchemaName = r.SchemaName
   AND st.TableName = r.TableName
   AND st.IndexName IS NULL
  LEFT JOIN Dba.ReindexSetting AS si
    ON si.DatabaseName = db_name()
   AND si.SchemaName = r.SchemaName
   AND si.TableName = r.TableName
   AND si.IndexName = r.IndexName
 WHERE r.IsReindexed = 'N'
    -- Index setting overrides table setting if both are specified
   AND coalesce(si.SkipReindex, st.SkipReindex, 'N') = 'N'
    -- Process tables in order of the most fragmented, largest
   AND r.Fragmentation >= @REORG_FRAGMENTATION_THRESHOLD
 ORDER BY r.Fragmentation DESC, r.NumberOfRows DESC, r.ReindexId;

SELECT @WorkingId = min(w.WorkingId)
  FROM @ReindexWorkingList AS w;

WHILE @WorkingId IS NOT NULL
BEGIN
    SELECT @ReindexId = w.ReindexId
      FROM @ReindexWorkingList AS w
     WHERE w.WorkingId = @WorkingId;

    -- Skip index because of low fragmentation?
    IF @REORG_FRAGMENTATION_THRESHOLD > (
            -- Assume that an index is highly fragmented if the exact %
            -- wasn't calculated to save time
            SELECT isnull(r.Fragmentation, 100)
              FROM Dba.ReindexList AS r
             WHERE r.ReindexId = @ReindexId
            )
    BEGIN
        UPDATE Dba.ReindexList
           SET IsReindexed = 'Y', IsSkipped = 'Y', ReindexStartTime = getdate(), ReindexEndTime = getdate()
         WHERE ReindexId = @ReindexId;
    END
            -- Rebuild or reorganize...
    ELSE
    BEGIN
        -- Try/catch inside a loop causes slower performance, but reindexing
        -- should continue on the next index if an error occurs.
        BEGIN TRY
            -- Rebuild or reorganize?
            -- 1) Ignore heaps
            -- 2) Always rebuild a clustered index
            -- 3) Rebuild nonclustered if > __, otherwise reorganize it
            -- According to Kalen Delaney (http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/dd612296-5b3a-40f1-829f-c654b835efed),
            -- rebuild always updates statistics with FULLSCAN while reorgnize does not.
            SELECT @Sql = 'alter index [' + r.IndexName + '] on [' + r.SchemaName + '].[' + r.TableName + '] ' + 
                   CASE WHEN IndexType = 'HEAP'                               THEN 'rebuild'
                        WHEN IndexType = 'CLUSTERED'                          THEN 'rebuild'
                        WHEN Fragmentation > @REBUILD_FRAGMENTATION_THRESHOLD THEN 'rebuild'
                        ELSE 'reorganize; update statistics [' + r.SchemaName + '].[' + r.TableName + '] [' + r.IndexName + ']'
                    END +
                -- TODO: Handle partitions properly
                ';'
             FROM Dba.ReindexList AS r
            WHERE r.ReindexId = @ReindexId;

            UPDATE Dba.ReindexList
               SET ReindexStartTime = getdate(), Sql = @Sql
             WHERE ReindexId = @ReindexId;

            EXECUTE (@sql);

            UPDATE Dba.ReindexList
               SET ReindexEndTime = getdate(), IsReindexed = 'Y'
             WHERE ReindexId = @ReindexId;
        END TRY

        BEGIN CATCH
            UPDATE Dba.ReindexList
               SET ReindexEndTime = getdate(),
                -- Mark as reindexed to show that an attempt was made...
                   IsReindexed = 'Y', ErrorNumber = error_number(), ErrorMessage = error_message()
             WHERE ReindexId = @ReindexId;
        END CATCH
    END

    SELECT @WorkingId = min(w.WorkingId)
      FROM @ReindexWorkingList AS w
     WHERE w.WorkingId > @WorkingId;
END

INSERT INTO Dba.ReindexHistory (HistoryTime, TableId, IndexId, SchemaName, TableName, IndexName, IsClustered, IsReindexed, NumberOfRows, Fragmentation)
SELECT isnull(@ExecutionTime, getdate()), l.TableId, l.IndexId, l.SchemaName, l.TableName, l.IndexName, 
       CASE l.IndexType WHEN 'CLUSTERED' THEN 'Y'
                        ELSE 'N'
       END AS IsClustered, 
       l.IsReindexed, l.NumberOfRows, l.Fragmentation
  FROM Dba.ReindexList AS l
  LEFT JOIN Dba.ReindexHistory AS h
    ON h.HistoryTime = l.FragmentationCheckStartTime
   AND h.TableId = l.TableId
   AND h.IndexId = l.IndexId
 WHERE h.HistoryTime IS NULL
 ORDER BY l.FragmentationCheckStartTime, l.TableId, l.IndexId;

UPDATE: I disabled Auto Update Statistics for the database and manually updated the statistics yesterday. This morning they are still populated. I assume this means there's something bad happening within Auto Update.

  • 2
    This isn't some sort of trunc and load scenario is it? Typically when I've seen this in an environment they are doing a trunc and load with the auto update statistics setting off. – Zane Dec 20 '18 at 16:40
  • Auto Update Statistics is enabled for the database. Auto Update Statistics Asynchronously is disabled. Auto Create Incremental Statistics is disabled. – Tim Bytnar Dec 20 '18 at 16:43
  • 1
    This might be helpful - brentozar.com/archive/2018/09/… – Kin Shah Dec 20 '18 at 16:59
  • 5
    Side note, I'd really spend some time and consider using Ola's scripts. – scsimon Dec 20 '18 at 17:40
  • 1
    At this point - I would recommend setting up a server side to see what host/process is dropping stats. – Kin Shah Dec 20 '18 at 18:15

Your Answer

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

Browse other questions tagged or ask your own question.