DocumentCode :
2081650
Title :
Workload driven index defragmentation
Author :
Narasayya, Vivek ; Syamala, Manoj
Author_Institution :
One Microsoft Way, Microsoft Res., Redmond, WA, USA
fYear :
2010
fDate :
1-6 March 2010
Firstpage :
497
Lastpage :
508
Abstract :
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.
Keywords :
SQL; decision support systems; input-output programs; B+-tree; I/O performance; Microsoft SQL server; database engines; decision support queries; expensive operation defragmentation; fragmentation information; scan large indexes; workload driven index defragmentation; Costs; Degradation; Engines; Indexes; Information analysis; Information management; Performance analysis; Relational databases; Thumb;
fLanguage :
English
Publisher :
ieee
Conference_Titel :
Data Engineering (ICDE), 2010 IEEE 26th International Conference on
Conference_Location :
Long Beach, CA
Print_ISBN :
978-1-4244-5445-7
Electronic_ISBN :
978-1-4244-5444-0
Type :
conf
DOI :
10.1109/ICDE.2010.5447889
Filename :
5447889
Link To Document :
بازگشت