2013 IEEE 29th International Conference on Data Engineering (ICDE) (2010)
Long Beach, CA, USA
Mar. 1, 2010 to Mar. 6, 2010
Manoj Syamala , Microsoft Research, One Microsoft Way, Redmond, WA, USA
Vivek Narasayya , Microsoft Research, One Microsoft Way, Redmond, WA, USA
Decision support queries that scan large indexes can suffer significant degradation in I/O performance due to index fragmentation. DBAs rely on rules of thumb that use index size and fragmentation information to accomplish the task of deciding which indexes to defragment. However, there are two fundamental limitations that make this task challenging. First, database engines offer little support to help estimate the impact of defragmenting an index on the I/O performance of a query. Second, defragmentation is supported only at the granularity of an entire B+-Tree, which can be too restrictive since defragmentation is an expensive operation. This paper describes techniques for addressing the above limitations. We also study the problem of selecting the appropriate indexes to defragment for a given workload. We have implemented our techniques in Microsoft SQL Server and developed a tool that can provide appropriate index defragmentation recommendations to DBAs. We evaluate the effectiveness of the proposed techniques on several real and synthetic databases.
Manoj Syamala, Vivek Narasayya, "Workload driven index defragmentation", 2013 IEEE 29th International Conference on Data Engineering (ICDE), vol. 00, no. , pp. 497-508, 2010, doi:10.1109/ICDE.2010.5447889