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.
Best Answer
How about
Database pages,Free list stalls/sec,Page reads/sec,Page lookups/sec,Readahead pages/sec? Their behavior, correlated with the event, would be very useful to know. Similar, all the counters in Memory Manager category.Could be a query that scans a large amount of cold data hits the server. As it reads the cold data, it evicts pretty much everything else in the BP, including its own just-read pages. After the event the BP slowly start building up the hot pages and PLE start to increase. Counters in Access Methods object could confirm this.