0

I'm having no end of trouble with one of our production SQL boxes - users are experiencing massive drop offs in performance and this is having severe impacts on the business. I've been investigating mainly from a SQL/dba perspective and using sp_BlitzFirst I've been seeing intermittent reports of very slow read/write times (typically ~100-300ms but at points as high as 1000ms+).

The real head scratcher is that after getting our infrastructure guys involved they can't see anything amiss from their end.

The server is a virtual machine (Windows Server 2008 R2, SQL 2008 R2 Standard with 12 cores and 32Gb of RAM providing the databases for a Dynamics CRM 4.0 instance and another web application that is integrated with CRM) running under vmware with storage being provided by a Dell EqualLogic SAN and they are seeing times more in the 30-40ms range. Now it's entirely possible that the samples taken by BlitzFirst and vmware are simply not coinciding and causing the disparity but if not could there be any other explanation? I've used perfmon on the server and while the averages for the disk read/write are pretty reasonable I see definite spikes that are much higher then vmware is reporting - much closer to the figures that BlitzFirst is reporting. Is there anything else I should be looking at?

I'm reasonably certain that the problems BlitzFirst is reporting are real as they always occur at the same points the users experience major problems. To be honest the application performance of this CRM has always been pretty crap but even with a pretty low bar it's still falling well short.

Improve this question
7
  • CHeck your CPU ready time, that gets translated into disk waits in some cases. Make sure the CPU in the vm is not overcommitted.
    – Stijn Wynants
    Commented Feb 10, 2017 at 12:36
  • Thanks - that's definitely worth a look.. I've asked the infra team to have a look and let me know.
    – motosubatsu
    Commented Feb 10, 2017 at 13:11
  • how many databases are on the instance and how big they are ? What is the max memory setting ? CPU power options set to high performance ? Is balloon driver being used ? Check if the host machine is not overloaded.
    – Kin Shah
    Commented Feb 10, 2017 at 14:32
  • In total there are 17 databases on this instance although 90%+ of the load is spread across two of these. Max memory setting is 20000. CPU power is currently set to "Balanced" although I don't have access to change that and I'm not certain whether that even has an effect given the CPUs are virtual.
    – motosubatsu
    Commented Feb 10, 2017 at 14:45
  • I don't know about the Balloon Driver settings as I have no access to vCenter but I can try find out. Infrastructure claim the host isn't overloaded but they are planning on reducing the spec of some test VMs that are on the host to see if that makes a difference.
    – motosubatsu
    Commented Feb 10, 2017 at 14:52

1 Answer 1

Reset to default
0

Not wanting to be one of those OPs who just disappears without providing closure here's an update..

Despite receiving multiple assurances from infra that the vhost was in no way overloaded and that contention for physical resources couldn't possibly be the problem they eventually moved multiple VMs off the host to another one and the symptoms disappeared once that was complete. So either the problems were caused by resource contention on the host bottlenecking the VM's performance or the symptoms that just coincidentally looked like resource contention just so happened to coincidentally disappear at the same time that load on the vhost was reduced.

So many thanks to everyone for the helpful comments/pointers!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.