Troubleshooting SQL server performance on VMWare

Question

Hi,

I am experiencing slowness issues after migrating a SQL database to a SQL server installed on a VM running on VMWare hardware. It was working fine on a legacy server.

I have observed many CXPACKET and CXCONSUMER waits. There is enough CPU, and MAXDOP and cost threshold for parallelism also seem to be good, but the issue persists.

Are there any specific OS level settings that need to be configured? Are there any common problems related to VMWare that could be causing these issues?

Thank you, Ashru

Answer 1

VMWare is not a Microsoft product.

See https://core.vmware.com/resource/architecting-microsoft-sql-server-vmware-vsphere

Answer 2

Hi @Asharaf Ali ,

Thank you for reaching out and welcome to Microsoft Q&A.

As far as I know, VMs are always slower than physical servers.

Here are several SQL Server best practices you can implement from this article to ensure your virtual SQL Server instances perform as well as possible.

1. Keep your versions updated is more important than you might think.
2. Plan and manage capacity, including CPU and memory capacity. Make sure the number of virtual CPUs you’ve assigned doesn’t go above the number of physical CPU sockets available.
3. Determine how much memory the VMs are actively using and adjust memory allocation to match.
4. Make sure the Power Plan set to High Performance on the host and in the guest.
5. You have to license a minimum of 4 cores.

For the CXPACKET and CXCONSUMER waits, I found this article, we can get the suggestions below:

1. You've set the CTFP and MAXDOP per industry best practices. You can let those settings bake for a few days. This clears the plan cache and forces SQL Server to rebuild query execution plans (reevaluate cost).
2. Make index improvements that will reduce the times when queries go parallel to do scans and sorts. Let new indexes bake and then look for queries that are still doing a lot of work.
3. Tune those queries and let them bake for a few days.
4. Identify which queries and stored procs have the most IO, CPU usage and are taking the longest to run and see if there’s room for improvement.
5. We recommend monitoring your performance counters to isolate the deeper issues.

Finally, as Olaf says, VMWare is not a Microsoft product, I'm not the expert in this field. I can offer you supports about SQL Server. Hope these can help you well.

If you have any confused, please feel free to share your issue here. Wish you have a good day!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 3

If you move from a physical server to a virtualised server, you can expect some performance degradation, but it should not be drastic, if the specs of the VM are in par with the physical server.

So that is the first thing to find out, how much RAM does the VM have? How much RAM did your physical machine have? How many cores do you have now, and how many did you use to have? And about disks, are they the same as before?

But it could also be a matter of normal query and index tuning. Because you move to new hardware, the optimizer makes different choices and some queries run slower. In this case, enable Query Store if you have already and check for slow queries. In fact the CX waits rather point in this direction.