March 10, 2008 at 2:11 pm
We are running SAP on sqlserver 2k5 and having high I/Os.
Does anyone know what the optimum block size would be at the file system level?
We currently are running 4k now and know we need to change it but to what?
Thanks
Steve
March 10, 2008 at 2:40 pm
I have read 64K since SQL Server stores data in extents that consist of 8 8K pages.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 11, 2008 at 11:34 am
it's unlikely changing the block size will actually have much visible effect - I've never been able to measure any; however, I agree that 64kb for t logs is vital - there's some question on 8kb or 64kb on the data drives - I figure backup drives would be 64kb. Enterprise sql can read up to 1024kb in read ahead.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 11, 2008 at 11:50 am
Steve Cockerill (3/10/2008)
We are running SAP on sqlserver 2k5 and having high I/Os.Does anyone know what the optimum block size would be at the file system level?
We currently are running 4k now and know we need to change it but to what?
Thanks
Steve
As SQL Server works mostly with extents (e.g. 8x8K), using a 64K block size will allow more pages per I/O transfer and this is the ideal size for high volume databases, but it will be a trade-off between file fragmentation vs wasted space, as small cluster sizes contribute to fragmentation and larger cluster sized contribute to wasted space if not fully utilized. In addition, you want to prevent split I/O's, which 4K clusters will contribute, so 64K also helps in this regard. This may be part of the reason you are seeing high I/O levels.
How does the RAID levels look on your implementation?
Thanks,
Phillip Cox
MCITP - DBAdmin
March 11, 2008 at 12:20 pm
Thanks everyone
Our implementation of SAP is hosted and they announced they wanted to change the cluster size to 8 and I was interested in what others thought. I was leaning towards 64 from research and previous experience. If 64 turned out to be the commonly used (recommended) size I was going to suggest they take us beyond 8k.
March 12, 2008 at 7:49 am
4K is definitely suboptimal. Also very important (and another thing most miss just like using default cluster sizes on format) is to sector align the partitions.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply