I was reviewing my index rebuild and reorganize activities. Some index rebuilding tasks are scheduled once or twice a week. I followed the best practices for the activities. However, it was not yielding expected performance gain. So, wondering what could be going wrong! Are there any hidden issues hindering the performance? I kept searching for potential clues in my favourite SQL Server gurus writings and compiled the hidden facts. Let's share my findings with you.
Check my relevant articles on this topics:
Facts
The fact is if you are rebuilding and recreating indexes smaller than 10,000 pages, you are just:
- wasting CPU power
- inflating logs
- annoying users and
- gaining zero
Going Deeper into the issue
Fragmentation
In my Understanding Index Fragmentation I explained the details about the fragmentation, its types and causes behind the fragmentation.
Simply:
- Internal fragmentation is too many free space in the data pages
- External fragmentation is out-of-order pages on disk. The logical sequence does not match physical placement
In other word:
- Internal fragmentation is wastage of space
- External fragmentation is wastage of reads
Fragmentation and Disk Type
- External fragmentation
- Mechanical Hard Disk Drive (HDD) - scattered reads mean lots of tiny movements by the drive head resulting slower performance.
- Solid State Drive (SSD) - it is basically a memory chip, so page order has minimal impact on read speed. Though SQL Server will not tell this.
- Internal fragmentation
- Both Mechanical HDD and SSD have no impact on internal fragmentation.
Fragmentation % and Page Count
- Rule of thumb, if page count < 10,000, skip rebuild/reorganize operation. As it will not yield any performance gain.
- Microsoft’s default page count for index maintenance is 1000 8K pages. 8K x1000 = approx. 8MB. That is small.
Index Rebuild and Reorganize
- Index Rebuild drops and recreates the index.
- it is resource-hungry
- it increases log use,
- it can block unless ONLINE
- Index Reorganize shuffles leaf-level pages gently.
- it is lighter
- it is less effective
Smart Suggestion
- page count < 10,000, skip rebuild/reorganize operation
- page count > 10,000 and fragmentation > 30%, reorganize
- page count > 10,000 and fragmentation > 50%, rebuild (if performance demands)
Statistics is Crucial
Statistics is used to estimate the cardinality of a query result. It is the knowledge of how many rows are in a table, and how many of those rows satisfy the various search and join conditions. Based on this, optimizer creates the best query plan i.e. whether to use an index seek, table scan, join methods, and more. If the stats are outdated or inaccurate, SQL Server can choose a bad query plan, which can lead to slow performance, excessive reads, or blocked processes.
So, updating Statistics daily in most transactional systems can produce higher ROI than blindly rebuilding indexes.
Tools for Smarter Index Maintenance
Use below tool which let you filter by both fragmentation level and page count, like:
Final Words
Do not rebuild indexes blindly. If you are using SSD and page count within 10,000, you are trying to solve a problem that does not exist. Possibly you are creating new ones like log growth and blocking.
Be smart:
- Focus on meaningful fragmentation
- Consider index size before acting
- Separate stats updates from index maintenance
Smarter maintenance means better performance with fewer headaches.
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.