May 24, 2017 at 2:45 pm
I am prepping a Windows Server 2016 box to install SQL Server 2014 Standard. I have two RAID10 arrays (Drives D: and E:) to house the datafiles and logfiles respectively.
I found an interesting article on the net pertaining on how to improve SQL performance:
http://www.sqlsolutionsgroup.com/improve-io-performance/
Not unless I misunderstood, the article suggest not needing to reformat the disks with 64kb sectors compared to the default 4kb sectors since server operating systems newer than 2008 automatically take care of it. So, I just want to know if I should leave it as is or make changes before installing SQL Serve 2014 on this server.
I ran the following commands on this server:
wmic partition get blocksize, startingoffset, name
Blocksize Name Startingoffset
512 Disk #0, Partition #0 1048576
512 Disk #0, Partition #1 472907776
512 Disk #0, Partition #2 593494016
512 Disk #3, Partition #0 135266304
512 Disk #2, Partition #0 135266304
512 Disk #1, Partition #0 135266304
fsutil fsinfo NTFSinfo D:
Bytes per sector: 512
Bytes per physical sector: 512
Bytes per cluster: 4096
Bytes per filerecord sector: 1024
Both the D: and E: fsutil display the same above values.
My understanding is the bytes per cluster / bytes per filerecord sector = sector size [4096/1024=4 or 4kb]
If the above is correct, then should I leave it alone or make changes. If you recommend changes, can you provide details, etc?
May 24, 2017 at 6:35 pm
cmp119 - Wednesday, May 24, 2017 2:45 PMI am prepping a Windows Server 2016 box to install SQL Server 2014 Standard. I have two RAID10 arrays (Drives D: and E:) to house the datafiles and logfiles respectively.I found an interesting article on the net pertaining on how to improve SQL performance:
http://www.sqlsolutionsgroup.com/improve-io-performance/
Not unless I misunderstood, the article suggest not needing to reformat the disks with 64kb sectors compared to the default 4kb sectors since server operating systems newer than 2008 automatically take care of it. So, I just want to know if I should leave it as is or make changes before installing SQL Serve 2014 on this server.
I ran the following commands on this server:
wmic partition get blocksize, startingoffset, name
Blocksize Name Startingoffset
512 Disk #0, Partition #0 1048576
512 Disk #0, Partition #1 472907776
512 Disk #0, Partition #2 593494016
512 Disk #3, Partition #0 135266304
512 Disk #2, Partition #0 135266304
512 Disk #1, Partition #0 135266304fsutil fsinfo NTFSinfo D:
Bytes per sector: 512
Bytes per physical sector: 512
Bytes per cluster: 4096
Bytes per filerecord sector: 1024Both the D: and E: fsutil display the same above values.
My understanding is the bytes per cluster / bytes per filerecord sector = sector size [4096/1024=4 or 4kb]
If the above is correct, then should I leave it alone or make changes. If you recommend changes, can you provide details, etc?
1) You are mixing things up. 64K is what you should use when FORMATTING a drive to store SQL Server data/log files.
2) Sector alignment is where the physical bits are out-of-whack with the actual IO that is done. You can give up 50% capacity on purely random 64K reads if you mess that one up. FORTUNATELY Windows Server since 2008 has sector aligned on 1024K which gets everything kosher. HOWEVER - there is a caveat there: the volume has to be > 4GB for this to happen. I still come across volumes smaller than that, especially in big/shared environments.
3) Oh, BTW: telling us you have two RAID10 disks is meaningless. The VAST majority of times I see such at clients it is ONE SET OF SPINDLES. This very likely means you are actually creating IO bottlenecks by carving up few spindles into more chunks.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 24, 2017 at 7:30 pm
1. Do you have the commands/steps necessary to format both disks (D: for datafiles and E: for logfiles)? I want to make sure its done right before proceeding. This would greatly be appreciated.
2. One volume has 1.1TB for datafiles, and the logfiles volume has 585Gbs. So I take it you're saying the alignment for volumes on a Windows 2016 Server are fine then.
3. I figure having two separate RAID10s for the datafiles and logfiles, and then a RAID5 for the tempdb ought to segregate everything for optimal performance. The RAID5 array are SSD drives.
May 25, 2017 at 9:47 am
since the t-log files are actually sector aligned the NTFS cluster size can stay at the default of 4k, makes no difference
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply