SQL Server stores the data and indexes on data files in a physical disk, this is called external fragmentation. As all files on the windows system, fragmentation degrades I/O performance.
SQL Server has also a internal fragmentation that occurs when records are removed from the database pages, but the space is not freed.
If data files have external fragmentation, index rebuilding will take longer since there is a I/O bottleneck.
Defragmentation is the process to fix fragmentation.
For each type of fragmentation, there is a solution:
1) Internal fragmentation:
Use the following script from SQL Server 2005 BOL to defragment or DBCC DBREINDEX .
2) External fragmentation:
Use a defragmentation tool like diskeeper to defragment the file system.
Developing a maintenance plan for SQL Server is a best practice.
No comments:
Post a Comment