SQL Server Physical File Fragmentation Complete Guide

Most of the time while working on SQL Server, users face a server’s performance issue, which is due to the bad structure of database or SQL Server Physical File Fragmentation.

Physical File Fragmentation in SQL Server has been a problem since the first file was written into floppy disk drive by using DOS. If contiguous clusters are not there at the time when OS writes a file into a disk system then, a file is said to be physically fragmented.

Physical fragmentation can give an additional load on the input or output subsystem to reduce performance, as the disks have to work more for reading and writing the data in a sequential manner. It can be small when only a single file is distributed into a handful of locations, which is there on the subsystem. In other situation, only one single physical file may divide into numerous fragments that result in physical file fragmentation. It is harder for disks to work if there is greater physical file fragmentation. All the files are written as contiguously.

Effects of SQL Server Physical File Fragmentation

If there is, a single file random read or writes file requires to be performed then, a degree of physical fragmentation will be having no effect on the performance of input or output operation. As the single file read or write operation is not affected by the fragmentation of physical file. Whereas if the file is fragmented heavily then, the disks will randomly read or write the whole data. Even if the data is logically sequenced then, it results in the extra work for disks drives.

Another factor that affects SQL Server is the interact of disk subsystem if there is a large cache, which is sitting there in between SQL Server then, its ios made up of SSD. Both of these factors can significantly cover the negative effect of a physical file fragmentation in SQL Server, which is not eliminated. Before a user creates a new database, which contains MDF, LDF, and NDF files.

Then, firstly utilize Windows defag.exe tool to check if there is physical file fragmentation present on disk array where a user can create database files. If it is there then, utilize defrag.exe to defrag all the physical files before making a new database. As the tool, helps to maintain the data in a sequential manner because the data is there in an improper manner. It helps to remove the physical fragmentation in the present database.

Impact of Physical File Fragmentation in SQL Server

The main impact of physical file fragmentation is that server responds late while fetching the records of the database.

How to do Defragmentation?

There is a free utility, namely sysinternal’s contig tool from Microsoft, which aimed to optimized a single file and further creates a new file that is contiguous. It is easy to utilize to analyze fragmentation of specified file with a database, which is online. For defragging the file, run Contig [file name]

SQL Server Physical File Fragmentation Complete Guide
SQL Server Physical File Fragmentation Complete Guide

Note: For defragging a file, database file should be offline.
Now test the defragging so that it makes easy to understand the effects of physical file on the performance.

SQL Server Physical File Fragmentation Complete Guide
SQL Server Physical File Fragmentation Complete Guide

After this, run a command to check that the data is offline, once the data is defragged then, bring it online. Use Contig [file name] to defrag the data.

SQL Server Physical File Fragmentation Complete Guide
SQL Server Physical File Fragmentation Complete Guide

Check Impact of physical file fragmentation in SQL Server by simply running DBCC CHECKTABLE. It makes easy to check the scope, total time, etc.

SQL Server Physical file fragmentation is the main matter to be considered when we talk about the performance of a system. In the above discussion, we have discussed the same and a way to defrag it.

You may like following SQL Server tutorials:

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com