Hello everyone!
I meet more and more frequently the problem of index fragmentation.
Indeed, when a large volume of data is inserted into a table without it being planned in the design of the database, indexes grow haphazardly ...
To detect such fragmentation, SQL Server 2000 provides the following command: DBCC
SHOWCONTIG
If this command is still available in SQL Server 2005 - and will have to disappear in future versions - the latter provides other tools more ergonomic and more accurate for administrators. Among these tools, we note in particular the dynamic function following
sys.dm_db_index_physical_stats
There are several ways to correct the problem of fragmentation. But before describing these techniques, let us first of all how we can prevent such fragmentation occurs ...
In reality, everything depends on the feeding of the database. Indeed, a batch load will be dealt differently from a single insertion.
Similarly, the functional medium imposes Constrain does not always allow the use of certain techniques.
Let us first if batch loading: when running a batch, we insert generally large amount of data. These batches usually run at night, periods of low activity for the database.
In this case, we will favor the removal of all the indexes before loading, and recreating the indexes at the end. This has two advantages:
- First regenerated indexes are not fragmented, since fresh
- On the other hand, the data loading is much faster
I recall here that the primary key includes a unique index must also be removed in this case - because no, it never happens that there are people who suppress all indexes except the primary key ;-).
course, this suppression index implies a lack of activity on the database on a given time slot.
In the case of insertions unit, the volume of these inserts is generally predictable and low. Under these conditions, it is best to size the FILLFACTOR Index a value ensuring sufficient space for its development.
careful not to go too all the same: a FILLFACTOR less than 50 performance degrades very sharply and is therefore more detrimental than the index fragmentation.
Let us now to a table whose indexes are heavily fragmented.
A simple solution is the elimination of indexes to recreate them. This solution has the advantage of always giving satisfaction and to avoid the explosion of file groups.
This solution can be performed in a traditional script ( DROP / CREATE ) or using the command DBCC DBREINDEX .
The major drawback of this method is the inactivity of the database needed for rebuilding indexes, as these transactions are transactions OFFLINE.
command DBCC DBREINDEX , always present in SQL Server 2005, has an equivalent with the command ALTER INDEX with the REBUILD option .
The alternative is defragmentation ( DBCC INDEXDEFRAG ). This solution is a ONLINE. This is the main rationale for using this command. However, care must be taken not to make this operation after loading and not in parallel with the risk of being ineffective.
The main drawback of this solution is the space required on groups of files. Indeed, defrag moves data blocks to free sites, and therefore poses problems quickly sized groups of files if the fragmentation is very important. In addition, the defragmentation operation is fully loggée is generating very large volumes in the transaction logs.
Again SQL Server 2005 still allows the command DBCC INDEXDEFRAG , destined to disappear in favor of ALTER INDEX with option REORGANIZE .
In conclusion, here is a summary of important points:
- The first thing is to ensure through the development of the database that our database does not fragment or little. To do this, do not hesitate to remove the indexes and recreate them in the feeding process. Index fragmentation is not inevitable.
- Thereafter, regular review of the state of database to monitor the evolution of the indexes and, where appropriate, take action.
- Under SQL Server 2000, the preferred nuclear command DBCC DBREINDEX if the operation can be conducted OFFLINE. We therefore restrict the use of the DBCC command INDEXDEFRAG cases requiring that data remains ONLINE.
- Under SQL Server 2005, use the ALTER INDEX command options with REBUILD and REORGANIZE going well, this command is only intended to be maintained in future versions of SQL Server.
0 comments:
Post a Comment