What’s the Index Rebuild and Reorganization Frequency Kenneth?

Thank you Ola Hallengren

I, like so many of my peers, am a big fan of Ola Hallengren.  If there was the Nobel Prize for SQL he would have undoubtedly received the award by now.

For those of you unfamiliar with Sir Hallengren I invite you to visit his little corner of the Internet and sample his SQL Server Maintenance Solution.  A one-stop shop for backups, DBCC CHECKDB, index maintenance, and statistics updating.  Ola’s solution also has a benefit of logging the actions it undertakes for you.  It’s because of this feature that I’m here today.

If it Smells Like a Duck and Feels Like a Duck…

In other words, trust your instincts…
Recently we had a vendor – one well-known for manufacturing both medical information systems and bad code – come to us with the following recommendation to make their cumbersome row-based process run faster overnight.  By faster, they were hoping for under 8 hours.  Their recommendations were to increase the minimum query memory setting and reduce the fill factor on all indexes to 60%.  They also wanted to fudge around with the network packet size as well but even they shrugged that off after they thought about it for a while.  The query memory setting was shot down immediately.  I informed them that SQL will already dynamically give larger queries more RAM LUVIN’ if necessary and that all this would do would be to allocate more memory to smaller queries that were not the underlying issue – if anything it led to the possibility of more memory being diverted from their problem process.

I was also prepared to shoot down the request to set all index fill factors to 60%, but I wanted to dazzle them with facts, because I knew from experience logic wasn’t going to work with them.  That is where Ola’s output, coupled with a dip into the SQL metadata I love so much.  The results came together like peanut butter & chocolate, peas & carrots, Sonny & Cher and Crazy & Beautiful.

Returning Index Maintenance Frequency from Ola Hallengren’s Maintenance Solution Output

The following query provides nice output for looking at rebuild and reorganization activity.  I clean up the underlying file in Ola’s solution, dbo.CommandLog, every 30 days so you’ll see that the 31 count on reorganizations is a little scary – not to say an index being rebuilt almost nightly (24 times) isn’t frightful either!

--============================================================================
-- INDEX REBUILD COUNT LAST 31 DAYS
--============================================================================
CREATE TABLE ##Index_History
    (
        the_database sysname,
        the_schema sysname,
        the_object sysname,
        the_index sysname,
        index_type VARCHAR(13),
        fill_factor tinyint,
        rebuild_count INT NULL,
        reorg_count INT NULL
    );
 
CREATE TABLE ##Indexes
    (
        the_schema sysname,
        the_object sysname,
        the_index sysname,
        fill_factor tinyint
    );
 
INSERT INTO ##Indexes
    (
    the_schema
    , the_object
    , the_index
    , fill_factor
    )
 
SELECT OBJECT_SCHEMA_NAME(SO.object_id, db_id('<database__name, , FOO>')) AS the_schema
    , SO.name AS the_object
    , SI.name AS the_index
    , SI.fill_factor
FROM <database__name, , FOO>.sys.objects SO
    INNER JOIN <database__name, , FOO>.sys.indexes SI
        ON SO.object_id = SI.object_id
WHERE SI.index_id > 0;
 
INSERT INTO ##Index_History
         (the_database
        , the_schema
        , the_object
        , the_index
        , index_type
        , fill_factor
        , rebuild_count
        , reorg_count
         )
 
SELECT C.DatabaseName
    , C.SchemaName
    , C.ObjectName
    , C.IndexName
    , CASE C.IndexType
        WHEN 1 THEN 'Clustered'
        ELSE 'Non-Clustered'
    END AS IndexType
    , IX.fill_factor
    , COUNT(C.ID) AS rebuild_count
    , 0 AS reorg_count
FROM iDBA.dbo.CommandLog C 
    LEFT JOIN ##Indexes IX ON C.SchemaName = IX.the_schema
        AND C.ObjectName = IX.the_object
        AND C.IndexName = IX.the_index
WHERE C.CommandType = 'ALTER_INDEX'
    AND C.ObjectType = 'U'
    AND C.Command LIKE '%REBUILD%'
    AND C.DatabaseName = '<database__name, , FOO>'
GROUP BY C.DatabaseName
    , C.SchemaName
    , C.ObjectName
    , C.IndexName
    , IndexType
    , IX.fill_factor;
 
--============================================================================
-- INDEX REORGANIZE (ONLY) COUNT LAST 31 DAYS
--============================================================================
INSERT INTO ##Index_History
         (the_database
        , the_schema
        , the_object
        , the_index
        , index_type
        , fill_factor
        , rebuild_count
        , reorg_count
         )
 
SELECT C.DatabaseName
    , C.SchemaName
    , C.ObjectName
    , C.IndexName
    , CASE C.IndexType
        WHEN 1 THEN 'Clustered'
        ELSE 'Non-Clustered'
    END AS IndexType
    , IX.fill_factor
    , 0 AS rebuild_count
    , COUNT(C.ID) AS reorg__count
FROM iDBA.dbo.CommandLog C 
    LEFT JOIN ##Indexes IX ON C.SchemaName = IX.the_schema
        AND C.ObjectName = IX.the_object
        AND C.IndexName = IX.the_index
    LEFT JOIN ##Index_History IH
        ON C.DatabaseName = IH.the_database
            AND C.SchemaName = IH.the_schema
            AND C.ObjectName = IH.the_object
            AND C.IndexName = IH.the_index
WHERE C.CommandType = 'ALTER_INDEX'
    AND C.ObjectType = 'U'
    AND C.Command LIKE '%REORGANIZE%'
    AND C.DatabaseName = '<database__name, , FOO>'
    AND IH.the_database IS NULL
GROUP BY C.DatabaseName
    , C.SchemaName
    , C.ObjectName
    , C.IndexName
    , IndexType
    , IX.fill_factor;
 
--========================================================
-- ACCOUNT FOR INDEXES BOTH REBUILT AND REORGANIZED
--========================================================
UPDATE ##Index_History
SET reorg_count = C2.reorganize_count
FROM ##Index_History IH
    INNER JOIN
        (
            SELECT C.DatabaseName
                , C.SchemaName
                , C.ObjectName
                , C.IndexName
                , COUNT(C.ID) AS reorganize_count
                 FROM iDBA.dbo.CommandLog C    
            WHERE C.CommandType = 'ALTER_INDEX'
                AND C.ObjectType = 'U'
                AND C.Command LIKE '%REORGANIZE%'
                AND C.DatabaseName = '<database__name, , FOO>'
            GROUP BY C.DatabaseName
                , C.SchemaName
                , C.ObjectName
                , C.IndexName
        ) C2 ON IH.the_database = C2.DatabaseName
            AND IH.the_schema = C2.SchemaName 
            AND IH.the_object = C2.ObjectName   
            AND IH.the_index = C2.IndexName 
WHERE IH.rebuild_count > 0
 
--============================================================================
-- RETURN THE RESULTS
--============================================================================
SELECT the_database
    , the_schema
    , the_object
    , the_index
    , index_type
    , fill_factor
    , rebuild_count
    , reorg_count 
FROM ##Index_History 
ORDER BY the_database
    , rebuild_count DESC
    , reorg_count DESC
    , the_object
    , the_index
 
--============================================================================
-- CLEANUP THE MESS
--============================================================================
IF EXISTS (SELECT name FROM tempdb.sys.objects WHERE name = '##Index_History')
BEGIN
    DROP TABLE ##Index_History
END
 
IF EXISTS (SELECT name FROM tempdb.sys.objects WHERE name = '##Indexes')
BEGIN
    DROP TABLE ##Indexes
END

The results will look something like this:

Capture

I don’t know about you, but when I see indexes that are either rebuilt or reorganized every night then I know they need some love.  These indexes are being either written to, updated, or deleted from on a scale daily that is causing at least 15% page fragmentation (the threshold for my reorganization to kick off) on a nightly basis.

If this were not a commercially-developed solution that I was prohibited from altering or precluded from working with the developers to perhaps look at how we could redesign the processes through which the content of the indexes was changing then I’d go that route.  The only thing I could do was to work the the vendor to educate them on what their product was doing in the wild and work with them to at least change how much room we were giving these indexes for evolution of their page contents to at least reduce the frequency of index rebuilds/reorganizations.

Microsoft Excel: The DBA’s Best Friend

I lived in Excel for 10 years. I had my start in coding through Visual Basic for Application behind Excel when I created a pre-press/printing estimating solution when I didn’t like the tools I had to work with. (Then there was a period of time when I played around with FileMaker Pro, but it was the 90’s and I was young and impressionable… DON’T JUDGE ME!)  Anyway….

The next step in my process was to copy and paste the results into Excel for easy formatting and providing some recommended alterations to their Fill Factors (rather than the vendor’s unfounded “set everything to 60%“.)

Capture_excel

You’ll see I added a column for an Excel function that looked at the rebuild/reorganization frequency and suggested a reduced fill factor as a result. I also added pretty colors and line shading so I would not confuse any Project Managers at the vendor’s underground lair on Skull Island at Vendor HQ.

Next Steps

If I had the ability to affect schema changes (aka this was my company’s code and I was responsible for its performance) the next step would be to look at page splits for each index through sys.dm_index_operational_stats as well as the the individual structures of the indexes to see if improvements could be made in their construction or, in reviewing usage patterns through sys.dm_db_index_usage_stats disabling indexes that are perhaps undergoing these level of writes without providing an adequate benefit on the read side of the equation.
After all, the only good index is an index used for significantly more reads than the overhead required for it’s existence as manifested in it’s writes load.

Contrary to popular belief, I don’t have anything against third-party software vendors. I would not want their job. No matter what they do or produce it will not be right for ALL their customers. I do however have an issue with shortcuts, not listening to your customers, and offering up bad advice because you don’t expect your customers to be informed and question your advice. Therefore I continue to advise you to stay informed and question things when you don’t believe what you’re being told is correct.