Typical index management requires you to check for fragmentation every week and decide to maintain it using index reorganization or index rebuild. However, there is very little to no awareness in existing solutions as to will index will be fragmented?
The disk subsystems is extremely fast, severs with multi-terabyte of memory is not rare any more. But still, reading index just to assess if it is fragment is wasted disk I/O.
In this solution, my goal is minimize the fragmentation in indexes and the time spent managing the indexes. I try to minimize the fragmentation in an index by managing the fill factor for each index. The solution learns over time, what is ideal fill factor. An ideal fill factor is where index fragmentation to 10% or less within a week. It learns which indexes I need to manage by checking their fragmentation, overtime, it learns which indexes “will be fragmented” and “which will not be”. Thereby reducing the disk I/O required to read and reducing the overall index maintenance time.
Check out the solution on my GitHub repo, sqlcan/sqlsim: SQL Server Smart Index Management (github.com). Try it out let me know if helped reduce the disk I/O by making index maintenance a bit smarter? 🙂
SQL Canada (SQLCAN) 