Solving The Issue Of SQL Server Physical File Fragmentation

jeffluo

SQL Server physical file fragmentation causes major performance issue in database, it happens when data is deleted from a drive and left small gaps to be filled by new data files. In File fragmentation the logical sequential pages are not exist in physical sequence. When there is physical file fragmentation, auto-growing files will not get the sufficient continuous space, therefore the files get scattered throughout the hard drive.

The physical file fragmentation cause slow access or seek time as for the time taken for accessing the data is increased and also, system needs to find all fragments of file before opening the file.

In addition, the data file pages are Out-of-order that also increases the seek time. To lessen the seek time, user can defrag the fragmented file. In this article we will discuss the problem of SQL Server physical file fragmentation and the way to defrag the file.

Problem

Usually DBA do not consider the SQL Server physical file fragmentation as a big issue. However, it takes lots of time to access fragmented file as compare to the file stored in continuous storage space.

If the auto grow option is enabled in the file and the file is heavily fragmented, in that case the files can not grow beyond a certain limit, which may cause error 665 in the system.

Cause of File Fragmentation

• If DBA performs backup operation repeatedly, this could leads physical file fragmentation in the SQL server.
• If DBA shares database server space with other applications such as web server, Sharepoint, etc.causes disk file fragmentation as the space allocated to these applications is not continuous.
  

Solution

SQL Server physical file fragmentation can be fixed with he help of Windows utilities, there is a tool called Sysinternal’s Contig (contig.exe) tool which is a free utility from Microsoft. This tool will create a new files that are contiguous in nature.

It is a great tool that will show fragmentation of files and allow them to be defragmentated.

DBA can easily deploy this tool, to analyze the fragmentation of a specific file, DBA can use contig-a option.

To defrag the file, DBA can run simple command Contig

Note: To defrag any database, it must be in Offline state.

User Can Follow The Given Steps To Defrag The Database:

• In order to defrag the database, user needs to bring it OFFLINE
  
  

  ALTER DATABASE [Database name] SET OFFLINE
  

• Use Contig [Filename] command, to defrag the file
• Again bring back the database in ONLINE state
  
  

  ALTER DATABASE [Database name] SET ONLINE
  

Other Practices That Resist Fragmentation

• By keeping data files and log file on different physical disk arrays.
• User can fix the problem of out of order page by reorganizing the index with altered index statements or with the help of SQL server maintenance plan. This problem is arises when data file pages are Out of order.
• The database file should be sized well and autogrowth must be set to suitable value.
• Monitor fragmentation with the help of Microsoft tools.
• Set up plans for the SQL server maintenance.
  

Conclusion

The issue of SQL Server physical file fragmentation is a curable problem, DBA can easily fix this problem with the help of Microsoft tools.