June 1, 2012 at 1:56 pm
Hi folks,
As we know the correct partition alignment can save 20-30% in performance. With Windows 2008 the alignment is done for you (I'm not sure if our tech guy knows how to set it, or what to despite my harrassing as he grips to the fact it is automatically done in Windows 2008).
Anyway - moving to SAN storage - if the alignment is set correctly in vmware/linux/unix (depending) - does it need to be set anywhere else? If I run check the partition offset out from the windows machine connecting to it - is it accurate and is showing the offset as based on the Storage array?
I ask as we have a big reporting server on SQL 2005 that used to run a job in 8 hours. The data drive was moved into a new storage array by our hardware guys. But the job now takes 13 hours! I know that they changed the disk cluster from 8K on the old to 64K on the new. But I wonder if the alignment is out too (they assure me its all done automatically).
In short - Any advice on setting up partition offsets for SAN drives via VmWare would be VERY much appreciated!
Thanks
Shark
June 1, 2012 at 2:37 pm
number of disks?
cache size?
RAID stripe size?
Storage paths?
There are so many factors that can have an effect.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 1, 2012 at 2:56 pm
Good questions - I have that all written down for the old server but not for the new one.
Cache could be an issue. Previously this server had its own dedicated storage - not it is on an array with other servers. No doubt bandwidth coming out from the host to the storage array too now that its shared.
June 1, 2012 at 5:29 pm
I just did this type of research recently to validate some storage that was recently presented to one of my servers. IIRC Server 2008 uses an offset of 1024KB as a default when provisioning a new disk which would mean that any stripe unit size <= 1024KB would divide equally and would therefore be aligned. I do not think I have encountered any storage configuration using a stripe unit size higher than 512KB, let alone 1024KB, but I do not do a lot of this kind of work.
Perry, not to hijack this thread because I do not want to, but could you expand on the reasoning behind needing to know cache size and storage paths before choosing a partition offset? I'll step away from the conversation either way, I was curious though so had to ask.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 1, 2012 at 6:20 pm
Shark Energy (6/1/2012)
Good questions - I have that all written down for the old server but not for the new one.
Investigate thoroughly and compare them to see the differences.
Shark Energy (6/1/2012)
Cache could be an issue.
Lack of or misconfigured cache is not uncommon.
Shark Energy (6/1/2012)
Previously this server had its own dedicated storage - not it is on an array with other servers. No doubt bandwidth coming out from the host to the storage array too now that its shared.
That's it, right there! Moving from local storage to a storage area network, you just increased the complexity by ten fold. You have the HBAs to consider as well as the many FC switches that comprise your storage network, besides multiple paths and software drivers!!
Shared storage (multiple IO patterns) can, and usually does suck 😉
SAN vendors have done a great job over the years with the hard sell, it's not always the right choice though.
Btw, is this SQL server a virtual machine as I notice you mentioned VMware above.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 1, 2012 at 6:47 pm
opc.three (6/1/2012)
IIRC Server 2008 uses an offset of 1024KB as a default when provisioning a new disk
Yes it does it's a sort of one size fits all, default configuration.
opc.three (6/1/2012)
which would mean that any stripe unit size <= 1024KB would divide equally and would therefore be aligned.
For SQL server you need to be using a stripe size of either 64k or 256k these have been proven to be the optimal sizes. You also need to consider your allocation unit size too. There are various calculations to make, read this link for further info.
opc.three (6/1/2012)
could you expand on the reasoning behind needing to know cache size and storage paths before choosing a partition offset?
I don't have a need to know and wasn't aware I made one!
The 4 items I questioned above are just a small link in the SAN chain.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 1, 2012 at 7:02 pm
Perry Whittle (6/1/2012)
opc.three (6/1/2012)
IIRC Server 2008 uses an offset of 1024KB as a default when provisioning a new diskYes it does it's a sort of one size fits all, default configuration.
opc.three (6/1/2012)
which would mean that any stripe unit size <= 1024KB would divide equally and would therefore be aligned.For SQL server you need to be using a stripe size of either 64k or 256k these have been proven to be the optimal sizes. You also need to consider your allocation unit size too. There are various calculations to make, read this link for further info.
opc.three (6/1/2012)
could you expand on the reasoning behind needing to know cache size and storage paths before choosing a partition offset?I don't have a need to know and wasn't aware I made one!
The 4 items I questioned above are just a small link in the SAN chain.
Went with 64kb. I think I read that article 10 times when I was going through that process. I linked your questions to the partition offset question in the op, maybe unduly. Thanks for taking the time.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 2, 2012 at 1:51 am
Remember that your offset is ultimately dependent upon your RAID stripe size, you probably shouldn't just accept the windows default. There are a whole bunch of calculations to make including the allocation unit size, it gets even more involved if you change the block size too.
The array stripe size is implemented at the early stages (during storage setup and config) long before you've created your partitions.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 2, 2012 at 1:53 am
Shark Energy (6/1/2012)
Good questions - I have that all written down for the old server but not for the new one.Cache could be an issue. Previously this server had its own dedicated storage - not it is on an array with other servers. No doubt bandwidth coming out from the host to the storage array too now that its shared.
One more thing, how many HBAs does the server have installed and what are the queue depth settings used?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply