question

Jeff Oresik avatar image
Jeff Oresik asked

Drive allocation unit optimum size?

We've had a recommendation to increase the size from 8192, 64K or larger. Any way to determine what is the best or is bigger better? SQL Server 2005 Standard Windows 2003 Server 64 SAN Drives Virtual Environment Data are more read intensive. Much of the data entry comes from another database via nightly import. Would the disk for log files be any different than data files?
sql-server-2005disk-io
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Håkan Winther avatar image
Håkan Winther answered
Recommendations for SQL server is 64k. Each page is 8k and an extent is 8 pages ( equals 64k ). SQL server tries to optimize the performance by reading extents.
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
To add to this, please watch out for SAN caching, it rarely helps on database loads. It can especially be a problem if you have write caching enabled for the LUN(s) that store your SQL Log files, you can get blazing speeds that then drop off when the cache flushes to disk. If you don't know about it, it can be hard to find.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.