Sign up to receive free email alerts when patent applications with chosen keywords are publishedSIGN UP

Abstract:

A method and a system defragment indexes in a relational database. The
relational database contains a set of tables, in which each table
contains a set of indexes, in which, for each index, a fragmentation
parameter is associated indicating the fragmentation level of the index.
The method includes the following steps: getting a list of the set of
tables; for each table of the list, getting the fragmentation parameter
associated to each index of the table; and depending on the value of the
fragmentation parameter, defragmenting the associated index.

Claims:

1. A method for defragmenting indexes in a relational database, the
relational database containing a set of tables, in which each table
contains a set of indexes, in which, for each index, a fragmentation
parameter is associated indicating a fragmentation level of the index,
which comprises the steps of:a) getting a list of the set of tables;b)
for each table of the list, getting the fragmentation parameter
associated with each of the indexes of the table; andc) depending on a
value of the fragmentation parameter, defragmenting an associated index.

2. The method according to claim 1, wherein the relational database is a
Microsoft SQL Database.

3. The method according to claim 2, wherein the fragmentation parameter is
a "Logical Scan Fragmentation" parameter obtained by executing a "DBCC
SHOWCONTIG" command.

4. The method according to claim 3, wherein in the step c) a "Logical Scan
Fragmentation" parameter value is greater than a threshold value between
circa 10% to circa 35%.

5. The method according to claim 3, which further comprises performing the
step of defragmenting the associated index by executing a "DBCC
INDEXDEFRAG" command.

6. The method according to claim 2, wherein the fragmentation parameter is
the "Scan Density" parameter obtained by executing a "DBCC SHOWCONTIG"
command.

7. The method according to claim 6, which further comprises in step c),
setting a "Scan Density" parameter value to be lower than a threshold
value between circa 55% to circa 85%.

8. The method according to claim 6, which further comprises performing the
step of defragmenting the associated index by executing a "DBCC
DBREINDEX" command.

9. The method according to claim 6, which further comprises, after all the
indexes of one selected table have been checked, updating table
statistics by executing an "UPDATE STATISTICS" command.

10. A method for defragmenting indexes in a relational Microsoft SQL
database, the relational database containing a set of tables, in which
each table contains a set of indexes, in which, for each index, a "Scan
Density" parameter and a "Logical Scan Fragmentation" parameter are
associated indicating fragmentation levels of the index, which method
comprises the steps of:a) getting a list of the set of tables from an
INFORMATION_SCHEMA.TABLE SQL server system view;b) for each table of the
list, getting the "Scan Density" parameter and the "Logical Scan
Fragmentation" parameter associated with each of the indexes of the table
by executing a "DBCC SHOWCONTIG" command;c) in case of an online index
defragmentation mode, depending on a value of the "Logical Scan
Fragmentation" parameter, for each of the indexes of each of the tables,
defragmenting an associated index by executing a "DBCC INDEXDEFRAG"
command; andd) in case of an offline index defragmentation mode, for each
of the indexes of each of the tables, depending on a value of the "Scan
Density" parameter, defragmenting the associated index by executing a
"DBCC DBREINDEX" command.

11. The method according to claim 10, which further comprises in step d),
setting the "Logical Scan Fragmentation" parameter value to be greater
than a threshold value contained between circa 10% to circa 35%.

12. The method according to claim 10, which further comprises in step b),
setting a "Scan Density" parameter value to be lower than a threshold
value contained between circa 55% to circa 85%.

13. A computer-readable medium having computer-executable instructions for
performing the method according to claim 1.

14. A computer-readable medium having computer-executable instructions for
performing the method according to claim 10.

15. A system, comprising:means for performing a method for defragmenting
indexes in a relational database, the relational database containing a
set of tables, in which each table contains a set of indexes, in which,
for each index, a fragmentation parameter is associated indicating a
fragmentation level of the index, said means programmed to:get a list of
the set of tables;for each table of the list, get the fragmentation
parameter associated to each of the indexes of the table; anddepending on
a value of the fragmentation parameter, defragment an associated index.

16. A system, comprising:means for performing a method for defragmenting
indexes in a relational Microsoft SQL database, the relational database
containing a set of tables, in which each table contains a set of
indexes, in which, for each index, a "Scan Density" parameter and a
"Logical Scan Fragmentation" parameter are associated indicating
fragmentation levels of the index, said means programmed toget a list of
the set of tables from an INFORMATION_SCHEMA.TABLE SQL server system
view;for each table of the list, get the "Scan Density" parameter and the
"Logical Scan Fragmentation" parameter associated to each of the indexes
of the table by executing a "DBCC SHOWCONTIG" command;in case of online
index defragmentation mode, depending on a value of the "Logical Scan
Fragmentation" parameter, for each of the indexes of each of the tables,
defragment an associated index by executing a "DBCC INDEXDEFRAG" command;
andin case of offline index defragmentation mode, for each of the indexes
of each of the tables, depending on a value of the "Scan Density"
parameter, defragment the associated index by executing a "DBCC
DBREINDEX" command.

[0002]The present invention relates to a method and to a system for
defragmenting indexes in a relational database.

[0003]A database management system (DBMS) is a set of computer programs
configured to control the organization, storage, management, and
retrieval of data in a database.

[0004]Today, the most common kind of database is a relational database.

[0005]A relational database management system (RDBMS) is a DBM system
based on the relational model as introduced by E. F. Codd. In brief, in
RDBM systems, data is stored in the form of tables and the relationship
among the data is also stored in the form of tables.

[0006]Thus, the table which stores database data/information is a
fundamental database object in a RDBM system. Every database contains one
or more tables, each table has its own unique name and contains columns
and rows. The database table columns, also called table fields, have
their own unique names and have pre-defined data types. Table columns can
have various attributes defining the column functionality (the column is
a primary key, there is an index defined on the column, the column has
certain default value, etc.).

[0009]In order to speed up queries, indexes are created in existing
tables. Indexes cannot be seen by users. Indexes allow locating table
rows in a quicker and more efficient way. It is possible to create an
index on one or more columns of a table, and each index is given a name.
The data rows for each table are stored in a collection of data pages.

[0010]The structured query language (SQL) is a database computer language
used in RDBM systems and a SQL Server System is a set of components
configured to meet the data storage and analysis requirements of
enterprises.

[0012]In fact, since typically such data modifications are not distributed
equally among the table rows, the fullness of each page can vary over
time.

[0013]Index fragmentation may reduce system performances by causing
additional page reads in queries that scan partially or fully a table.

[0014]Index fragmentation may have a large variety of negative effects on
different workload types. Certain applications may be severely affected
when indexes are defragmented.

[0015]In order to prevent damages caused by index fragmentation, database
administrators usually drop and then rebuild all indexes of all tables
without analysing which are the indexes actually requiring
defragmentations.

[0016]In fact, the task of selecting the indexes to be defragmented is
time consuming and thus is not performed systematically as it should.

SUMMARY OF THE INVENTION

[0017]It is accordingly an object of the invention to provide a method for
defragmenting indexes in a relational database and a system of performing
the method which overcome the above-mentioned disadvantages of the prior
art devices and methods of this general type, which defragments indexes
in a relational database and minimizes human intervention.

[0018]The aforementioned aim is achieved by a method and a system for
defragmenting indexes in a relational database, the relational database
contains a set of tables, in which each table contains a set of indexes,
in which, for each index, a fragmentation parameter is associated
indicating the fragmentation level of the index. The invention includes
the following steps: a) getting a list of the set of tables; b) for each
table of the list, getting the fragmentation parameter associated to each
index of the table; and c) depending on the value of the fragmentation
parameter, defragmenting the associated index.

[0019]In embodiments of the invention, the relational database may be a
Microsoft SQL Database.

[0020]In embodiments of the invention the fragmentation parameter may be
the "Logical Scan Fragmentation" parameter obtained by executing the
"DBCC SHOWCONTIG" command. In step c), the "Logical Scan Fragmentation"
parameter value may be greater than a threshold value contained between
circa 10% to circa 35%; and/or the step of defragmenting the associated
index at step c) may be performed by executing a "DBCC INDEXDEFRAG"
command.

[0021]In embodiments of the invention, the fragmentation parameter may be
the "Scan Density" parameter obtained by executing the "DBCC SHOWCONTIG"
command. In step c), the "Scan Density" parameter value may be lower than
a threshold value contained between circa 55% to circa 85%. The step of
defragmenting the associated index at step c) is performed by executing a
"DBCC DBREINDEX" command; and/or after all indexes of one selected table
have been checked, table statistics may be updated by executing an
"UPDATE STATISTICS" command.

[0022]It is therefore the aim of the present invention to overcome the
above mentioned drawbacks, in particular by providing a method and a
system for defragmenting indexes in a relational database which minimizes
human intervention.

[0023]The aforementioned aim of defragmenting indexes in a relational
database which minimizes human intervention is also achieved by a method
and a system for defragmenting indexes in a relational Microsoft SQL
database, the relational database contains a set of tables, in which each
table contains a set of indexes, in which, for each index, a "Scan
Density" parameter and a "Logical Scan Fragmentation" parameter are
associated indicating the fragmentation levels of the index. The
invention includes the steps of getting a list of the set of tables from
the INFORMATION_SCHEMA.TABLE SQL server system view; for each table of
the list, getting the "Scan Density" parameter and a "Logical Scan
Fragmentation" parameter associated to each index of the table by
executing the "DBCC SHOWCONTIG" command; in case of online index
defragmentation mode, depending on the value of the "Logical Scan
Fragmentation" parameter, for each index of each table, defragmenting the
associated index by executing "DBCC INDEXDEFRAG" command; and in case of
offline index defragmentation mode, for each index of each table,
depending on the value of the "Scan Density" parameter, defragmenting the
associated index by executing "DBCC DBREINDEX" command.

[0024]In embodiments of the proposed invention, the "Logical Scan
Fragmentation" parameter value may be greater than a threshold value
contained between circa 10% to circa 35%; and/or the "Scan Density"
parameter value may be lower than a threshold value comprised between
circa 55% to circa 85%.

[0025]The proposed invention may be preferably implemented in software.

[0026]The proposed invention, by maintaining a good health of table
indexes, improves the performances of applications using relational
databases.

[0027]The proposed invention increases RDBMS availability so that data can
be faster retrieved by the user.

[0028]The proposed invention allows saving time due to the fact index
fragmentation is automatically performed.

[0029]Other features which are considered as characteristic for the
invention are set forth in the appended claims.

[0030]Although the invention is illustrated and described herein as
embodied in a method for defragmenting indexes in a relational database
and a system of performing the method, it is nevertheless not intended to
be limited to the details shown, since various modifications and
structural changes may be made therein without departing from the spirit
of the invention and within the scope and range of equivalents of the
claims.

[0031]The construction and method of operation of the invention, however,
together with additional objects and advantages thereof will be best
understood from the following description of specific embodiments when
read in connection with the accompanying drawings.

BRIEF DESCRIPTION OF THE DRAWING

[0032]The single FIGURE of the drawing is a flowchart which schematically
illustrates an algorithm according to an example embodiment of the
invention.

DETAILED DESCRIPTION OF THE INVENTION

[0033]According to the present invention, the following actions are
executed in a relational database: getting a list of the set of tables of
the relational database; for each table of the list, getting the
fragmentation parameter, indicating the fragmentation level, associated
to each index of the table; and for each index of each table, depending
on the value of the fragmentation parameter, defragmenting the associated
index.

[0034]The FIGURE of the drawing shows a flowchart schematically
illustrating an algorithm of an example embodiment of the present
invention for a Microsoft SQL relational database. Microsoft SQL
relational database commands are described in the Microsoft SQL Server
Books Online.

[0035]At start step ST, the algorithm is started.

[0036]At step A10, a list of the tables of the database is gathered. Such
a table list is obtained by reading the RDBM system catalog. By system
catalog is denoted the set of tables, maintained by the SQL Server, with
information about all the objects, data types, constraints, configuration
options, and resources available to the SQL Server.

[0037]This table list is retrieved from INFORMATION-SCHEMA.TABLES SQL
Server system view. This is the SQL Server table that contains all the
table names of a certain database.

The below example returns all the table names of a database called PUBS:

TABLE-US-00002
USE PUBS
SELECT * FROM INFORMATION_SCHEMA.TABLES

[0038]At step A11, the first table of the table list is selected.

[0039]At step A12, a list of the set of "Index Name" indexes, i.e. the
internal physical indexes, relative to the selected table are retrieved.

[0040]At step A13, the first index is selected.

[0041]At step A14, the fragmentation parameter associated to the selected
index is retrieved.

[0042]The fragmentation parameters obtained at step A14 are the "Scan
Density" parameter value and the "Logical Scan Fragmentation" parameter
value. These fragmentation parameters are obtained by executing the DBCC
SHOWCONTIG command for the table index.

[0043]At conditional step C1, a check is performed whether it is requested
a mode of online index defragmentation YC1 or, instead, an offline index
defragmentation mode is requested NC1.

[0044]An online defragmentation mode YC1 may be used when the database is
in-use by some users and, instead, an offline defragmentation mode NC1
may be used when no user is connected to the database.

[0045]In case of online index defragmentation mode YC1, for each specified
table of the list, depending C2 on the value of the "Logical Scan
Fragmentation" value, the defragmentation of the associated index is
performed by executing the DBCC INDEXDREFRAG command.

[0047]In case of offline index defragmentation mode NC1, for each
specified table of the list, depending C3 on the value of the "Scan
Density" value, the defragmentation of the associated index is performed
by executing the DBCC DBREINDEX command.

[0048]The DBCC DBREINDEX index defragmentation command is executed YC3
when the "Scan Density" value is found to be above a configurable
threshold value disposed between circa 55% to circa 85%, e.g. circa 70%.

[0049]After the index defragmentation command is executed for a whole
table, also the statistics may be rebuilt by executing the command UPDATE
STATISTICS <tablename>, not shown in the FIGURE. Having up to date
statistics is important since the table statistics are used to determine
the optimal strategy for evaluating a query by the SQL Server query
optimizer to estimate the cost of using a given index for a query.

[0051]At conditional check C4, it is checked whether end of index list is
reached. In case end of index list check C4 returns false NC4, a new
iteration is performed starting from step A14. In case end of index list
check C4 returns true YC4, the following table of the table list is
selected at step A16.

[0052]At conditional check C5, it is checked whether end of table list is
reached. In case end of table list check C5 returns false NC5, a new
iteration is performed starting from step A12. In case end of index list
check C4 returns true YC4, the algorithm ends END since all tables with
all their indexes have been processed.

[0053]In an embodiment of the present invention, at startup of the index
defragmentation algorithm, the user may be entitled to choose whether to
perform an online index defragmentation or an offline index
defragmentation.

[0054]Online defragmentation does not hold locks long term and thus does
not block running queries or updates. On the contrary, offline
defragmentation holds locks long term and thus does block running queries
or updates. A lock is a restriction on access to a resource in a
multi-user environment. SQL Server locks users out of a specific row
automatically to maintain security or prevent concurrent data
modification problems.

[0055]Hence, in an online index defragmentation mode, the tables and
indexes are advantageously available to the user while the index is being
defragmented. Instead, in an offline defragmentation mode all users are
disconnected from the database and after the physical reorganization of
the index page the statistics are conveniently rebuilt.

[0056]In another embodiment of the present invention, a SQL Server system
may be giving the choice to schedule an online index defragmentation with
at certain times with a certain periodicity, e.g. daily or weekly, and to
schedule the offline index defragmentation mode with other times and
periodicity, e.g. at nights, weekly or monthly.