Most of the time while working on SQL Server, users face 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 to an additional load on 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 handful of locations, which is there on subsystem. In other situation, only one single physical file may divide into numerous of fragments that results 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.
Other factor that affect 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 new database. As the tool, helps to maintain the data in sequential manner because the data is there in 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 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]
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.
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.
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.