How To: Run code before or after index maintenance


You can schedule code to run before or after index maintenance operations. There are several options available: 

  • Run code before or after a single database
  • Run code before or after each and every table in a database
  • Run code before or after a single table
  • Run code before or after each of a few tables (code executing before or after each table)
  • Run code before or after all but a few tables (code executing before or after each table)
  • Run code before or after reindex statements (within the same batch)

NOTE: Unless otherwise specified, pre and post code will run in the context of the Minion Reindex’s database (wherever the Minion Reindex objects are stored), because it was a design decision not to limit the code that can be run to a specific database. Therefore, always use "USE" statements – or, for stored procedures, three-part naming convention – for pre and postcode.


To run code before or after a single database, insert a row for the database into Minion.IndexSettingsDB. Populate the column DBPreCode to run code before the index operations for that database; populate the column DBPostCode to run code before the index operations after that database. For example: 

INSERT INTO [Minion].[IndexSettingsDB]
           ( DBName
           , Exclude
           , ReorgThreshold
           , RebuildThreshold
           , FILLFACTORopt
           , PadIndex 
           , DBPreCode
           , DBPostCode)
     VALUES
           ('YourDatabase'  -- DBName
           , 0    -- Exclude
           , 15    -- ReorgThreshold
           , 25    -- RebuildThreshold
           , 90    -- FILLFACTORopt
           , 'ON'    -- DBPreCode
           , 'EXEC YourDatabase.dbo.SomeSP;' -- DBPreCode
           , 'EXEC YourDatabase.dbo.OtherSP;' -- DBPostCode
           );


To run code before or after each and every table in a database, insert a row for the database into Minion.IndexSettingsDB. Populate the column TablePreCode to run code before the index operations for each individual table in the database; populate the column TablePostCode to run code after the index operations for each individual table in the database. For example: 


INSERT INTO [Minion].[IndexSettingsDB]
           ( DBName
           , Exclude
           , ReorgThreshold
           , RebuildThreshold
           , FILLFACTORopt
           , PadIndex 
           , TablePreCode
           , TablePostCode)
     VALUES
           ('YourDatabase'  -- DBName
           , 0    -- Exclude
           , 15    -- ReorgThreshold
           , 25    -- RebuildThreshold
           , 90    -- FILLFACTORopt
           , 'ON'    -- DBPreCode
           , 'EXEC YourDatabase.dbo.SomeSP;' -- TablePreCode
           , 'EXEC YourDatabase.dbo.OtherSP;' -- TablePostCode
           );


To run code before or after a single table (instead of each table), insert a row for the table into Minion.IndexSettingsTable. Populate the column TablePreCode to run code before the index operations for that database; populate the column TablePostCode to run code before the index operations after that database. 

Note: An entry in Minion.IndexSettingsTable overrides ALL the index maintenance settings for that table; defaults set in Minion.IndexSettingsDB will be ignored for this table.

For example:

INSERT  INTO [Minion].[IndexSettingsTable]
    ( [DBName] ,
      [SchemaName] ,
      [TableName] ,
      [ReorgThreshold] ,
      [RebuildThreshold] ,
      [FILLFACTORopt] ,
      [PadIndex] ,
      [GetRowCT] ,
      [GetPostFragLevel] ,
      [UpdateStatsOnDefrag] ,
      [LogIndexPhysicalStats] ,
      [IndexScanMode] ,
      [LogProgress] ,
      [LogRetDays] ,
      [IncludeUsageDetails] ,
      [TablePreCode] ,
      [TablePostCode] 
    )
VALUES  ( 'YourDatabase' ,  -- DBName
      'dbo' ,    -- SchemaName
      'YourTable' ,  -- TableName
      10 ,    -- ReorgThreshold
      20 ,    -- RebuildThreshold
      80 ,    -- FILLFACTORopt
      'ON' ,    -- PadIndex
      1 ,    -- GetRowCT
      1 ,    -- GetPostFragLevel
      1 ,    -- UpdateStatsOnDefrag
      0 ,    -- LogIndexPhysicalStats
      'Limited' ,  -- IndexScanMode
      1 ,    -- LogProgress
      60 ,    -- LogRetDays
      1 ,    -- IncludeUsageDetails
      'EXEC YourDatabase.dbo.SomeSP;' ,  -- TablePreCode
      'EXEC YourDatabase.dbo.OtherSP;'  -- TablePostCode
     );


 

To run code before or after each of a few tables, insert one row for each of the tables into Minion.IndexSettingsTable, populating the TablePreCode column and/or TablePostCode column as appropriate. 

To run code before or after all but a few tables, insert one row for the database into Minion.IndexSettingsDB, populating the TablePreCode column and/or the TablePostCode column as appropriate. This will set up the execution code for all tables. Then, to prevent that code from running on a handful of tables, insert a row for each of those tables to Minion.IndexSettingsTable, and keep the TablePreCode and TablePostCode columns set to NULL. 

For example, if we want to run the stored procedure dbo.SomeSP before each table in [YourDatabase] except tables T1, T2, and T3, we would: 


    1. Insert a row to Minion.IndexSettingsDB for [YourDatabase], setting PreCode to ‘EXEC dbo.SomeSP;’
    2. Insert a row to Minion.IndexSettingsTable for [YourDatabase].dbo.T1, establishing all appropriate settings, and setting PreCode to NULL. 
    3. Insert a row to Minion.IndexSettingsTable for [YourDatabase].dbo.T2, establishing all appropriate settings, and setting PreCode to NULL. 
    4. Insert a row to Minion.IndexSettingsTable for [YourDatabase].dbo.T3, establishing all appropriate settings, and setting PreCode to NULL. 

NOTE: We strongly recommend that you encapsulate any pre- or post-code into a stored procedure, unless the code is extremely simple. You can’t pass pre- or post-code parameters into the indexing routine, so pre- and post-code must be self-contained.  

Example - A real world TablePreCode example:  You have a database supplied by a vendor.  This database has a table with a non-clustered index with ALLOW_PAGE_LOCKS = OFF set.  This option causes the reorganize operation on that index to fail.  To resolve this, enter a row for that table into the Minion.IndexSettingsTable table, and include the following TablePreCode and TablePostCode options:


INSERT  Minion.IndexSettingsTable
    ( DBName 
      , SchemaName 
      , TableName 
      , Exclude 
      , ReindexGroupOrder 
      , ReindexOrder 
      , ReorgThreshold 
      , RebuildThreshold 
      , AllowPageLocks 
      , TablePreCode 
      , TablePostCode
    )
SELECT  'Demo'     --DBName
    , 'dbo'   --SchemaName
    , 'fragment'  --TableName
    , 0     --Exclude
    , 0     --ReindexGroupOrder
    , 0     --ReindexOrder
    , 10     --ReorgThreshold,
    , 20     --RebuildThreshold
    , 'ON' 
    , 'USE [Demo]; ALTER index ix_fragment2 ON dbo.fragment SET (ALLOW_PAGE_LOCKS = ON);'   -- TablePreCode
    , 'USE [Demo]; ALTER index ix_fragment2 ON dbo.fragment SET (ALLOW_PAGE_LOCKS = OFF);' --TablePostCode


To run code before or after reindex statements and within the same batch, you can use the StmtPrefix and/or StmtSuffix columns in Minion.IndexSettingsDB, Minion.IndexSettingsTable, or both.  

It is important to understand that this column allows you to prefix every reindex statement for a table, or for a database, with a statement of your own.  This is different from the table precode and postcode, because it is run in the same batch. Whereas, precode and postcode are run as completely separate statements, in different contexts.  

A good example use case for this is the need to ensure that your reindex statement is chosen as the deadlock victim (should a deadlock occur) for DatabaseA.  In this case, you would set StmtPrefix to "SET DEADLOCK_PRIORITY LOW;" for DatabaseA in the Minion.IndexSettingsDB table.  Other uses include setting a lock timeout, or adding a time delay to every reindex statement.  

The StmtPrefix you choose will be shown as part of the Cmd column in the Minion.IndexMaintLogDetails table. 



IMPORTANT: To ensure that your statements run properly, you must end the code in this column with a semicolon.