July 10, 2012 at 4:11 pm
Hi All,
I have a small confusion about Disk alignment best practices:
I have refereed to
http://msdn.microsoft.com/en-us/library/dd758814.aspx
http://support.microsoft.com/kb/929491
Basically, just want to clarify if I am missing anything or not:
1. BytesPerCluster for SQLServer should be 65,535 bytes (= 64K) as sql server performs I/O based on an extent (8 8KB pages = 64KB).
This can be found form fsutils volume diskfree [driveletter:]
2. PartitionOffset which is StartingOffset (found from wmic partition get BlockSize, StartingOffset, Name, Index ) should be divisible by 65535 (64K) for windows 2003 servers and by 1048576 (1MB) for Windows server 2008 and up.
This is a rough calculation to find out if the disk is aligned properly or not. If the modulus of StartingOffset and 65535 is 0 then it is aligned properly and if it is not 0 then it is not aligned and requires a format with proper alignment.
blockSizeIndex Name StartingOffsetStartingOffset%65536.0
512 0 Disk #1, Partition #0 32256 0.4921875
512 0 Disk #2, Partition #0 32256 0.4921875
512 0 Disk #3, Partition #0 32256 0.4921875
512 0 Disk #0, Partition #0 32256 0.4921875
512 1 Disk #0, Partition #1 41126400 627.5390625
512 2 Disk #0, Partition #2 26008335360 396855.7031250
So from above table, none of my disks are aligned properly ? Is this correct ?
Please correct if anything I misunderstood.
I am aware that this is not the best way to find whether the disk is aligned or not as the whitepaper from Jimmy May Suggest below formula
The results of the following calculations must result in an integer value:
Partition_Offset ÷ Stripe_Unit_Size
Stripe_Unit_Size ÷ File_Allocation_Unit_Size
As there is no way to find out the Stripe_Unit_size (except asking your SAN or Infra Admin or using some third party vendor tools).:-D
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
July 10, 2012 at 5:35 pm
SQLQuest29 (7/10/2012)
1. BytesPerCluster for SQLServer should be 65,535 bytes (= 64K) as sql server performs I/O based on an extent (8 8KB pages = 64KB).
Optimal stripe sizes for SQL server are 64k and 256k, however if you're referring to the file allocation size then 64k is the norm.
SQLQuest29 (7/10/2012)
2. PartitionOffset which is StartingOffset (found from wmic partition get BlockSize, StartingOffset, Name, Index ) should be divisible by 65535 (64K) for windows 2003 servers and by 1048576 (1MB) for Windows server 2008 and up.
Hmm, not really, you use whatever offsets you require. It's just that Windows 2008 auto aligns to 1mb which is a sort of "one size fits all" scenario, only in some cases it may not be what you want 😉
SQLQuest29 (7/10/2012)
This is a rough calculation to find out if the disk is aligned properly or not. If the modulus of StartingOffset and 65535 is 0 then it is aligned properly and if it is not 0 then it is not aligned and requires a format with proper alignment.blockSizeIndex Name StartingOffsetStartingOffset%65536.0
512 0 Disk #1, Partition #0 32256 0.4921875
512 0 Disk #2, Partition #0 32256 0.4921875
512 0 Disk #3, Partition #0 32256 0.4921875
512 0 Disk #0, Partition #0 32256 0.4921875
512 1 Disk #0, Partition #1 41126400 627.5390625
512 2 Disk #0, Partition #2 26008335360 396855.7031250
So from above table, none of my disks are aligned properly ? Is this correct ?
Please correct if anything I misunderstood.
I am aware that this is not the best way to find whether the disk is aligned or not as the whitepaper from Jimmy May Suggest below formula
The results of the following calculations must result in an integer value:
Partition_Offset ÷ Stripe_Unit_Size
Stripe_Unit_Size ÷ File_Allocation_Unit_Size
As there is no way to find out the Stripe_Unit_size (except asking your SAN or Infra Admin or using some third party vendor tools).:-D
The 2 calculations highlighted above are the correct way to calculate the alignment.
On a SAN it's going to make no difference, your LUN could be, and quite possibly is, spread all over the place.
The values shown above are typical of a windows 2003 server with MBR default partitions. Disk 0 which looks to be your boot drive, has multiple partitions and in this case even on a dedicated array, without knowing exactly the sector layouts would be almost impossible to align correctly as each partition needs to start at the correct offset.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 6:59 am
Thanks a lot Perry for the clarification !
Still confused with http://msdn.microsoft.com/en-us/library/dd758814.aspx
Valid Starting Partition Offsets
Because versions of Windows earlier than and including Windows Server 2003 comply with the 63 hidden sectors reported by disk hardware, and because the most common sector size 512-byte sectors, the default (and suboptimal) starting partition offset is 32,256 bytes, exactly 31.5 KB.
Explicitly defining the starting offset from 31.5 KB to exactly 32 KB might seem like a legitimate approach. In fact, as mentioned earlier, 64 KB is typically the minimum (and a common) valid starting partition offset for SQL Server because of the correlations described later.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
July 11, 2012 at 7:41 am
SQLQuest29 (7/11/2012)
Thanks a lot Perry for the clarification !Still confused with http://msdn.microsoft.com/en-us/library/dd758814.aspx
Valid Starting Partition Offsets
Because versions of Windows earlier than and including Windows Server 2003 comply with the 63 hidden sectors reported by disk hardware, and because the most common sector size 512-byte sectors, the default (and suboptimal) starting partition offset is 32,256 bytes, exactly 31.5 KB.
Explicitly defining the starting offset from 31.5 KB to exactly 32 KB might seem like a legitimate approach. In fact, as mentioned earlier, 64 KB is typically the minimum (and a common) valid starting partition offset for SQL Server because of the correlations described later.
What is it you are confused with?
The doc states that the 63 sectors (31.5k) scenario is the default sub-optimal starting offset, meaning it's default and not optimal, hence why it was changed in Windows 2008.
Offsets of 32K are not used as they do not generate correct alignment values, the result must be an integer value and as such the starting offset must accomodate the strip size which must then in turn accomodate the file allocation size.
so, guess which ones aren't aligned
Offset stripe size aligned
32k 64k 0.5
32k 256k 0.125
64k 64k 1
256k 64k 4
1024k 64k 16
1024k 256k 4
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 9:15 am
Perry Whittle (7/11/2012)
SQLQuest29 (7/11/2012)
Thanks a lot Perry for the clarification !Still confused with http://msdn.microsoft.com/en-us/library/dd758814.aspx
Valid Starting Partition Offsets
Because versions of Windows earlier than and including Windows Server 2003 comply with the 63 hidden sectors reported by disk hardware, and because the most common sector size 512-byte sectors, the default (and suboptimal) starting partition offset is 32,256 bytes, exactly 31.5 KB.
Explicitly defining the starting offset from 31.5 KB to exactly 32 KB might seem like a legitimate approach. In fact, as mentioned earlier, 64 KB is typically the minimum (and a common) valid starting partition offset for SQL Server because of the correlations described later.
What is it you are confused with?
The doc states that the 63 sectors (31.5k) scenario is the default sub-optimal starting offset, meaning it's default and not optimal, hence why it was changed in Windows 2008.
Offsets of 32K are not used as they do not generate correct alignment values, the result must be an integer value and as such the starting offset must accomodate the strip size which must then in turn accomodate the file allocation size.
so, guess which ones aren't aligned
Offset stripe size aligned
32k 64k 0.5
32k 256k 0.125
64k 64k 1
256k 64k 4
1024k 64k 16
1024k 256k 4
64 KB is typically the minimum (and a common) valid starting partition offset for SQL Server -- This is where I am confused ... as Initially I thought that in windows 2003 servers, if the startingoffset is divisible by 65536 then it is not aligned properly and in Windows 2008 and up, if the startingoffset is divisible by 1048576 (which it will be as it is aligned by default), then disk is properly aligned 😉
Update:
The whitepaper from EMC also states the same ..
http://www.emc.com/collateral/hardware/white-papers/h6665-symtrx-microsoft-windows-server-wp.pdf
Page: 27, below fig 12. 😉
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
July 11, 2012 at 10:00 am
SQLQuest29 (7/11/2012)
64 KB is typically the minimum (and a common) valid starting partition offset for SQL Server -- This is where I am confused
ah, ok let me see if i can clear this up. Yes 64k is a common starting offset in most scenarios, but not all. In Windows 2003 you had to manually go and gather information and explicitly set your partition alignments.
if you were presented a RAID array with a 64k stripe size and you set your starting offset to 64k then 64 / 64 = 1
if you were given a RAID array of 128k stripe and use a 64k offset then 64 / 128 = 0.5
if you were given any RAID stripe size and use a 1024k offset then the divisible result is always a whole integer, hence why 2008 now auto aligns to 1024k (1MB).
SQLQuest29 (7/11/2012)
... as Initially I thought that in windows 2003 servers, if the startingoffset is divisible by 65536 then it is not aligned properly
That's just it in Windows 2003 you had to calculate and decide. You choose your stripe size first and then choose the starting offset that best suits your strip and file allocation sizes. For SQL Server you would use a stripe size of 64k and offsets of either 64k or 256k (wholely divisible by the stripe) 😉
SQLQuest29 (7/11/2012)
and in Windows 2008 and up, if the startingoffset is divisible by 1048576 (which it will be as it is aligned by default), then disk is properly aligned ;-)[/b]
Remember, in Windows 2008 the starting offset is always 1MB as default and always will be wholely divisble by any RAID stripe your array uses 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 10:29 am
Thanks .. now I am clear on it.
Also, made an edit to my answer
Update:
The whitepaper from EMC also states the same .. (Windows 2003 server)
http://www.emc.com/collateral/hardware/white-papers/h6665-symtrx-microsoft-windows-server-wp.pdf
Page: 27, below fig 12.
Apologies for asking again.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
July 11, 2012 at 10:41 am
it's no problem
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 10:56 am
Perry Whittle (7/11/2012)
it's no problem
Apologies, but I meant that still 10% un-clarity is there as referencing this Paper: http://www.emc.com/collateral/hardware/white-papers/h6665-symtrx-microsoft-windows-server-wp.pdf
It states that: (Below Fig 12: Page 27) for Win 2003 servers
The starting offset provided by the wmic command is in in bytes. To ensure proper alignment, this number should be evenly divisible by 65536.
So this must be true for Win 2008 Server that the starting offset should be divisible by 1048576 (1MB).
Please clarify it.
Again, thanks a lot for your help.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
July 11, 2012 at 11:30 am
That statement is based on the example immediately preceeding it.
Start from the heading Partition Alignment on page 25 and read through thoroughly. This is descriptive of the requirements
EMC Symmetrix with Windows 2003 and 2008
Depending on the version of Windows, there are several ways to correct alignment and ensure optimal performance. In all cases it is recommended that the partition offset or alignment value be equal to some increment of 64KB. This could mean that the partiton may start 128 sectors or 65536 bytes into the disk, or at some number larger but evenly divisible by 128 sectors or 64KB. In either case, the partiton will be considered aligned.
The statement you are mis understanding is relative to the following example provided for it under the Querying Alignment heading on page 26.
EMC Symmetrix with Windows 2003 and 2008
The example in Figure 12 uses the wmic CLI to return specific partition information including the starting offset, from an MBR basic disk and a GPT basic disk created specifying a 64KB alignment with diskpart.
In this scenario, the value returned by wmic must be divisible by 65336 bytes (64K)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 12:47 pm
Thanks a lot Perry !!
Very helpful.
Out of curiosity, is there any way that the relation between the disk# and the drive (e.g c: ,d:) can be found meaning they can be correlated ?
e.g disk#0 Partition #0 -- C:
disk #1 Partition #0 -- D: etc...
I will do some research.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
July 11, 2012 at 1:41 pm
SQLQuest29 (7/11/2012)
Thanks a lot Perry !!Very helpful.
Out of curiosity, is there any way that the relation between the disk# and the drive (e.g c: ,d:) can be found meaning they can be correlated ?
e.g disk#0 Partition #0 -- C:
disk #1 Partition #0 -- D: etc...
I will do some research.
yes, you can use diskpart.exe to query the disk\volume\partition layouts. Open diskpart by typing diskpart at a command prompt then use the following command sequence (my disk layouts are used as a reference
list disk
produces
Disk ### Status Size Free Dyn Gpt
-------- ------------- ------- ------- --- ---
Disk 0 Online 465 GB 0 B
Disk 1 Online 465 GB 0 B
select disk 0
Now detail the disk using
detail disk
produces
Hitachi HTSAAAAAAAAAAAAAAAAA
Disk ID: AAAAAAAAAAAAA1
Type : ATA
Status : Online
Path : 0
Target : 0
LUN ID : 0
Location Path : PCIROOT(0)#ATA(C00T00L00)
Current Read-only State : No
Read-only : No
Boot Disk : Yes
Pagefile Disk : Yes
Hibernation File Disk : No
Crashdump Disk : Yes
Clustered Disk : No
Volume ### Ltr Label Fs Type Size Status Info
---------- --- ----------- ----- ---------- ------- --------- --------
Volume 2 SYSTEM NTFS Partition 199 MB Healthy System
Volume 3 C OS NTFS Partition 451 GB Healthy Boot
Volume 4 E RECOVERY NTFS Partition 14 GB Healthy
Volume 5 H HP_TOOLS FAT32 Partition 103 MB Healthy
My disk 0 has 4 partitions as shown above and my 2nd disk shows
Hitachi HTS5AAAAAAAAAAAA
Disk ID: AAAAAAAAAAAAAA
Type : ATA
Status : Online
Path : 0
Target : 1
LUN ID : 0
Location Path : PCIROOT(0)#ATA(C00T01L00)
Current Read-only State : No
Read-only : No
Boot Disk : No
Pagefile Disk : No
Hibernation File Disk : No
Crashdump Disk : No
Clustered Disk : No
Volume ### Ltr Label Fs Type Size Status Info
---------- --- ----------- ----- ---------- ------- --------- --------
Volume 6 D NTFS Partition 465 GB Healthy
Also have a look at the following 2 commands
list volume
list partition (must select a disk first)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 2:27 pm
Perfect !
Again, thanks a lot for your help in understanding the concepts 🙂
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
July 12, 2012 at 7:53 am
you're welcome 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply