THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: file fragmentation and SAN - Part VI

A script to fragment a test file

Well, this post won't cover any actual test results and is not specific to SAN. But in the spirit of full disclosure, I've attached the script I used to control the fragmentation level of the test files mentioned in this series of posts. The script is written in Perl, but can be easily done in any language.

The script does not itself fragment any file. Rather, it fragments the entire disk drive into non-contiguous blocks, or fragments, of a given size. A file (such as a database file) created afterwards is then necessarily fragmented into these fragments.

It takes three parameters at the beginning of the script: (1) $DRIVE to specify which drive the test file will be placed, (2) $FRAGMENT_SIZE to specify the size of the fragments in bytes, and (3) $FREE_SIZE to specify how much free space you want to have after the script completes. So if you want to examine the behavior of a 10GB test file that is fragmented into 128KB fragments, you may want to specify $FREE_SIZE to be slightly bigger than 10GB.

The script begins by creating an empty directory tree with enough directories so that each directory will hold 200 files of the specified size. It then proceeds to create 200 files in each of these directories until the drive is completely filled up. Finally, it randomly deletes some of these files until the free space on the drive reaches the value specified by $FREE_SIZE.

Hope you find this script useful in case you'd like to check out the impact of file fragmentation yourself.

Published Tuesday, December 30, 2008 5:19 PM by Linchi Shea
Filed under: , , , ,

Attachment(s): fragDisk.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Grumpy Old DBA said:

I've been testing prior to a data centre move and with extreme fragmentation of the mdf files ( and I do mean extreme ) I've seen a severe degredation of performance for one of my tests. I hope to post the details to my blog tomorrow; can't tell you the SAN ( nda ). My tests are all done with SQL scripts.

December 31, 2008 3:09 AM
 

Linchi Shea said:

How do you define 'extreme fragmentation of the mdf files'? I guess I can wait until you post it :-)

December 31, 2008 1:24 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement