Deploying Smart Index Script
Executing the SmartIndexMainteanceScriptV1.48.sql will create following objects
Database
Tables
- MaintenanceWindow
- Maintenance Windows which can be assigned to each index. No limitation on number of maintenance windows you can have. However if there is overlap, script will pick the first maintenance window that matches the Weekday and Time.
- DatabaseStatus
- Simple flag value for tracking if Transaction Log with in database has reached capacity.
- MetaData
- Used in determining when the indexes usage stats were last collected. Helps in calculating proper delta value.
- MasterIndexCatalog
- Primary table that tracks all objects that need to maintained.
- MaintenanceHistory
- All history related information, what was maintained? How was it maintained? Why was it not maintained?
Stored Procedures
- upUpdateMasterIndexCatalog
- Used to identify new objects/indexes and clean up old objects that no longer exist.
- upUpdateIndexUsageStats
- Used to track index usage stats over server restarts, to understand true range scans for each index.
- upMaintainIndexes
- Primary Procedure to maintain all indexes.
- (Manual) upUpdateMainteanceWindow
- Re-assign Maintenance Windows to each index based on the index size.
- (Manual) upCleanupHistory
- Clean up history in the MaintenanceHistory table, right now table is not maintained and can grow large over time.
SQL Server Agent Jobs (Following jobs will need to be created manually)
- (Manual) Execute upMaintainIndexes. Recommend, schedule should be defined to match maintenance windows in dbo.MaintenanceWindow.
- (Manual) Execute upUpdateIndexUsageStats. Recommend, scheduled to run daily 6:00AM.
- (Manual) Execute upUpdateMasterIndexCatalog. Recommend, scheduled to run weekly Monday 4:00AM.
- (Manual) Execute upUpdateMainteanceWindow. Recommend, scheduled to run monthly Last Friday 4:00AM.
- (Manual) Execute upCleanupHistory. Recommend, schedule to run monthly Last Friday 5:00AM.
Smart Index Script Configuration
- Configuration items in dbo.upMaintainIndexes procedure.
- @MAXDOP
- The script will calculate MAXDOP to use for an index operation (1 or @MAXDOP value). Therefore you’ll need to configure this to the Microsoft best recommended practice. Currently set to 8.
- @FiveMinuteCheck
- Safety check. When the script reaches with in 5 minutes of maintenance window it is design to not attempt another operation. This as worked well for my customer. However maybe you would want longer safety margin.
- @MaxLogSpaceUseBeforeStop
- This is a strict limit on how much T-Log is full for the given index’s database. If it reaches this limit for the duration of the maintenance window no indexes with in that database will be maintained.
- @DefaultOpTime
- The script will learn over time how long it takes to maintain each object. However for new objects, it assumes 1/10 of the total maintenance window duration. If this is too large or to small please adjust to your needs.
Smart Index Script Internals
- Indexes are maintained based on Last Managed, Skip Count and Range Scans.
- Only indexes that have range scans are maintained.
- Currently script triggers on 10% and 30% fragmentation for reorganize and rebuild respectively.
- Maintenance window name “HOT Tables” cannot change.
- If an index has been defined in the HOT Tables maintenance window, then index will be maintained as long as it has at least 64 pages.
- Indexes which reach 10% fragmentation with ALLOWPAGELOCKS set to OFF, no operation is attempted until fragmentation reaches 30%.
- Script can maintain indexes online or offline. Online is Default. Can update OfflineOpsAllowed in dbo.MasterIndexCatalog per index to allow offline operation on the index.
- dbo.MasterIndexCatalog keeps track of Last Reorganize Time, Last Rebuild Time, and Last Fragmentation Time.
- Script can fail to main an index if 1) index cannot be reorganized 2) index cannot be rebuilt online 3) index cannot be rebuilt offline – in such case index is skipped and next index is evaluated.
- Script adjusts Fill Factor from 70% to 99% based on how quickly index becomes fragmented.
- Script maintains Skip Count and Max Skip Count for each index, which help script learn how quickly indexes are becoming fragmented, there by avoiding unnecessary fragmentation scans.
- Maintenance window bound, if end it reached. It stops and picks up from where it left off.