We have a production server that dumps it's Page Life Expectancy many times a day (climbs to 2500s and falls back to near 0s)… After watching some training videos about virtualization for SQL Server, I was pointed towards several Perfmon counters to look at:
- Memory Limit in MB
- Memory Ballooned in MB
- Memory Reservation in MB
- Memory Swapped in MB
Ballooned, Reserved, and Swapped are all at 0 but Memory Limit was set to nearly 3tb (perhaps a default setting like SQL's Max Memory setting…)
I viewed these counters during an event when the PLE dropped to 0 and none of the counters changed. According to Spotlight, Buffer Cache and Procedure Cache are remaining at high % utilization and no stats seemed to really budge except the PLE itself.
To me, this points to not enough RAM allocated to the machine to the point that many of the queries are forcing the server to look to disk to get results.
What steps do I need to take to prove or refute this?
Server Config:
– Windows 2008 r2 VM
– 4 cores
– 12GB RAM
– main DB is ~150GB
Best Answer
Wes. Those Perfmon counters are a good start to make sure that the VM is configured correctly (meaning, the host isn't wildly overcommitted on RAM.)
However, they don't help size the VM.
If you're having performance problems, you'll want to identify two things:
To get the top wait, try running sp_BlitzFirst @SinceStartup =1. (Disclaimer: that's an open source script, and I'm one of the authors.) You can also use 3rd party monitoring tools like Idera SQL DM, Quest Spotlight, and SQL Sentry Performance Advisor. (Disclaimer: those cost money, and I didn't write any of those, but they're awesome.)
The @SinceStartup = 1 switch gives you waits since startup, which isn't as cool as monitoring software, but it's a free start.
If your top wait is PAGEIOLATCH, that means reading data pages from a data file. If that's the case, you want to find the queries reading the most data. For that, use sp_BlitzCache @SortOrder = 'reads' (again, disclaimer, open source script, and I'm one of the authors.)
That'll give you the top 10 queries ordered by how much data they're reading. They're usually candidates for index tuning or query tuning. I'd always rather tune those rather than add memory, but if PAGEIOLATCH is your top wait, and you're not allowed to tune those top 10 queries, nor tune their indexes, then the next fix is to add memory to the VM. (But only after going down this route of troubleshooting.)