VMWare server guest sqlservr.exe has high cpu at reads and writes.

It's possible this can happen, We've seen it many times.

What resources have been assigned to the Virtual Machnine, CPU, Memory?

What iSCSI connection, software, hardware, jumbo frames, what disks do you have in the SAN, RAID type etc

check for memory limit... Edit Settings, Resource tab, click on memory, unlimited checkbox should be checked.

If it's inside of a resource pool, move it out so that it is outside of all pools.

It is out of a resource pool and has unlimited box checked.  

VM Configuration
4GB Memory
1 vCPU
1 NIC

Are you using the VMXNET3 NIC?

are there other queries/databases on this server?  could it be that the query or the database changed 2 months ago?

NIC = E1000
MSA2012i
RAID Level RAID 50
Virtual Disk Size 8101.46 GBytes
Virtual Disk Status Fault Tolerant
Number of Drives 24
Number of Volumes 11

It is a Finance application running on the server and during tax season the has a update of the tax percentages.  I called the software company and they haven't had any other issues.

I increased the guest OS to 8GB of ram and 2vcpu quad core. It has more resources and it uses the additional resources. Is there any tools I can run on the ms SQL server?

SQL will always use all the memory in the server, and release back to the OS as other aplications request memory.

There is an option in SQL server to limit the amt of memory SQL can use.  If you can't find it, let us know...

When the issue occurs does the sqlsrvr process on the server flatline at 100% until the query finishes, and then cpu returns to normal, and during this time the server is sluggish, and also a vmware cpu alert is triggered on the virtual machine?

do these users usually run this report?

you ve not got two users running the same report?

is the server 32 or 64 bit?

The sqlservr does shoot to 100% cpu until query finishes.  It is a bunch of different processes the users are running in the application that pulls different queries that make it go to 100%.  It doesn't make any vmware cpu alerts in the virtual machine.  There is about 15 users consistently on the client application that queries the sql server.  The guest OS is 32bit.

What is the issue?

what I mean is does it affect performance to the end user?

what is the size of the database?

This issue it that the same processes that use to take a couple of seconds or minutes now takes 10s of minutes and even an hour on a certain process.  It has gotten to the point when the system is not productive.

The database is 2.7GB

So cpu in task mgr goes up but you don't see same amt of increase from vsphere client for cpu usage?

@danm66: Correct

I've just checked, and we still have the footage of videos that we recorded, of a stable SQL installation, with no issues, until the query was executed, this made the query time approx 13 minutes (virtual) to complete, but because the server flatlined at 100%, also stopped the Web Services that was providing a real-time portal function for the application as well. All was well when the query finished.

When it was migarted to a physical server, the query time was just under 2 minutes, and the database size fo the application was 500MB.

If you are interested to see the CPU Performance, and Query, let me know, I'll upload a link for the URL.

I would suggest a discussion with the vendor about, do they have any exprience of it running in a VMware vSphere environment.

@hanccocka Thanks, and yes I would like to see it.  I also am looking into the Physical.  Did you do a V2P conversion or just start from scratch and restore the database to the new physical?

What we did was built a new server, kept the OS and Database the same, copied the database from the virtual to the physical (over night), changed all the clients to connect to the new server (if you client software uses an ODBC connection, then this is easy, just create a new ODBC connection for the software).

(we had tested this before thouroughly, and it was significantly faster)

and let the users continue as normal, they noticed immediately it was faster!

So the cat was out of the bag!

I'll upload the video we just need to mask some of the confidential information on screen. But you can clearly see CPU is idle, and when query starts it jumps up and users 100%, which was 3GHz processor. (2 vCPU), with 8GB RAM for 11-13 minutes.

Have powered dowm the vm recently?  Not a restart, but a power-off/power-on.

If there has been any messing with limits or resource pools, you sometimes need to restar the vm world to reset everyrhing.

If you aren't seeing the cpu increase in the client performace measurements then it's usually related to some kind of resource starvation.

Hanccocka, did you see the same cpu measure inconsistency in your cases?

@danm66: we've seen this issue many times, with poor vendor applications, and badly written databases, and queries. It's parallel queries that causes the issue. We often find as well it's from very small software houses we have this issues with as well. (and it doesn't help, that alot of them, don't have a clue, what we are talking about, other than wanting us to re-write their databases and queries for free), and as for knowing anything about VMware (don't get me started on that!).

Okay, sorry it's taken me a few days, I've had to obscure the application from view, because it contains sensitive data. But you'll get the general idea, CPU relative stable, and it's being used concurrently by at least 100 clients.

and then one client in view (blurred!), executes the query, the duration of which is about 11 minutes, causing High CPU >  3GHz (100%), causing all other clients to suffer and fail with timeout issues.

Poor Microsoft SQL Performance caused by Virtualisation - Not all servers are equal!

Thanks to the both of you. I have ordered a new physical server and will configure it the same way. Make the switch over night and see if there is a difference. If it isn't I just have another server.

Get your users to test before you switch.

It was the vendors stored procedures.  They made changes and it now works fine.