This Article 
   
 Share 
   
 Bibliographic References 
   
 Add to: 
 
Digg
Furl
Spurl
Blink
Simpy
Google
Del.icio.us
Y!MyWeb
 
 Search 
   
2010 IEEE 26th International Conference on Data Engineering (ICDE 2010)
Workload driven index defragmentation
Long Beach, CA, USA
March 01-March 06
ISBN: 978-1-4244-5445-7
Vivek Narasayya, Microsoft Research, One Microsoft Way, Redmond, WA, USA
Manoj Syamala, 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.
Citation:
Vivek Narasayya, Manoj Syamala, "Workload driven index defragmentation," icde, pp.497-508, 2010 IEEE 26th International Conference on Data Engineering (ICDE 2010), 2010
Usage of this product signifies your acceptance of the Terms of Use.