I'm trying to put my hands on the reason this SQL Server slows down sporadically. There are no patterns detected so far. It's a SQL Server 2008 R2 running on a Windows Server 2008R2 VM on top of VMware 5.0. The VM has 16GB of memory with max server memory in SQL set to 8GB. On seemingly random days i see Page Life Expectancy drop off a cliff from 42,000+ sec to 200-300 sec for a few minutes. It does not decrease with time. It literally falls of a cliff. A complete 90 degree drop on the chart when i plot the values. then i it starts to build up again
The Target Server Memory and Total Server Memory counters are consistent at 8GB throughout. There also no spikes in batch requests/sec or user connections at these times.
On the VM level, Memory Mapped is consistently at 16GB and Memory Ballooned is 0, so the VM is not under memory pressure.
I'm trying to think where i should look next. What counters or areas of interest should i put my eyes on as a next step to understand these performance drops on the server.