Solved

Partition alignment offset and Allocation unit size in Windows 2003 Server system with SQL 2005 DB

Posted on 2009-07-01
2
1,198 Views
Last Modified: 2012-05-07
Hi,

I've a couple of Windows 2003 servers with SQL 2005 DB installed and I plan to install some new ones. They are all VMware virtual machines and they have usually 3 partitions: C: with system, D: with SQL database and E: with some installation files. I've searched in google that to get better performance of system and DB I should customize partition(s) allignment offset.

A couple of advices I could find in this article: http://www.experts-exchange.com/Storage/Misc/Q_23977512.html?sfQueryTermInfo=1+64+align+offset

So... I've made use of Dell's advice and fixed allocation unit size for partition on which SQL DB resides to 64K. Partition's alignment offset has been set to 64K as well. My questions are:

- Should I also setup the same offset for C: partition (where system resides)? What about allocation unit size for C partition?
- Does somebody have a link to webpage where partition alignment, offsets and all this stuff is described in a pretty simple way?

Regards.
0
Comment
Question by:imgadmin
2 Comments
 
LVL 2

Accepted Solution

by:
Sguzek earned 200 total points
ID: 24754082
"Should I also setup the same offset for C: partition (where system resides)? "

It is not neccessary however it doesn't hurt at all, and it is better to do it than not to do.  Select offset equal to cluster size or its multiply. But do not expect any fabulous performance gains due to partition aligment
.
"What about allocation unit size for C partition"

Bigger allocation unit (cluster size) - more space is wasted -especially in each place when ther is a big numbe of small files - and system partition it is such a a place where you will have a lot of small files. Even 1 byte file will take one cluster.  Moreover writting such a file requires to write 64kB of data - quite a big overhead, isn' it ?

I would rather go for standard NTFS 4kB cluster for system partition.

Regards,
Slawek
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question