By: Armando Prato | Comments (4) | Related: > Fragmentation and Index Maintenance
Problem
I've read many articles about defragmenting indexes and how it's a good practice. However, I've noticed that my physical database files are terribly fragmented too. What can cause this? Can I benefit from defragmenting them?
Solution
SQL Server only reports on logical index and data page fragmentation and does not have any in-built utility to check on actual physical file fragmentation. It's up to you to check the fragmentation level of your physical disks using the disk defragmenting utility of your choice.
Physical database files that are heavily fragmented can certainly hurt performance since queries that access index and data pages are initially read from disk before being placed into the buffer cache. The task of finding these pages on heavily fragmented physical disks causes disk reads to become inefficient. By defragmenting heavily fragmented database files, you can improve system I/O when reading physical pages from disk. As an added benefit, the time it takes for maintenance operations such as index rebuilding can be greatly improved if the database files are contiguous. You should note that physically defragmenting data files does not repair logical fragmentation; the defrag simply makes the physical data file contiguous. Re-indexing is still required to repair any logical fragmentation that is internal to the file.
Physical database file fragmentation is generally caused by database events that grow and shrink the physical files such as auto grow and auto shrink. These are options which can be set when a database is created or altered manually or when a database is created or altered via the SQL Server UI tools (i.e. Management Studio or Enterprise Manager). Manually shrinking or growing files using T-SQL commands can also contribute to physical file fragmentation.
Auto shrink is a database option that is used to conserve disk space. It creates a thread that runs periodically to detect when the database has more than 25% free disk space allocated to it. The database will be shrunk leaving it with 25% free disk space while the rest of the previously allocated disk space is released back to the Windows operating system. I personally don't like using the auto shrink option as it can do more harm than good. When it executes, it can greatly consume system resources such as CPU and disk which can severely impact server performance. In addition, you have no control over when it executes; it's done automatically. More than likely, the freed disk space will probably end up being needed by the database again. If you have auto shrink enabled on your database to conserve disk space, consider moving the database to a new server with more disk space or adding new disks while shutting the setting off. Disk is cheap these days!
Auto grow is a database file option that can be set to allow your database to automatically grow its data and log files in either percentage or fixed size increments as more disk space is required. This will kick in when database operations such as a large transaction occur and the file does not have enough space to accommodate the data required. Auto growing can be another performance killer for the same reasons as auto shrinking. I do set my databases to auto grow. However, I try to minimize auto growth by sizing my database files with enough space when they're initially created to accommodate future growth. In addition, I try to make my auto growth size large enough so this feature kicks in as infrequently as possible. Sizing your databases as large as possible at creation can greatly minimize the effects of physical file fragmentation. Allowing frequent auto growth to occur with auto shrink set could also potentially cause an interesting scenario where files are constantly growing and shrinking leading to rapid physical fragmentation as well as server performance issues. Monitoring your server's disk space on a regular basis should be a part of your maintenance activities.
If you decide to defragment the physical disks that your SQL Server data files reside on, you should first take the precaution of first backing up all databases that have files that reside on the disks to be defragmented. Stop the SQL Server service along with its related services and then run your defragmenter. After the disks have been defragmented, you can restart all services. If you're a shop that cannot afford to take your database offline, there are commercial utilities that exist that can defragment your disk files while the database is still online.
Next Steps
- Examine your database settings and evaluate the turning off of auto-shrink if it's set on
- Examine your database file sizes and consider manually expanding them to minimize future auto-growth
- Read about the other database options
- Read this tip about rebuilding indexes
About the author
Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
