Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

SQL Server 2005 on Win 2008 STD R2

I am running SQL Server STD 2005 on a Win 2008 STD R2.

For SQL data - separate storage -  RAID 5 - where the RAID policy is set to Read Ahead.

When formatting the drive - what - Allocation Size Units is best recommended to get max performance for SQL.  Some sites say 4K while some say 64K - so how to pick what and when?


I have an an accounting system and a SharePoint database  using this SQL server. All databases files combined is about  >50GB


Thanks
0
3Musketeers
Asked:
3Musketeers
  • 2
1 Solution
 
arnoldCommented:
Win2k8 has alignment of the partition.
In case of large files, you should use the higher value. If you are also using the pace for file sharing, you should use the 4k.
What is the write policy, write through or write back?
You are looking at individual files for unit allocation and not cumulative.
http://www.microsoft.com/download/en/details.aspx?id=21949
http://technet.microsoft.com/en-us/sqlserver/bb671245
http://sqlblog.com/blogs/greg_low/archive/2009/07/05/disk-partition-alignment-best-practices-for-sql-server-whitepaper.aspx
0
 
3MusketeersAuthor Commented:
write policy is write through
read policy is read ahead
on the RAID controller

will not be using the drive for sharing files at all...
0
 
arnoldCommented:
If it only will have large files, you should use the larger allocation units.
If you have the time, you could format the storage in one way small allocation units
then use the sql storage testing tools
http://sqlserverio.com/2010/06/15/fundamentals-of-storage-testing-io-systems/
http://support.microsoft.com/kb/231619
http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

You could use the simpler of the two to see the differences.
and then decide for yourself.

http://msdn.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

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