I am trying to help a customer with a performance problem on a SQL server that runs several different OLTP systems. The system is continuously handling hundreds of batches per second. CPU load is very low, the disk is 100% busy almost all the time.
Users are complaining about longer response times than expected. This is a brand new server, so we have no previous baseline.
This is a virtual server using Hyper-V, the physical server is only used for this virtual server.
16 logical CPU:s
23GB allocated to SQL server using minimum and maximum server memory
SQL Server 2012, Standard edition
Lock Pages in memory is NOT enabled
When looking at memory usage on the machine, there does not seem to be any external memory pressure - there is no paging and there seems to always be physical memory available.
When trying to diagnose the problem I have found something that I think might be the cause of the heavy disk read activity.
I watch the following perfmon counters:
SQL Server Memory Manager:Free Memory (MB)
SQL Server Memory Manager
atabase Cache Memory (MB)
SQL Server Buffer Manager
age Life Expectancy
When the server is loaded I would expect Free memory to be very low, Database Cache memory to be close to the maximum, and Page Life expectancy to be high.
What I am seeing is instead that Free memory is constantly about 16 GB, Database Cache memory varies between 200 MB and 2 GB. Page Life expectancy repeatedly drops to 0, climbs to 100, and drops to 0 again.
So, there is lots of free memory available, but it is not getting used!
I have used SQL Profiler to look for any SQL commands that reads has a large amount of reads, but I ave not found anything significant. There are simply a very large number of small requests.
Do you have any ideas about what could be causing this behavior?
Any ideas about how to continue trying to find the cause of the problem?