Disk fragmentation – SQL Server Performance

Leave a comment (0) Go to comments

Question :

Does Disk Level fragmentation causes performance issues to SQL Server ?

SQL Server Performance affected by Disk fragmentation

Answer : Yes, SQL Server also suffer from disk fragmentation. Fragmented objects such as tables, indexes, files or databases exert a heavy hit on performance. 

There are different kinds of fragmentation. First type of fragmentation is a disk fragmentation and other is internal fragmentation to SQL Server data which is specific to data pages and index pages.

One type of fragmentation is a disk fragmentation, where the disk the database files and log are placed is highly fragmented, causing chunks of db files to be separated on the disk, such causing IO operations for reading extents to increase and additional moving of the disk heads. This is especially true when the disk is not dedicated to the database files, the server is used as a file server and other files are written often to it.

Other is Internal fragmentation of SQL Server. All SQL Server databases, over time, experience "internal" fragmentation of its data. This occurs when records are removed from database pages but the space it occupied is still there after deletion. Eventually this space is reused, but as it is reused, the data pages become fragmented, which can lead to unnecessary I/O, especially in case of table scans where many data pages are read one after another.


How to solve External fragmentation (Disk Level) problem,

Use Windows Disk Tools to check disk fragmentation, the tool visualize how your files are fragmented 0n disk. If you realize that your disk is fragmented then you have to plan a downtime to defragment. You can use native windows disk defragmenter (Disk Manager) or any tool like DiskKeeper to deal with disk fragmentation. BUT first you have to:

  1. Stop SQL Server service
  2. Copy database files from the drive you intent to defragment (this is your file level backup in case your tool cause a damage in the database file)
  3. Run the defragment tool
  4. Start SQL Server
  5. Check your databases, best tool to use for checking is DBCC CHECKDB, because it scans all pages inside the database and if there is an physical error it will found it.

How to solve Internal fragmentation problem,

In SQL Server, there are several ways to defrag internal fragmentation. One of these methods is to use the DBCC REINDEX command to rebuild clustered and non-clustered indexes. Once indexes are rebuilt, data pages are now logically contiguous, and disk I/O is minimized.

Impact measure of External Fragmentation

Some tests have been performed by Disk keeper cooptation and they published the following test results.

Test 1 : SQL Server Bulk Insert Tests (measured in seconds)

Low Fragmented 22.1
Defragmented 20.9
Medium Fragmented 31
Defragmented 25
High Fragmented 53.3
Defragmented 33.4

Test 2 : SQL Server Query response (measured in seconds)

Low Fragmented 35.3
Defragmented 33.3
Medium Fragmented 41.5
Defragmented 38.5
High Fragmented 61.3
Defragmented 50.8

Detailed Test Report can be downloaded from here

Conclusion : We should keep an active eye of disk level fragmentation and on schedule intervals, we should defrag disk at OS level, if fragmentation found

EOF - Disk fragmentation – SQL Server Performance, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.