SQL SERVER DISK CONFIGURATION PROPERTIES

I am creating a Raid 1 and a raid 10 for a sql server. When I am creating the array I have to select strip size(8,16,32 etc..) and sectors (64/32) what should I use
tonzur40Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andyalderSaggar maker's framemakerCommented:
64k is the recommended stripe element size for SQL since it stores 8 x 8k pages in one extent so when it asks for an extent you want it to be able to fetch that extent from a single disk so the other disks can get on with something else. HP's term "strip" means the same as stripe element or stripe unit. Multiples of 64K work pretty well too since again one disk stores a whole extent.
https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

The number of sectors per track doesn't matter just leave it at default. It's a leftover from an old OS that had to have all the boot code on the first track. Also set the write cache to the highest percentage available.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Agree with andyalder

Have a read of that technet link. Scroll all the way down to : Essential Correlations: Partition Offset, File Allocation Unit Size, and Stripe Unit Size
Note the two correlations:
1) Partition_Offset ÷ Stripe_Unit_Size
2) Stripe_Unit_Size ÷ File_Allocation_Unit_Size

However, a starting partition offset of 1,048,576 bytes (1 MB) and a stripe unit size of 65,536 bytes produces a result of exactly 8, an exact integer, which is consistent with alignment.

Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes. The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.

Then read the full article - it has some measurements / tools / scenarios

Also worth reading : http://sqlserverio.com/2010/06/16/fundamentals-of-storage-systems-stripe-size-block-size-and-io-patterns/  particularly the graphs down the bottom
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
what is your sql server version, edition ? will it be used for Read, Write, R-W?
what is your OS?
if >=sql2008 and 64-bit -. the "Disk Partition Alignment Best Practices for SQL Server' is your answer

you can try 64kb  stripe size
in order to have a very specific answer for your system
you need to test your storage and see what you need

for example, you can try to use

How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem

https://support.microsoft.com/en-us/help/231619/how-to-use-the-sqliosim-utility-to-simulate-sql-server-activity-on-a-d
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

tonzur40Author Commented:
Currently is SQL 2008 but we will updating to 2012 in the next month so I have to plan for both scenarios. it is R-W. Same situation with the OS right now is 2008 but we will upgrade to 2012 as soon as we upgrade SQL
0
andyalderSaggar maker's framemakerCommented:
The version of MS SQL doesn't really matter as far as disk layout is concerned, they all use 64K extents. Older operating systems (pre-Win2008) had to have the disk partitions aligned, newer ones do it automatically when you create the volume.

There's a couple of instances when a smaller strip size is faster, a single table scan with nothing else going on and backup/restore since in those instances a single thread can use all the disks at once rather than one at a time but in the vast majority of cases there's more than one user/thread wanting to use the database at the same time.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
andyalder: it is not a monochrome view -->if you use 32 bit or sql 7  6.5. sql servers, OS too 32 vs 64 bit  -- they are differently using storage...another story  ..----------------------------------------------------------------------------------------------------------------------------------
the main idea when you set storage (server) - it is knowledge  of  design; how data will be "used"

for OS > 2003  and 64 bit ---you may know R-W activities let you set storage differently  including Arrays
vs Read Only DB setup \, etc
you may even like to use RAID 0 for some cases - > there are different variations
tonzur40 -Conclusion:
1, use MSFT best practice doc ( posted above)  64kb  stripe size  should be a good start
2, before you install sql server -- try to use IO stress test;

3. properly set system DB;DB data files; log files ;tempdb ( e.g. not on C: drive - but dedicated LANs with maybe, if possible, dedicated Array controllers for user DBs and tempDB)
4. use RAID5 RAID10 for
0
andyalderSaggar maker's framemakerCommented:
SQL 6.5 32 bit was 8K (write) pages and 64K (read-ahead) extents too, it's always been the best block size to use on the storage. Of course you could employ a consultant to tune it but that'll cost more than a couple of extra disks [or SSDs] for a day's work.

4. Use RAID5 for nothing, join BAARF - http://www.baarf.dk/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.