What is the first word that comes to mind whenever one mentions the need for optimizing search or for increasing the performance of a database? Index.
Indexes play a vital role in SQL Server performance tuning, but they too need periodic maintenance. An index is logically implemented as a B-tree.
Over time as data is accumulated by the table, fragmentation may set in and the distribution of data within the index may become unbalanced, with some pages becoming full (as governed by the fill factor) leading to “page splits” whereas others might be sparsely filled. If data is deleted from the table, it leaves behind a “hole” in the index page – this free space is not reclaimed, which contributes to the bloating of the index. This reduces the efficiency of the indexes, while possibly consuming more than required disk space. At this stage, administrators need to perform maintenance operations on the index.
Index Maintenance operations can be classified into two:
- Index Rebuild
- During a rebuild, the entire index (from the root to the leaf nodes) is rebuilt using the same columns, index type, uniqueness attribute, and sort order
- The rebuild of an index effectively recreates the entire B-tree structure
- Index Reorganize
- Index reorganization, on the other hand, only removes fragmentation at the leaf level
- Intermediate-level pages and the root page are not defragmented
From an availability perspective, Reorganization is better than a rebuild, because reorganization is always an ONLINE process, i.e. it acquires only short-term locks whereas REBUILD prevents any changes to the underlying table until the operation completes, i.e. it acquires long-term locks. The question therefore is:
What types of locks do index REBUILD and index REORGANIZE operations occupy on the underlying table, by default (i.e. the “ONLINE” option is not specified)?
As a bonus exercise, you may want to share any queries or tests that you may have conducted to arrive at the answer to the question above. Please note that the bonus exercise does not carry any rewards this time around – it is only as an exercise for the brain cells.
The rules are very simple:
- You can answer these questions till Wednesday 22:00hrs (IST)/Wednesday 12:30hrs (US-EDT) – August 15, 2012
- Answers will be shared and winners declared in my post on Thursday, August 16, 2012
- You can answer multiple times – however, at the end of the day, only one entry would be considered
- Follow the following on Twitter!
- Winners will be chosen randomly
- Prizes will be given out through Twitter – hence, it is important that you follow me
Remember: Participation is always more important! Make sure you reply to the quiz even if it's just a guess!
Until we meet next time,
Be courteous. Drive responsibly.