Sql Server Downgrade performance

Question

Dear All.,

I have the problem run SQL Server on Virtual Machine (VMWare). The all application access to the SQL Server is very slow. We got the information the cause is "Bytes per Physical Sector" is 512 when it should be 4k, is it right?
If it is right, what should I do, Should I reconfigure the raid?

Note: I already set The NTFS Cluster Size is 64k as SQL Server recommendation.

Regards,
Nana Sutisna

Answer 1

Hi NanaSutisna-7928,
SQL Server supports disk drives that have standard native sector sizes of 512 bytes and 4 KB.
If you are using Advanced Format Disks that are physically formatted with 4,096 bytes, but expose a logical sector size of 512 bytes, then you must update your SQL Server systems with the following fixes to be in a supported state:

• Windows Update to enable correct recognition and reporting of physical and logical sector size:
  2553708 A hotfix rollup that improves Windows Vista and Windows Server 2008 compatibility with Advanced Format disks
  982018 An update that improves the compatibility of Windows 7 and Windows Server 2008 R2 with Advanced Format Disks is available
• SQL Updates to use the updated logical and physical sector sizes in the SQL Server transaction log manager:
  SQL Server 2008 R2 Service Pack 1 and later versions of SQL Server 2008 R2
  SQL Server 2012 RTM and later versions of SQL Server 2012

If you use Advanced Format Disks without the updates that are mentioned earlier in this section, there is a risk of potential data loss and performance issues. Disk drives that have native 4KB sector size (both physical and logical) will work fine without the Windows and SQL updates.
In addition, please check if there are any network or CPU issue.
Please refer to Hard disk drive sector-size support boundaries in SQL Server and Microsoft support policy for 4K sector hard drives in Windows which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

How to find logical sector? Do you mean "Bytes Per Sector"?

If I run fsutil, I got below:

Bytes Per Sector : 512
Bytes Per Physical Sector : 512
Bytes Per Cluster : 65536
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0

By the disk configuration above, is it OK for SQL Server?

Regards,
Nana Sutisna

Answer 3

Hi NanaSutisna-7928,
Thanks for your reply.

How to find logical sector? Do you mean "Bytes Per Sector"?

Yes.

is it OK for SQL Server?

I think it is okay to SQL Server.
Please refer to the following articles to check if there are any CPU, I/O or network issue:
Troubleshooting SQL Server CPU Performance Issues
Slow I/O - SQL Server and disk I/O performance
Why is My Database Application so Slow

Best Regards,
Amelia

Answer 4

oh ya, this server is cloned from VM on Hyper-V and migrated it to VMWare. The VM on Hyper-V is normally, but the VM on VMWare is downgrade performance.
the difference between the two servers is only "Bytes per Physical Sector".
The VM on VMWare has "Bytes per Physical Sector": 512 while the VM on Hyper-V has "Bytes per Physical Sector" : 4K

Reagrds,
Nana Sutisna