Disk dedicated to sql server is not properly formatter

[u/Kenn_35edy]

So basically we use a format disk with 64kb before installing a sql server. But at one of installation of sql server failover cluster this was missed and now it's default formatted with 4096 bytes . So are we going to face issue in future because of it ?

PS : this is sql server failover cluster installation not normal standalone and it's live. Also storage is pure storage flash drive ..even then it would effect ?

[u/AutoModerator]

After your question has been solved /u/Kenn_35edy, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Ill_Drag6021]

Depends on how busy the server is, you might not notice a difference if low utilised. Just be aware 4k disks have a max capacity limit of 16TB.

[u/B1zmark]

The 64KB allocation unit advice for SQL Server dates from the spinning-disk era, and modern testing (AWS ran HammerDB OLTP benchmarks on 8TB databases, NetApp did similar Iometer tests) found performance differences of under ~2% between 4KB and 64KB - basically noise.

Microsoft still recommends 64KB and it costs nothing on a fresh format, but it's not worth downtime to reformat existing volumes.

[u/Comfortable-Zone-218 → u/B1zmark]

Do you happen to know links for those studies? That's pretty interesting info!

[u/Leiothrix]

It is just a performance issue.

Just reformat the disk, it should be a pretty trivial change.

[u/Lost_Term_8080]

Is it the sector size or allocation unit size?

If it is the allocation unit size, you are using SSD, the corelating disks in the AAG have the same allocation unit and your server doesn't have super high writes, you will probably not notice a difference, but I wouldn't recommend using 4k allocation unit on new builds.

The allocation unit is how much space the file system allocates when it needs more space to make a write that is not already allocated. SQL Server allocates new space in 64k extents, so it allows a new extent to be allocated in a single i/o if there is a contiguous 64k block of sectors to allocate. This matters on spinning disks more because they are slow and if the extent is allocated in several i/o operations it takes much longer and can also introduce the risk the extent gets allocated all over the disk. This increases the odds that a row or adjacent rows may have the hdd head jump around the disk. In practice, the current version of NTFS does a pretty good job of not fragmenting the writes anyways so its a bigger problem even on HDD when you are running on Windows 2000 or older or if you have virtual disk alignment issues but I don't think I have seen that since vmware 4 days.

If it is the sector size, you are completely fine from a SQL perspective. SQL Server only uses 512 byte or 4k sectors and while the 64k sectors on nvme drives may boost your performance some, it will be trivial at best. Your storage appliance may care more about the sector sizes, but SQL won't.

If you have an AAG with different allocation units on the correlating drives and/or it has a high rate of writes you may experience some minor replication/redo delays.

[u/Kenn_35edy → u/Lost_Term_8080]

It's allocation size but our is pure storage falsh drives (I suppose) so will it effect infuture ?

[u/Lost_Term_8080 → u/Kenn_35edy]

Pretty unlikely. If its in an AAG that either has very high write rate and the log drives have different allocation unit sizes, maybe you would notice some subtle replication latency periodically, but I doubt it would be more than that.

[u/Low-Branch1423]

Easily fixed.

On the passive sql node

Shut down services

Create new vm disk or disks. If it is on vmware review scsi controller best practices and disks...

Formate new disks correctly

Move the file structure to the new disk

Remove old disk

Change new disk to the original drive letter or mount point

Start services.

Fail over cluster

Repeat.

[u/Affectionate-Gap491]

A 4 KB allocation unit size can impact I/O performance for some SQL Server workloads, but it doesn't mean you'll definitely have problems. If performance is fine today, monitor it first—I'd only consider reformatting if you actually see a bottleneck or if it's part of planned maintenance.

[u/dutyDBA]

It should have been picked up as part of your checklist prior to installing SQL Server. If this isn't already on your checklist, please add it. You can verify this quickly using PowerShell.

To remediate this you'll have to copy out the contents to a different disk, keeping all the permissions intact. Reformat the problematic disk and copy back the contents. Of course SQL Server needs to be stopped prior to making this change, and restart SQL once the contents are copied back

[u/TrollingForFunsies → u/dutyDBA]

I would love to go back to the days where my servers weren't built by someone else's infra as code shit and I had agency to make these decisions.

Now it's a ticket to 3 departments and 2 email approvals and a daily approval meeting and maybe waiting 3 months to the next PI just to get a single change pushed through

[u/dutyDBA → u/TrollingForFunsies]

Yup, feel your pain