December 4, 2007 at 3:25 pm
I have configured RAID array and accepted default block size. I have also been advised I should set an allocation size of 64KB on the drives in Windows?
along the lines of 'format /a'
I hope this is the correct forum (as I felt this was performance tuning!!).
Can anyone advise.
Many Thanks
SQL Novice 'Phil'
Note: Windows Server 2003 Standard R2 SP2 64bit / SLQ 2005 Standard 64bit 8GB RAM.......that reminds me, should I do anything with the paging file??
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 4, 2007 at 4:12 pm
64 kb size is what recommended....
move pagefile to his own drive to avoid fragmentation....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 5, 2007 at 1:06 am
Thanks for the reply.
Can you advise the syntax for how I would set the Block Size to 64KB or find ot the current size?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 5, 2007 at 10:44 am
raid aray configuration tools allows you to select when you are configuring it...i think its ther default value for most of the cases though
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 6, 2007 at 6:15 am
a few tips:
- Pagefile: keep it on same size no matter of disk allocation. That means that you need to set min and max size of the same value. This will keep page file in a good shape.
- Allocation size: first you have to align partitions using diskpar. Regarding size I would leave it to default - because it depends more on what type of storage subsystem you have (EMC, IBM, Hitachi...) and type of workload you have (small transactions, mostly reads, mostly writes, blob's.....) as well as RAID types on coresponding storage vendors.
rgds
Sinisa
December 6, 2007 at 9:09 am
1) what stripe size did you use for the RAID set?
2) you should use diskpart to force sector alignment of the NTFS partition.
Both of these carry significant performance penalties if not done appropriately.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 6, 2007 at 10:02 am
The RAID Stripe Size is 128KB for each logical partition (RAID 1+0), this was the default.
What do I need to do to optimize configuration?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 6, 2007 at 12:16 pm
Hi, can you please expand on:
diskpart
I have configured 3 ARRAYS:
A1: RAID 1+0
Logical C 50GB (OS / Page File)
Logical E: 90GB (Temp DB)
A2: RAID 1+0
Logical F: 139GB (Data .mdf)
A3: RAID 1+0
Logical G: 139GB (Logs .ldf)
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 6, 2007 at 12:55 pm
There is a huge amount of variables to the configuration of storage, including but not limited to:
RAID type
number of spindles per RAID set
HBA cache and cache ratio as well as queue length
data access patterns
what type of file on what type of RAID
RAID stripe size
NTFS stripe size
NTFS partition alignment
If you truly want optimal performance you should hire a consultant to give you recommendations and provide benchmarking. This shouldn't take that long for such a small setup.
diskpart is a tool from MS that allows you to specify information when you create partitions. Sector misalignment can lead to between 10 and as much as 50% I/O performance hit depending on cofiguration.
For the given setup I note the following:
page file, os and tempdb is on same raid
raids seem very small, leading me to think small amounts of spindles. how many different disks per raid set listed?
128kb stripe sizes will be suboptimal, especially for the tlog set. 64kb is generically better for most data access and 8kb could possibly be better for tlog specific stripes. 256kb could be better for large-scale warehouses
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 6, 2007 at 2:20 pm
Hi. 2 * 146GB SAS drives 10000rpm per array (6 discs in total + 1 hot spare).
I have hired a consultant to come to site to mirror. I am trying to get a better understanding of the required config.
Many Thanks,
Phil
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 6, 2007 at 2:46 pm
Well, with 2 drives per RAID set you don't have RAID 1+0 - simply RAID 1. You need 4 drives min for RAID 10.
Your setup is reasonable. I would prefer 64KB stripe size for all RAID sets and NTFS partitions. You really do need to ensure sector alignment for optimal performance. Search the web for sector alignment sql server performance and you will find a plethora of information. It is easy to do.
You can use SQLIO to test various configurations before you go live. I would play around with the HBA queue length too. With only 2 drives (actually 1 active since RAID1) something lower than 32/64 will probably be optimal. Tough to say.
cache should prolly be 75% write, 25% read as a best guess.
Remember that this is free advice so YMMV!! :-))
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 7, 2007 at 4:00 am
Thanks for the 'Free' advice 🙂
I will do the search + amend Stripe Size. Regards RAID1+0 this is simply a 2 disc Mirror, config utility records as RAID1+0.
How about the Stripe Size for the Logical Partition housing the OS?........that would be more free advice!! 🙂
Many Thanks,
Phil.
Update:
OK have read a little......I set the stripe size on the RAID to 64KB for ALL logical partitions.
From the command line I have run:
format X: /A:64k (x being drive letter)
I will do this for all drives excluding the C drive!!!
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply