July 16, 2003 at 4:05 pm
Going right back to basics and considering 64KB cluster size in formatting SCSI disks on Win 2000 server (Win 2003 not viable just yet), in an effort to squeeze as much performance gains as possible by reducing disk I/O operations.
However, physical defrag is not possible, due to MS API limitation of 4KB cluster size.
Question: With a frequently updated dB which will eventually see an increase in size of the dB, will the benefit of (hopefully), reduced disk I/O's be eroded away with (eventually) physical file fragmentation on the disk.
Looking to anyone with experience on this matter.
Any comments, pointers to further discussion areas appreciated.
July 16, 2003 at 7:08 pm
Hi there
This is a really tricky one and hard to answer. I suppose the issues are compounded with these variables:
a) how the growth settings are configured for each DB
- defines the "physical" grab for disk space in multiples of the OS /A:size defined
by the format command.
- the file growth in Mb may ultimately determine the fragmentation of the physical file on disk as a request is made for space
b) fill-facotors, pad-index values - row packing per page
c) effective use of the buffer cache to ultimately reduce IO in the first place
d) raid config
I always set of the allocation unit size to 64kb. BUT, this may need to change when striping over multiple disks for example. ANother issue to remember, db servers are, by their nature, multi-user. The disks are spinning all over the place and users are requesting data throughput the DBMS. This is why I mention c) as one of the key determinants to overall performance management. High reuse and logical hits are key to maintaining great performance. All that said, its an interesting topic at the power disk levels and hard to measure. I generally use sysinternal.com software and apps from intel to hammer disks to determine raw throughput.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 17, 2003 at 2:02 am
Yes, it's better to have data residing in cache, but when we need to read/write.....
I guess ultimately it comes down to how often and in what size chunks the dB grows.
The more static the files in terms of growth, the more benefit you get from having sector (sorry not cluster) size = extent.
At the other end of the scale, if a file grows in small increments and frequently, then disk fragmentation may outweigh benefits.
As always, comes down to knowledge of your dB and correct design & initial config settings of files etc, in this instance.
So maybe one train of thought would be to have relatively static files on 64K sector disks, and others at risk of disk fragmentation on 4K sectors?
From your reply comes another related question:
Does striping have an influence on sector size?
TIA
July 28, 2003 at 5:49 am
Be sure the chunk size in your raid sets holds a whole cluster, especially for raid-5. Otherwise each write will end up needing to hit more than 2 disks and calculate parity in two rair 5 blocks.
What I've been doing, but have not tried to test for effectiveness, is makig both the raid set and NTFS cluster 64k.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply