Block size for SQL Server Data files

I need your suggestion for Block size for SQL Server Data files.
64k is kind of large for my small databases but I never really like 4k either so one some of my servers I've been using 8k blocks for NTFS.

Can you people tell me about best practice for this.
Abdul WahabAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Formatting a system drive is a bit different....

Setting your block size will be different for different systems. Could well be the case that setting it to 8k or 128k will yield improvements for any given system.

The whole block size of 64k does come from Microsoft because of the NTFS setting
Volume Formatting      We recommend you follow Microsoft's recommendation of formatting volumes with an NTFS allocation unit size of 64 Kilobytes, unless you need to leverage NTFS' compression capabilities, which are only available on 4 Kilobyte allocation unit sized-volumes. This doesn't mean that SQL Server will use an IO block size of the allocation unit size you pick -- SQL will issue IOs as small as a single sector (512 bytes) or as large as 8 Megabytes for ColumnStore indexes. We also recommend that you use the /L parameter of the FORMAT command for your database volumes (or the -UseLargeFRS parameter for the Format-Volume Powershell cmdlet), which will help avoid certain issues like CHECKDB failing with error 665. This creates NTFS volumes with large File Record Segments. Please note that the /L parameter is only available on Windows Server 2012 and above.
Source : https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/001_Microsoft_SQL_Server_Quick_Reference

But by all means set it to 64k - it wont really hurt anything. All I am saying is dont feel compelled to make it 64k if you have knowledge of your DB activities that might suggest a different block size.

A good rule of thumb is to align with your disk block size. If SQL is 64kb but disk is 4kb then that does mean potentially 16 disk IO per 1 SQL Block

And SQL Server will only grab an extent when it is needed, it is not as if the unit of IO is always an extent.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
64kb should be the minimum to use for SQL Server databases and that's because the minimum information that SQL Server engine loads from the disk is an extent page. A data page has 8kb and an extent are 8 data pages and that makes 64kb (8 * 8kb).
If for example you format the disk with block sizes of 8kb, it will make the engine to access 8 times the disk to collect an extent page. With a 64kb block size it will access only once.
Hope that I could explain it in a way that you are able to understand.
1
 
Mark WillsTopic AdvisorCommented:
There really (and truly) no such thing as a "Best Practice" for block size as far as SQL Server is concerned.

Have a read of :  https://blog.purestorage.com/what-is-sql-servers-io-block-size/

8k is around a page size, so why not :)
0
 
Mark WillsTopic AdvisorCommented:
Didnt see Vitor's post at the time of my post.

I still stand by what I said above about block size as far as SQL is concerned.

It is a bit of a myth that it must be 64k - that was a NTFS allocation unit that some how became a defacto requirement for SQL, but it isnt real.

Yes, Vitor is correct about 8K page sizes and allocation units, but SQL will grab a page and extent on demand / as needed. So what you need to consider is more along the lines of How much disk will my DB Need, And LOG files, And..... then reserve that space on disk and set about consuming (and managing) that space.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It is a bit of a myth that it must be 64k
Not really a myth for me as I saw the huge improvement in my database systems after performing the change. And this was suggested by our Microsoft support at that time (around 10 years ago) and I still use it as an important configuration in our systems.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.