Slow DB Performance. Seems to be memory related.
- by David
I am seeing a pooorly performing web app with a SQL 2005 backend. The db is on a w2k3 machine with 4GB RAM.
When I run perfmon on it I see the following.
Page life expectancy is low. Consistently under 300 while the Buffer cache hit ratio is always 99% +.
The target server memory is always 1618304 and the total server memory is always a number just below that. So it seems that it isn't grabbing enough of the available memory.
I have AWE enabled, with the lock pages right for the SQL service account and have set a maximum of 2.25Gb... but it doesn't go near that.
When I restart the SQL service the page life expectancy goes much higher, 1000+, and the total target memory starts at 0 and slowly works its way back up to the previous limit.
Then it hits the limit and the page life expectancy goes back down massively to <300.
So I'm guessing there is something limiting the amount of memory.
Any ideas on what that would be and how I can fix it?