Defragmenting SQL Server database disk drives

Introduction

Should SQL Server disks be defragmented at the operating system layer?

That depends on the fragmentation state of the current drives. Generally, it doesn’t hurt and it may help, assuming you follow the precautions that are described in the "Precautions when defragmenting SQL Server database drives" section. The only negative is that you must shut down SQL Server unless the defragmentation tool supports transactional data capabilities. The good news is that, after you've run the defrag, you don’t really have to do it again unless you have a lot of Autogrowth and other files moving on and off the disks. Make sure that you understand any write-caching strategies that the utility uses. Caching by such a utility might involve a non-battery-backed cache, and this could violate WAL protocol requirements.
More information
A disk defragmenter moves all files, including the database file, into contiguous clusters on a hard disk. This optimizes and speeds up file access. With the exception of the Windows NT operating system, if you do not defragment your hard disk, the operating system may have to go to several physical locations on the disk to retrieve the database file, making file access slower.

Because physical data access is the most expensive part of an I/O request, defragmentation can provide performance gains for SQL Server and other applications. Positioning related chunks of data close to each other reduces I/O operation requirements.

Various defragmentation utilities are available on the market today. Some utilities enable defragmentation on open files, whereas others require closed-file defragmentation or perform better when used under closed-file conditions. Additionally, some utilities have transactional capabilities, whereas others do not.

Precautions when you defragment SQL Server database drives

When you evaluate a defragmentation utility for use with SQL Server, make sure that the utility provides transactional data capabilities. Specifically, choose a defragmentation utility that provides the following transactional data capabilities:
  • The original sector is not considered moved until the new sector has been successfully established and the data successfully copied.
  • The utility protects against a system failure, such as a power outage, in a safe way that keeps the files logically and physically intact. To guarantee data integrity, a pull–the-plug test is highly recommended when a defragmentation utility is running on a SQL Server-based file.
  • The Write-Ahead Logging (WAL) protocol requires the prevention of sector rewrites to avoid data loss. The utility must maintain the physical integrity of the file as long as it is performing any data movement. The utility should work on sector boundaries in a transactional way to keep the SQL Server files intact.
  • The utility should provide appropriate locking mechanisms to guarantee that the file retains a consistent image for any modifications. For example, the utility should ensure that the original sector cannot be modified when it's copied to a new location. If modifications were allowed, the defragmentation utility could lose the write.

Critical Disk defragmenters that do not provide these transactional data capabilities should not be used unless the SQL Server instance using the disks to be defragmented has been shut down so that you are not defragmenting open database files.

Open-file defragmentation raises several possible issues that closed-file defragmentation typically does not:
  • Open-file defragmenting affects performance. Defragmentation utilities may lock sections of the file, preventing SQL Server from completing a read or write operation. This may affect the concurrency of the server that's running SQL Server. Contact the manufacturer of the defragmentation tool to learn how files are locked and how this could affect SQL Server concurrency.
  • Open-file defragmenting can affect write caching and ordering. Open-file-based utilities require I/O path components; these components must not change the ordering or intended nature of the write operation. If the write-through or WAL protocol tenants are broken, database damage is likely to occur. The database and all associated files are considered to be a single entity. (This is covered in many Microsoft Knowledge Base articles, SQL Server Books Online, and various white papers.) All writes must retain the original write-ordering sequences and write-through capabilities.

Recommendations

  • Defrag the NTFS volume, unless it was just formatted, before you create a new database or move existing databases to the volume.
  • Make sure that you plan and size your SQL data and log files appropriately when the database is first created.
  • Create your pre-SQL Server 2014 transaction logs with Autogrowth in mind if it will be used.
  • Defrag the disk or disks on which your transaction logs reside. This will prevent external file fragmentation of the transaction log. This problem may occur if your files have had a lot of Autogrowth or when it's not a dedicated disk that contains a lot of databases, logs, or other files that have been modified. In that situation, files (including the transaction log file) may be interleaved and fragmented.
  • If you're defragmenting database drives that are cluster disks, the cluster disks should be set up to suspend health monitoring (also referred to as maintenance mode).
  • To minimize fragmentation, don't shrink your database files. Also, manually grow them in sizes that minimize the growth activity.
  • Keep your database files on dedicated disks.
  • Perform a full backup before you defragment those locations that contain SQL Server database and backup files.

خصائص

رقم الموضوع: 3195161 - آخر مراجعة: 09/30/2016 15:53:00 - المراجعة: 1.0

Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Express, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 R2 Workgroup, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Web, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Enterprise Core, Microsoft SQL Server 2012 Express, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web, Microsoft SQL Server 2014 Business Intelligence, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web, Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Enterprise Core, Microsoft SQL Server 2016 Express, Microsoft SQL Server 2016 Standard, Microsoft SQL Server 2016 Web

  • kbsurveynew kbtshoot kbexpertiseadvanced KB3195161
تعليقات