July 13, 2005 at 9:48 am
Hi there,
The default allocation units for W2K server is 4096 bytes. SQL Pages are 8060 bytes.
What are the performance consequences of leaving the disk clusters at the default, and should every DBA change it to 8K ??
Thanks
July 13, 2005 at 10:30 am
It's claimed a larger block size will give improved performance, I've never been able to prove it ( takes too long to set up and is difficult to benchmark ) with most modern raid controllers I doubt you'd see much difference - the number of spindles would be a greater factor.
I've always tried to get tran log drives formatted with 64kb blocks but again I have no benchmarks to prove increased performance.
Read ahead works with 64kb chunks so technically a 64kb block may be best for data.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 13, 2005 at 11:10 am
Colin, I see the logic. So, in other words if the system is on the SAN it shouldn't matter what the size is.
I don't even know how to change the size (on the SAN or local drives). Wouln't it require reformatting the drive?
As far as Windows 2003, do you know what the default cluster size is?
Thanks very much!
July 14, 2005 at 4:03 am
Yes you have to reformat to change block and stripe size - that's why I never had time to really test it out.
I have different views on SANs which I have found can give all types of problems for a performance oltp server. But that's another thread all together.
Block sizes are same for 2003 - we upgraded our servers and didn't touch the SAN disks at all.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 14, 2005 at 8:56 am
You should set the cluster size to the extent size (8 pages = 64K) because AFAIK SQLServer is reading at least an extent from the disk. But the cluster size should definitly not be smaller the 8 K as SQLServer is only working with pages.
Bye
Gabor
July 22, 2005 at 5:50 am
The rule of thumb --> SQL reads extents (64k) and writes pages (8k)
For a SAN, tweaking stripe/block size only begins to show performance affects on larger databases ... by larger I mean 500+ Gb ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 2, 2005 at 9:47 am
Guys, one more thing:
How important is it to format the drives that house system databases (tempdb, master, msdb). Should that matter?
Tnks again
November 2, 2005 at 4:50 pm
They are databases as well with data and transaction logs ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply