Fragmentation can be defined as any condition that cause more than optimal amount of disk I/O to be performed in accessing a table or cause the longer disk I/O. For optimal performance of SQL queries, the data pages of tables are as contiguous as possible and pages are as fully packed as possible. Fragmentation breaks this rule thus reducing the performance of the queries. There are two levels of fragmentation: file system level (physical disk fragmentation) and index level fragmentation.
Physical disk fragmentation is the fragmentation of the database file in the file system, when file system cannot allocate contiguous space for it. As a result disk head has to move back and forth when reading from the datbase files. That happens when database file is stored on the same disk as other files, such as OS, application, or log files or when database grows frequently and in small chunks. The way to avoid it is to allocate enough space originally and to specify growth option to allocate larger chunks instead of small ones.
Before running windows defragmentation tool, SQL Server needs to be stopped, otherwise database file will be skipped.
Index fragmentation can be reduced by selecting an appropriate fill factor when creating the index.
There are two ways to defragment indexes: rebuild and reorganize.
When you reorganize an index, SQL Server physically reorders the leaf-level pages to match logical order of the leaf nodes. The process does not allocate new pages but only uses existing ones, compacting the indexes. Reorganization uses minimal resources and does not block queries or updates.
Reorganization should only be used on lightly fragmented indexes (no more than 30% fragmentation), heavily fragmented indexes should be rebuilt.
The sql below reorganizes the specified index:
The important thing to keep in mind is that if you try to reorganize or rebuild an index with few pages, most likely nothing will change.
Rebuilding an index involves dropping the original index and building a new one, so that index starts clean, with minimal fragmentation. Index can be rebuilt offline and online. Rebuilding is done the same was as reorganizing except REBUILD keyword is used instead of REORGANIZE:
if you want to add some options to your rebuild, use WITH clause
Physical disk fragmentation is the fragmentation of the database file in the file system, when file system cannot allocate contiguous space for it. As a result disk head has to move back and forth when reading from the datbase files. That happens when database file is stored on the same disk as other files, such as OS, application, or log files or when database grows frequently and in small chunks. The way to avoid it is to allocate enough space originally and to specify growth option to allocate larger chunks instead of small ones.
Before running windows defragmentation tool, SQL Server needs to be stopped, otherwise database file will be skipped.
Index fragmentation can be reduced by selecting an appropriate fill factor when creating the index.
There are two ways to defragment indexes: rebuild and reorganize.
When you reorganize an index, SQL Server physically reorders the leaf-level pages to match logical order of the leaf nodes. The process does not allocate new pages but only uses existing ones, compacting the indexes. Reorganization uses minimal resources and does not block queries or updates.
Reorganization should only be used on lightly fragmented indexes (no more than 30% fragmentation), heavily fragmented indexes should be rebuilt.
The sql below reorganizes the specified index:
ALTER INDEX index_name ON DBName.TableName REORGANIZE
The important thing to keep in mind is that if you try to reorganize or rebuild an index with few pages, most likely nothing will change.
Rebuilding an index involves dropping the original index and building a new one, so that index starts clean, with minimal fragmentation. Index can be rebuilt offline and online. Rebuilding is done the same was as reorganizing except REBUILD keyword is used instead of REORGANIZE:
ALTER INDEX index_name ON DBName.TableName REBUILD
if you want to add some options to your rebuild, use WITH clause
ALTER INDEX index_name ON DBName.TableName REBUILD WITH ( FILLFACTOR = 60, ONLINE = ON )
No comments:
Post a Comment