June 7, 2017 at 11:41 am
I have never known how our server team formats the drives in 4k blocks or 64k blocks. They gave me a new server and I installed it all and it is running fine. Then a server engineer came to me later and said I formatted the drives in 4k blocks.
Can anyone tell me if this is a major problem, minor issue or you probably won't notice.
Then the question becomes, what is the recommended one? and is there a way I can tell?
June 7, 2017 at 2:19 pm
recommended is 64k due to that being the size of an extent.
An extent is 1 chunck of data that SQL requests. Having your block size and extent size the same means that when SQL tries to read a 64K chunk, it can read 1 block which will be continuous and thus quick to read. If you have a 4K block size, it needs to get 16 of them from disk which may or may not be in physical order on the disk.
Lets say that it is a worst case scenario setup. SQL asks for 1 extent, the first block it gets is the first block on the disk. The second block is the last block on the disk. Third is the second block on the disk and so on until you have all 16 blocks. Presuming this is a spinning platter disk, it would need to jump back and forth across the disk repeatedly to get your 16 blocks. Assuming this is an SSD, it would need to do 16 separate read requests from the disk.
You put that as a 64K block size on the disk, when SQL asks for it, you only need to get 1 block and there is only 1 disk seek. It will perform a lot faster when you have a heavy read operation.
As for finding the block size it was formatted as, I do not know of a "good" way, but you can run chkdsk against the partition and when it is done, it will tell you the number of "bytes in each allocation unit" which is your block size.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 7, 2017 at 4:10 pm
To get the current allocation unit size, from the command line:
fsutil fsinfo ntfsinfo YourDrive:
Look at bytes per Cluster for allocation unit size.
Otherwise start diskpart, type list volume, type select volume <your volume number> and then type filesystems
Sue
June 8, 2017 at 3:26 am
bmg002 - Wednesday, June 7, 2017 2:19 PMrecommended is 64k due to that being the size of an extent.An extent is 1 chunck of data that SQL requests. Having your block size and extent size the same means that when SQL tries to read a 64K chunk, it can read 1 block which will be continuous and thus quick to read. If you have a 4K block size, it needs to get 16 of them from disk which may or may not be in physical order on the disk.
Lets say that it is a worst case scenario setup. SQL asks for 1 extent, the first block it gets is the first block on the disk. The second block is the last block on the disk. Third is the second block on the disk and so on until you have all 16 blocks. Presuming this is a spinning platter disk, it would need to jump back and forth across the disk repeatedly to get your 16 blocks. Assuming this is an SSD, it would need to do 16 separate read requests from the disk.You put that as a 64K block size on the disk, when SQL asks for it, you only need to get 1 block and there is only 1 disk seek. It will perform a lot faster when you have a heavy read operation.
As for finding the block size it was formatted as, I do not know of a "good" way, but you can run chkdsk against the partition and when it is done, it will tell you the number of "bytes in each allocation unit" which is your block size.
it's also irrelevant for the t-log drive as this does not use extents, the sector size is used for the log file layout.
Better off just leaving this as default 4K NTFS cluster size
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 9, 2019 at 2:24 pm
To get the current disk Format you can go with PowerShell:
get-wmiobject -class win32_volume|select Name, blocksize
regards,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply