SQL Server Physical File Fragmentation Complete Guide
Check out Best Alternative to InfoPath -> Try Now
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.
SharePoint 2016 Tutorial Contents
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]
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.
You may like following SQL Server tutorials:
- SQL Server Functions Tutorial
- SQL Server tutorial and query examples
- Difference between primary key and foreign key in SQL server
- SQL Server stored procedure tutorial
- Difference between clustered and non clustered index SQL server
- Difference between primary key and unique key in SQL Server
- The report server is unable to access encrypted data. Apply a back-up key or delete all encrypted content
- Reporting Server Integration feature missing in SharePoint 2013/2016
- SQL Server 2016 installation step by step with screenshots
- windows successfully diagnosed a low virtual memory condition sql server event id 2004
- Delete and create a new log file with minimum size in SQL Server
Download SharePoint Online Tutorial PDF FREE!
Get update on Webinars, video tutorials, training courses etc.