topic Re: EVA4100 and SQL allocation unit size when formatting drive. in HPE EVA Storage

EVA4100 and SQL allocation unit size when formatting drive.

Digex — Thu, 25 Sep 2008 16:44:59 GMT

What is the best practice for formatting a partition after using diskpart to set alignment to 32kb? Is it to keep the allocation unit size to default of 4kb or set it higher?

Re: EVA4100 and SQL allocation unit size when formatting drive.

IBaltay — Thu, 25 Sep 2008 18:26:36 GMT

Hi,
maybe the following threads could help you:
http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=1056062

http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=1251071

Re: EVA4100 and SQL allocation unit size when formatting drive.

Uwe Zessin — Fri, 26 Sep 2008 15:55:28 GMT

You cannot use file system compression with clusters larger than 4KB. Here is a Microsoft Knowledge Base article with some additional discussion, enjoy:

http://support.microsoft.com/kb/140365/en-us

Re: EVA4100 and SQL allocation unit size when formatting drive.

Digex — Fri, 03 Oct 2008 13:12:51 GMT

UWE,
From an EVA4100 standpoint do not do a disk alignment and keep the partition cluster size to default of 4kb is best practice? Reason I ask is I see so many write up's for SQL 2005 that recommend to partition a volume with cluster size larger like 64k for database.

Re: EVA4100 and SQL allocation unit size when formatting drive.

Uwe Zessin — Fri, 03 Oct 2008 15:41:28 GMT

Ah, it is unlikely that you will compress SQL databases, will you? ;-) In that case I would follow the SQL best practice and go with a larger cluster size.

A few days ago I have read that one of the analyzers (don't recall the exact name right now) complains if a data partition is not aligned. Now, if not aligning a partition does not hurt in an EVA environment, I doubt it will hurt if you _do_ align.

So, I would align the data partition(s) and run with the suggested cluster size.

Re: EVA4100 and SQL allocation unit size when formatting drive.

Digex — Fri, 03 Oct 2008 15:51:08 GMT

So what is the recommended alignment for EVA? Im assuming 32kb= 64 sectors?

align=16
align=32
align=64
?

Re: EVA4100 and SQL allocation unit size when formatting drive.

Uwe Zessin — Fri, 03 Oct 2008 16:09:51 GMT

Yes, on the EVA3000/5000 it was 64 blocks.

The EVA chunk size is 128KB. If you use a cluster size of 64KB, I suggest you use a 64KB/128blocks offset so that NTFS clusters do not cross EVA chunks. If doing otherwise would have a negative impact on performance can only be said if it is tested.

Re: EVA4100 and SQL allocation unit size when formatting drive.

Digex — Fri, 03 Oct 2008 16:19:43 GMT

Someone from HP told me to do

CREATE PRIMARY PARTITION ALIGN=32

Which would probably be causing my issue. Also if you let windows it automatically it will set the offset to 32kb. I guess I will try 64kb and see what happens.

Our Universe database vendor recommended 16kb offset.

Re: EVA4100 and SQL allocation unit size when formatting drive.

IBaltay — Fri, 03 Oct 2008 16:33:26 GMT

Hi,
The general SQL recommendation is using a 64KB bock size during the format.
Partition alignment is also recommended.
You can use the diskpart command line as follows:
CREATE PARTITION PRIMARY ALIGN=64
and then format the disk

Re: EVA4100 and SQL allocation unit size when formatting drive.

Digex — Mon, 06 Oct 2008 13:43:09 GMT

Per this sql guide
"When using DISKPAR, adjust the alignment by 512 bytes, and when using DISKPART, the disk should be aligned to 64. The reason for this is due to the original master boot record design of WinTel based systems. The master boot record for all drives is 63 blocks (1 block = 512 bytes)."

Doesn't this mean that you should set align=32kb since 32kb is equal to 64 blocks? (64x1024=32,768kb.)

I do format the partition using 64k but I leave the alignment=32kb.

Re: EVA4100 and SQL allocation unit size when formatting drive.

TTr — Mon, 06 Oct 2008 14:14:45 GMT

The microsoft web site has many interesting articles. Search for "sql server storage alignment"

http://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=sql+server+storage+alignment

Re: EVA4100 and SQL allocation unit size when formatting drive.

Digex — Mon, 06 Oct 2008 14:20:03 GMT

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

Per that article it agree's with all of your statements.

Quote" 64-KB offset (128 sectors) is a common value that works on many storage arrays and has been used with success in the SQL Server test labs (at the time of writing this paper). Vistaâ ¢ and Windows Longhorn Server remove the need to manually establish correct alignment and use 1024 KB (2048 sectors) as a starting offset, which will probably work well for almost any array."

I wonder why HP EVA rep's say not do this at all.