What's your practice on Disk Defragmentation methods - specific to data file and indexes drives?
Fragmentation is dearest friend of database when you need to deal with Performance, so by using the DBCC statements and other methods here you can deal the database level fragmentation, what about the physical level fragmentation?
So what is the best way forward to fix the data file fragmentation in SQL Server, say when you have much of SHRINK operations are performed on Database level. The generic answer would be to use the defragmentation tools from windows or operating system based tools, but the issue will be they treat the SQL data file as a whole and will not defragment it granularly. You may be aware that FILE FRAGMENTATION for SQL Server data file occurs even when you perform a backup and restore of that database, in case of having multiple files for data & indexes. But to address this issue you cannot perform a downtime to SQL Server services in order to let the operating system defragmentation tool too defrag the physical file, and theoritically speaking there isn't much external fragmentation anyway. It's more helpful to regularly reindex your data to reduce the internal fragmentation as much as possible, that will reduce the outer layer of fragmentation of physicial file where the effectiveness of the read-aheads and the amount of data that can be buffered, to the maximum (as per my tests).
The efficiency of performance (resource level, such as Disk) will be efficient disk I/O, by making sure the disk alignment and RAID configuration is correct, scaling your disk arrays to properly handle the I/O load, and maintaining proper layout of the Log, Data, TempDB, and backup files. As usual the best practice of not letting the database with AUTO-SHRINK and leavintg the AUTO-GROW settings on the volatile inserts/update/deletes processes of database. There is a reason behind for not letting the default-setting of auto-grow (in case of auto-shrink too), as it will reduce the number of trips of volume-level file fragments created.
So for the newly created database it may not be much as the data and log files are initialized with empty space. In this case Data and log files are first initialized by filling the files with zeros when you create a database; add files, log or data, to an existing database; increase the size of an existing file (including autogrow operations); or restore a database or file group. File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros. Within SQL Server 2005 version, the data file are initialized instantly, that will be capable for fast/quick execution of file operations. This can be achieve with an instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files, but not in the case of log files. Bear in mind such an operation (advantage) will be good at when using Windows 2003 server version and later.
So with using DBCC statements such as DBCC SHOWCONTIG the SQL Server will not report fragmentation, but will give 0 percent fragmentation even when the on-disk files are horribly fragmented. As the operating system level layout is not available to SQL Server, that also depends upon the physically lays out the bits on disk; so it's the operating system's job to manage physical bits on disk. In this case, performing an OS-level defragmentation could help performance by making the files more contiguous on disk. However, defragmenting at the OS level doesn't always have the effect that you might expect. SQL Server pre-allocates space when you create a file. It is best to perform a scheduling of regular OS-level defragmentation using the tools such as Disk-Keeper and http://www.diskeeper.com/defrag/dk-boost-sql-server.asp. So it is better to integrate the OS level jobs during quiet period on SQL Server as file defragmentation is integrated in the operating system, defragmenting open files is perfectly safe. However, I/O activity generated must be considered if continuous high performance is mandatory. To close the last piece of this blog I would like to suggest to create the data file on contiguous space that has already been defragmented, such as speicifying the next 3 or 5 year growth of your database, this is specific to OLTP based databases.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.