Sql-server – SAN block size for SQL Server on 4KB NTFS

hardwaresansql serversql-server-2008

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.

Best Answer

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.

Related Question