Solved

SQL Server 2005 on Win 2008 STD R2

Posted on 2012-03-27
3
406 Views
Last Modified: 2012-06-21
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
Comment
Question by:3Musketeers
  • 2
3 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 37774631
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
 

Author Comment

by:3Musketeers
ID: 37785026
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
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 37785496
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

INTRODUCTION The purpose of this document is to demonstrate the Installation and configuration of the Data Protection Manager product. Note that this demonstration was prepared on the basis of Windows OS is 2008 R2 and DPM 2010. DATA PROTECTI…
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

770 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