We have an app that runs SQL Server 2008 R2 Express on the back end on a Windows 2008 R2 virtual server running on VMWare ESXi. Users complain that the app is very slow. I see very high disk IO that does not make sense to me.
- Average disk queue length is around 60, max is around 250
- Average disk reads/sec is around 2000, with max around 3500
- Resource Monitor shows that sqlservr.exe is reading the .mdf file at around 40-60 MB/sec (see below). Write IO is very low.
- The .mdf file is only 2 GB, which means the entire thing should fit into memory
- The server has 32 GB RAM and typically less than 16 GB in use
- The server has 8x 15k SAS disks in RAID10. It is a Dell with a PERC H710 controller, which is capable of taking an SSD to enable CacheCade (SSD-based read cache), which I am tempted to try, but it seems like the whole thing should be cached in RAM anyway so I am not sure this will help.
- The SQL VM sits on a Dell server running VMWare ESXi 5. There is one other VM on that ESXi host, but it is a domain controller with very little IO.
- This app previously ran on a standalone Windows 2003 server. We upgraded to Windows 2008 R2 and also upgraded the app to a newer version at the same time, so it's possible either of those factors may be contributing.
What are the options to dig into this deeper? Is there a way to see what the SQL server is doing under the hood (what queries are being executed, etc)? Does this behavior seem normal, and the load is just too much for the storage? I am wondering if something is not configured correctly.