question

NanaSutisna-7928 avatar image
0 Votes"
NanaSutisna-7928 asked NanaSutisna-7928 answered

Sql Server Downgrade performance

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NanaSutisna-7928 avatar image
0 Votes"
NanaSutisna-7928 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered NanaSutisna-7928 commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I found CurrentDiskQueueLength is greater than 4 on Windows Performance Monitor.
So I predict the problem caused by wrong setting of Bytes per Physical Sector, but you said me the value 512 is OK for SQL.
I checked CPU and Network is OK.
Strangely I checked on VMware desktop, there are no disk issue either on the host server or on the VM.

Regards,
Nana Sutisna

0 Votes 0 ·
NanaSutisna-7928 avatar image
0 Votes"
NanaSutisna-7928 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.