SQL Server Disk I/O on Windows Server 2008

  • Article

Imagine you work in a library and it’s your turn to put the books away that have just been returned. You have a complete catalogue of where every book should go but it’s not quite right, because someone didn’t notice that the first book in the list is supposed to be in two volumes , but actually is just one slightly bigger book.  So every time you put a book away it’s location is one place out.  This would slow you down especially if you couldn’t remember this problem existed each time you put a book away (like a goldfish with a 6 second memory). 

Now imagine you SQL server and every time you access the disk you are one track out, that would slow you down as well woudn’t it.  This can be quite a common problem and can slow disk I/O down by anywhere from 15 -30%. So why is this happening, how do find out if it affecting it and how do you fix it?

Traditionally windows has 63 hidden sectors on the front of a disk to store such things as the partition table and master boot record.  The problem is that it claims to be using 64! So When Windows writes the first chunk of data to a new partition:

  • It writes the first 512Bytes to the 64th sector of the first track.
  • It writes the rest to the second track.
  • This single write request requires access of both tracks.
  • To read this data requires accessing both tracks

So is this affecting you and what can you do about it? Check out the disk partition tool for windows server 2003.  Here’s a handy quote from that link:

“Disk alignment is a required optimization and must be applied by OEMs during Setup. Disk alignment provides a significant increase in system performance. Failure to perform disk alignment can decrease performance by 10 to 15 percent in RAID array systems.”

Although this is pitched at OEM's it might be worth checking your systems as the performance tuning consultants I know often see this when they are on client sites.

My concern and expertise is SQL Server , but this will affect any I/O operations, so other I/O intensive applications like Exchange will also be affected as well as third party databases, such as Oracle and MySQL

Finally the reason I included Windows Server 2008 in this post  is that provided you have started from bare disks or have bought a new server then this shouldn't affect you.

   

Technorati Tags: Oracle,MySQL,SQL Server,Windows Server 2003,Winodws Server 2008.