7

A potential customer wants to evaluate our storage system. They run Windows 2008R2 x64 with 4KB NTFS on a virtual test machine they've sent us. They did not seem to know this at hand, so I think it's reasonable to assume that environment was not tweaked. The tests are inserting, indexing, searching, deleting. Tool is not known to me.

Given that the windows NTFS block size is 4KB, and SQL write in 64KB chunks - is it safe to assume that a block size of 64k on the SAN is a good choice?

They run SQL Server 2008, perhaps standard.

Improve this question

2 Answers 2

Reset to default
5

Quoting from Brent Ozar's article SQL Server Virtualization Q&A:

Question: “Regarding Virtualization and SAN… is there a recommendation for setting block size on SQL Server 2008?”

There’s a few related settings here including NTFS allocation unit size, RAID stripe size, and partition offset. Check with your SAN vendor’s documentation to see what’s right for you. In most cases for OLTP databases, you’re in decent shape with 64K NTFS allocation unit size and RAID stripe size, and a 1mb partition offset.

So yes, by default you'll be safe with a 64k block size, but see also your storage documentation, maybe they specify some other preferable unit for a database server.

Some more great information from the same blog: SAN Storage Best Practices for SQL Server.

Improve this answer
2
  • Thanks for the links, they support the 64KB best practice. In this scenario the NTFS block size is that of 4KB though, is that not something I have to take into consideration (as they will be clustered to 64KB blocks anyway, and thus one I/O on the SAN)?
    – 3molo
    Feb 15, 2013 at 8:02
  • 2
    Yes it does need to be taken into account. You want the file system as well as the LUN setup for 64k.
    – mrdenny
    Feb 15, 2013 at 16:12
5

Yes you'll want 64k blocks on the array and the disk as SQL Server will be doing the bulk of it's operations in 64k chunks. While there will be some reads which are smaller than this and some that are larger (such as read ahead) the bulk of the operations will be 64k in size.

Assuming that the server is a Windows 2008 server (or newer) and that the LUN is new to the server, the partition will be correctly aligned automatically when it's created (unless they screwed it up manually using diskpart).

Improve this answer
2
  • 1
    Are all reads done in 8KB blocks by default? Is that not the main consideration because the (synchronous) writes determine the latency, more or less?
    – 3molo
    Feb 15, 2013 at 8:03
  • 1
    Reads are done by the extent, not by the page so reads are the size of an extent which is 64k. You'll see the occasional read of 8k can happen but that's only when SQL has removed one page of an extent from the buffer and it needs to get the one page back. A safe generalization is that all disk IO is 64k.
    – mrdenny
    Feb 15, 2013 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.