Database disk partitions

I'm looking at a new, freshly configured clustered server ('a' node) and
examining the partitions with DISKPART. I don't understand what I'm seeing,
nor do I know if it is best.
First (from chkdsk) all volumes have a 4 KB AUs (blocks) and I believe that
the volumes the .*df files go on should have 64 KB AU's.
DISKPART however, is showing mostly a 32 KB offset on all drives, except for
disk 4, which is showing (none of these are DB drives):
Part # Type Size Offset
Partition 1 OEM 31 MB 32 KB
Partition 2 Dynamic (C:) 20 GB 31 MB
Partition 3 Dynamic (E:) 48 GB 20 GB
Questions:
Is 32 KB the default offset?
Is a 32 KB offset OK when using a 64 KB AU?
What's up with the 31 MB and 20 GB offsets?
In writing this post, I'm remembering (vaguely) that the offset is how far
into the disk to go before starting the partition. If so, I fail to see how
defining a 64 KB offset benefits the database server.
Thanks,
JayThese might be of interest in this area.
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx
http://sqlblogcasts.com/blogs/christian/archive/2007/10/09/disk-sector-alignment.aspx
http://www.emc.com/techlib/pdf/H2610_ref_arch_emc_sol_for_ms_wp_ldv.pdf
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:uTb5RTxGIHA.5328@.TK2MSFTNGP05.phx.gbl...
> I'm looking at a new, freshly configured clustered server ('a' node) and
> examining the partitions with DISKPART. I don't understand what I'm
> seeing,
> nor do I know if it is best.
> First (from chkdsk) all volumes have a 4 KB AUs (blocks) and I believe
> that
> the volumes the .*df files go on should have 64 KB AU's.
> DISKPART however, is showing mostly a 32 KB offset on all drives, except
> for
> disk 4, which is showing (none of these are DB drives):
> Part # Type Size Offset
> Partition 1 OEM 31 MB 32 KB
> Partition 2 Dynamic (C:) 20 GB 31 MB
> Partition 3 Dynamic (E:) 48 GB 20 GB
>
> Questions:
> Is 32 KB the default offset?
> Is a 32 KB offset OK when using a 64 KB AU?
> What's up with the 31 MB and 20 GB offsets?
> In writing this post, I'm remembering (vaguely) that the offset is how far
> into the disk to go before starting the partition. If so, I fail to see
> how
> defining a 64 KB offset benefits the database server.
> Thanks,
> Jay
>
>|||Thanks Andrew, good reading.
I see my confusion on the offset. I was actually looking for the allignment.
Where can I find what the alignment a disk partition, or volume was created
with?
Where is the alignment specified? Is it in the creation of the partition, or
the creation of the disk colume?
Thanks,
jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eFtywJyGIHA.5544@.TK2MSFTNGP02.phx.gbl...
> These might be of interest in this area.
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx
> http://sqlblogcasts.com/blogs/christian/archive/2007/10/09/disk-sector-alignment.aspx
> http://www.emc.com/techlib/pdf/H2610_ref_arch_emc_sol_for_ms_wp_ldv.pdf
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uTb5RTxGIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> I'm looking at a new, freshly configured clustered server ('a' node) and
>> examining the partitions with DISKPART. I don't understand what I'm
>> seeing,
>> nor do I know if it is best.
>> First (from chkdsk) all volumes have a 4 KB AUs (blocks) and I believe
>> that
>> the volumes the .*df files go on should have 64 KB AU's.
>> DISKPART however, is showing mostly a 32 KB offset on all drives, except
>> for
>> disk 4, which is showing (none of these are DB drives):
>> Part # Type Size Offset
>> Partition 1 OEM 31 MB 32 KB
>> Partition 2 Dynamic (C:) 20 GB 31 MB
>> Partition 3 Dynamic (E:) 48 GB 20 GB
>>
>> Questions:
>> Is 32 KB the default offset?
>> Is a 32 KB offset OK when using a 64 KB AU?
>> What's up with the 31 MB and 20 GB offsets?
>> In writing this post, I'm remembering (vaguely) that the offset is how
>> far
>> into the disk to go before starting the partition. If so, I fail to see
>> how
>> defining a 64 KB offset benefits the database server.
>> Thanks,
>> Jay
>>
>|||Hi Jay,
Alignment is only relevant to the first partition and what you're
seeing is the default 32KB alignment for Windows 2003 and prior
versions shown as the "offset" from the start of the disk. This has
nothing to do with the recommendation for 64KB AU's when you format
the partition. The alignment value should be your RAID stripe/cache
size so its only relevant to disks configure in RAID. Vista and
Longhorn automatically align to 1024KB for partitions > 4GB so you
might just want to do that if you can't get a definitive
recommendation from your storage vendor.
Regards,
Christian Bolton
http://coeo.com
http://sqlblogcasts.com/blogs/christian
On Oct 30, 11:10 pm, "Jay" <nos...@.nospam.org> wrote:
> Thanks Andrew, good reading.
> I see my confusion on the offset. I was actually looking for the allignment.
> Where can I find what the alignment a disk partition, or volume was created
> with?
> Where is the alignment specified? Is it in the creation of the partition, or
> the creation of the disk colume?
> Thanks,
> jay
> "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com> wrote in messagenews:eFtywJyGIHA.5544@.TK2MSFTNGP02.phx.gbl...
>
> > These might be of interest in this area.
> >http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliob...
> >http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-i...
> >http://sqlblogcasts.com/blogs/christian/archive/2007/10/09/disk-secto...
> >http://www.emc.com/techlib/pdf/H2610_ref_arch_emc_sol_for_ms_wp_ldv.pdf
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> > "Jay" <nos...@.nospam.org> wrote in message
> >news:uTb5RTxGIHA.5328@.TK2MSFTNGP05.phx.gbl...
> >> I'm looking at a new, freshly configured clustered server ('a' node) and
> >> examining the partitions with DISKPART. I don't understand what I'm
> >> seeing,
> >> nor do I know if it is best.
> >> First (from chkdsk) all volumes have a 4 KB AUs (blocks) and I believe
> >> that
> >> the volumes the .*df files go on should have 64 KB AU's.
> >> DISKPART however, is showing mostly a 32 KB offset on all drives, except
> >> for
> >> disk 4, which is showing (none of these are DB drives):
> >> Part # Type Size Offset
> >> Partition 1 OEM 31 MB 32 KB
> >> Partition 2 Dynamic (C:) 20 GB 31 MB
> >> Partition 3 Dynamic (E:) 48 GB 20 GB
> >> Questions:
> >> Is 32 KB the default offset?
> >> Is a 32 KB offset OK when using a 64 KB AU?
> >> What's up with the 31 MB and 20 GB offsets?
> >> In writing this post, I'm remembering (vaguely) that the offset is how
> >> far
> >> into the disk to go before starting the partition. If so, I fail to see
> >> how
> >> defining a 64 KB offset benefits the database server.
> >> Thanks,
> >> Jay- Hide quoted text -
> - Show quoted text -