SQL Server IO Block Size

There is a lot of documentation around the proper way to format the drives used for your SQL Server instance. I have written about the proper NTFS Cluster Size, and I’m not alone in my interest about this subject. In this article by Argenis Fernandez we hear that this might not matter.

One of my favorite myths out there is the one that says that the optimal IO block size for SQL Server is 64 kilobytes. If you believed that until now, sorry — it’s just not true. The myth stems from the fact that the best practice (from Microsoft) says that you should use an NTFS allocation unit size of 64 kilobytes for your database partitions/volumes – with some exceptions, like FILESTREAM/FILETABLE stores. Some folks confused the two things, and thus the myth came to be. So let me clarify it one more time: IO block size and NTFS allocation unit size are two very different things.

So, what’s SQL Server’s IO block size, then? You might be wondering. The truth is that there isn’t just one – it varies, wildly, depending on the operation performed and the disk structure associated with it.

Before we move on to discussing specifics, here’s a link to our SQL Server best practices – we talk a little bit about NTFS allocation units there.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s