How does Microsoft recommend defragmenting SQL Server data files at the OS file-system level? Do I need to add data-file defragmentation to my regular database maintenance, or does SQL Server avoid file fragmentation by reserving contiguous data space?
A I'm not sure that Microsoft has an "official" defragmentation recommendation for SQL Server data files, but I believe that OS-level defragmentation can often improve SQL Server performance. SQL Server uses standard Windows I/O APIs to manage its physical I/O; SQL Server doesn't have direct control over the exact physical placement of bytes on disk when Windows creates and expands SQL Server disk files. Imagine that you're creating a new database—which, of course, means that you're creating a new physical file. Windows will likely attempt to use contiguous disk space, if the space is available, to create the data file. However, Windows can't create the file in contiguous space if the file is too large to fit into any of the existing blocks of contiguous disk space. In such a case, the SQL Server data file won't be contiguous, and SQL Server can't do anything about it.
Let's assume that the data file can fit in a contiguous block of disk space at creation time. Physical fragmentation could still occur if the SQL Server data file is marked to autogrow and Windows can't satisfy those additional requests for space with contiguous space. Note that none of the defragmentation techniques in SQL Server (e.g., DBCC INDEXDEFRAG) address the problem of physical fragmentation within the OS file system. Also, DBCC SHOWCONTIG doesn't report fragmentation that happens when data files are spread over noncontiguous disk space within the file system. These commands reveal other types of fragmentation within the SQL Server extents and rows within the physical data file. (A discussion of these commands is outside the scope of this Q&A.) Will SQL Server performance improve if you defragment SQL Server's data files? It depends. Certain classes of queries—such as table scans and large-range scans that read data in large, contiguous blocks—might benefit from OS-level file defragmentation. However, online transaction processing (OLTP)—style queries that issue single INSERT, SELECT, UPDATE, and DELETE commands are unlikely to benefit because short transactions that affect relatively small amounts of data rarely need to read large blocks of data in one operation.
