July 21, 2008 at 2:49 am
Hi,
if i use Mirror for Log Files and RAID5 for Data Files,what disk type should i use in computer management - Basic,Dynamic,GPT - to gain better performance?
THX
July 21, 2008 at 3:19 am
Depends on a couple of factors:
- Are your disks on a SAN?
- How big are your DB's likely to be?
- How active are your DB's ?
- Whay type of data are they holding? Text/BLOB Data ?
If it's on a SAN; I would suggest Dynamic as you can allocate more space to the LUN/VDisk then just expand it in Windows. If it's over 2TB; then I believe GPT is the way to go.
If you can I would always go for 15k disks where possible.
Steve
July 21, 2008 at 4:19 am
Steve Maxwell (7/21/2008)
Depends on a couple of factors:- Are your disks on a SAN?
- How big are your DB's likely to be?
- How active are your DB's ?
- Whay type of data are they holding? Text/BLOB Data ?
If it's on a SAN; I would suggest Dynamic as you can allocate more space to the LUN/VDisk then just expand it in Windows. If it's over 2TB; then I believe GPT is the way to go.
If you can I would always go for 15k disks where possible.
Steve
Thx for Ur replay.
1. the disk aren't SAN - there are regular disk inside the server powered by raid controller HP Smart Array 512MB Cache
2.my DB will be at least 10GB
3. lots of reads less write
4.the data that stored there will be Text
July 21, 2008 at 5:13 am
if you have underlying hardware RAID leave the windows disk types as Basic, you do not want or need dynamic disks.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2008 at 6:17 am
What is the technical reason for leaving the Windows disk as Basic, if you have a raid disk system running your other data files?
July 22, 2008 at 8:40 am
You may want to consider RAID 10, instead of RAID 5 for your SQL volumes - RAID 5 is not a recommended configuration. Also, dont forget to format your volumes appropriately for SQL - 64K offset and 64K cluster size.
This is an extract from our Policy document for SQL Volumes, on how to use DISKPART to format a volume appropriately:
NOTE: When formatting a drive on a cluster, the drive must not be a member of a cluster group. If it is, you must first delete it from the cluster group, format it, and then add it back into the cluster group.
NOTE: If the drive you are formatting contains multiple partitions, you will need to make sure you know what partitions are there so you can recreate the partitions. The diskpart CLEAN command removes all partitions on the drive.
1.Using the diskpart command utility:
a.LIST VOLUME – this returns a list of drive letters and data about each
b.SELECT VOLUME – This sets focus on that drive letter
c.DETAIL VOLUME – returns a disk number associated to the current volume (drive letter)
d.SELECT DISK – This sets focus on that drive number
e.CLEAN – This command is destructive. It removes all partitions on the selected drive.
f.CREATE PARTITION PRIMARY ALIGN = 64 – creates a partition aligned on the 65th sector
g.ASSIGN LETTER= – This reassigns the drive letter after changing the offset. This should probably be set to the same as in step 1.b.
h.EXIT – exits the utility
2.At DOS prompt (you can also do this in the disk mgr GUI if you like)
a.Format : /a:64K /fs:NTFS – This command is destructive. This formats the drive with 64K blocks. When the format is done, it will prompt for a label. The label is optional, but should be used to indicate what information will be stored on the drive (e.g., Data, Log, Backup, etc.).
3.Verification
a.At DOS prompt
i.FSUTIL FSINFO NTFSINFO – This returns a bunch of information, but you want to look for “Bytes Per Cluster”, which should be 65536 (64K). This is the block size.
b.Using diskpart
i.SELECT VOLUME
ii.LIST PARTITION – this returns information about the partition(s) on the volume and shows the offset which should be 64K, meaning the partition is aligned at the 65th sector
July 22, 2008 at 11:24 am
psmith (7/22/2008)
What is the technical reason for leaving the Windows disk as Basic, if you have a raid disk system running your other data files?
dynamic disks are designed to support software RAID and help prevent data loss if no hardware solution is available.
from Microsoft Technet
Dynamic disks were first introduced with Windows 2000 and provide features that basic disks do not, such as the ability to create volumes that span multiple disks (spanned and striped volumes), and the ability to create fault tolerant volumes (mirrored and RAID-5 volumes).
This is what your hardware RAID is doing.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2008 at 12:03 am
Simon Facer (7/22/2008)
You may want to consider RAID 10, instead of RAID 5 for your SQL volumes - RAID 5 is not a recommended configuration. Also, dont forget to format your volumes appropriately for SQL - 64K offset and 64K cluster size.This is an extract from our Policy document for SQL Volumes, on how to use DISKPART to format a volume appropriately:
NOTE: When formatting a drive on a cluster, the drive must not be a member of a cluster group. If it is, you must first delete it from the cluster group, format it, and then add it back into the cluster group.
NOTE: If the drive you are formatting contains multiple partitions, you will need to make sure you know what partitions are there so you can recreate the partitions. The diskpart CLEAN command removes all partitions on the drive.
1.Using the diskpart command utility:
a.LIST VOLUME – this returns a list of drive letters and data about each
b.SELECT VOLUME – This sets focus on that drive letter
c.DETAIL VOLUME – returns a disk number associated to the current volume (drive letter)
d.SELECT DISK – This sets focus on that drive number
e.CLEAN – This command is destructive. It removes all partitions on the selected drive.
f.CREATE PARTITION PRIMARY ALIGN = 64 – creates a partition aligned on the 65th sector
g.ASSIGN LETTER= – This reassigns the drive letter after changing the offset. This should probably be set to the same as in step 1.b.
h.EXIT – exits the utility
2.At DOS prompt (you can also do this in the disk mgr GUI if you like)
a.Format : /a:64K /fs:NTFS – This command is destructive. This formats the drive with 64K blocks. When the format is done, it will prompt for a label. The label is optional, but should be used to indicate what information will be stored on the drive (e.g., Data, Log, Backup, etc.).
3.Verification
a.At DOS prompt
i.FSUTIL FSINFO NTFSINFO – This returns a bunch of information, but you want to look for “Bytes Per Cluster”, which should be 65536 (64K). This is the block size.
b.Using diskpart
i.SELECT VOLUME
ii.LIST PARTITION – this returns information about the partition(s) on the volume and shows the offset which should be 64K, meaning the partition is aligned at the 65th sector
when i run this command FSUTIL FSINFO NTFSINFO L: i get this results 65536.
but when i run this command SELECT VOLUME,LIST PARTITION i get this results Offset = 32KB.why?
July 23, 2008 at 1:50 am
did you format using the 64k switch?
was it successful?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2008 at 2:09 am
i didn't format the partition through the command prompt instead i format it through the computer management but in the format i changed the Allocation Unit Size to 64K
and the format was successfully.
July 23, 2008 at 6:52 am
avipenina (7/23/2008)
when i run this command FSUTIL FSINFO NTFSINFO L: i get this results 65536.
but when i run this command SELECT VOLUME,LIST PARTITION i get this results Offset = 32KB.why?
The FSUTIL command gives you the Block Size (aka Cluster Size) - 65536 is 64K, the LIST PARTITION command gives you the offset. Both should be set to 64K.
July 24, 2008 at 10:37 am
What kind of performance results can I expect if both the offset and cluster byte size is set to 4k instead of 64k?
July 24, 2008 at 12:25 pm
That depends on several things - the size of the database vs the amount of RAM, I/O profile of the database, number of updates in the database, etc. I have to admit to having been lazy and relied mostly on other people's testing results. The one round of testing I did (some time ago, at another position), I saw a 25% gain (approx) in I/O performance going to 64K / 64K configuration, this was a 400GB database with high Read / low Write (relatively speaking) profile.
Realistically, you will need to test and set your own benchmarks for your system.
This document gives a pretty good explanation of the technical reasons why you get the performance gains http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122,00.html
July 24, 2008 at 12:30 pm
Thanks Simon.
July 24, 2008 at 12:52 pm
NP. Sorry I can't give you a defintive answer on performance gains.
I don't tend to revisit older systems to reformat disks - business units dont have much appetite for the downtime and percieved risks involved, but I always make sure any new servers are configured properly. Of course, a badly performing system that's I/O bound is always a good candidate for benchmarking and conversion.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply