• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

disk alignment partitioning for SQL performance

KevinK recommends for SQL on Windows Server 2003  to have 64K sector offset for the Data drives.

http://sqlblog.com/blogs/kevin_kline/archive/2008/10/06/how-to-improve-application-and-database-performance-up-to-40-in-one-easy-step.aspx

Ours has 32K as the Starting Offset. Since 64K is multiples of 32K, will that be ok, or is it recommended to change it to 64KB.

thanks
0
anushahanna
Asked:
anushahanna
  • 3
  • 2
2 Solutions
 
Vaidas911Commented:
I know that it is recommended to have 64K, but after realigning disk and setting to 64K - did not gain any performance (tested with SQL backup to see MB/s) and users reported no difference. Our ERP DB is ~20GB.
By the way, Windows 2008 Server sets 64K by default.
0
 
anushahannaAuthor Commented:
Ours is 122GB.

Vaidas911, did you do it on W 2003 Server?

I thought ours was 32KB, but the it is only 31.5K of metadata.
0
 
Vaidas911Commented:
Ours also W 2003 Server, and yes, 31.5K is standart.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tiago_avizCommented:
What would make a monster of a difference is increasing your RAID controller's stripe size and getting more cache to your controller, adding battery to have a battery backed cache.. That surely makes things better.

If it's not possible, then it's a good idea to span the data on more spindles, specially on your case with such a huge database.
0
 
anushahannaAuthor Commented:
tiago_aviz, it is just not about the stripe size, but  if it will lead to 2 IO ops instead of 1.

We have 100 spindles underneath our LAN, but still the Disk IO has remained the same..
0
 
anushahannaAuthor Commented:
Vaidas911, perhaps you should have gone higher than 64k?
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now