Featured Script
replace_substring
This stored procedure can be used to search and replace substring in the char, nchar, varchar and nvarchar columns in all ta… (read more)
Disk Fragmentation
Disk fragmentation is one of the primary causes for database performance degradation. When tuning a database for performance there is always tension for reducing fragmentation on many levels.
- Tables can be fragmented, split across multiple non-contiguous pages and extents
- Indexes can be fragments the same as tables
- The files containing your tables can be fragmented on the host OS
- In a virtual environment, the database files may be fragmented again on the physical disk hosting the virtual machines
Fragmentation occurs due to adding, and deleting data, and sometimes when updating data. If the data you are trying to generate cannot fit where it is currently stored, or you can’t add data contiguous to existing records, new data is stored elsewhere in your data file. When you expand your database on a drive it is not guaranteed that the disk space allocated to it will be contiguous to the existing data file allocation.
That’s the problem in a nutshell. What brought it to mind was my reading an editorial by a vendor claiming to be able to de-fragment real time at such speed that the cost of de-fragmenting is recovered by faster performance due to reduced fragmentation.
In a typical database environment for SQL Server, SharePoint Databases, Reporting Services databases, and SSAS databases we generally defragment on a scheduled bases during reduced load times. So, the day starts out good (lets say we do nightly de-fragmentation), because fragmentation has been reduced. As the day progresses data is added, updated and deleted. Performance degrades as time progresses until the next nightly de-fragmentation runs.
So, let’s hear from you. How are you addressing Fragmentation issues? Do you have a standard best practice you prefer, or does it vary depending on the application? What if you are using a SAN? Have you tried using a real time disk de-fragmentation driver?
Cheers,
Ben