SQL 2008 Performance Counters gone MAD - Ample RAM but Page Life expectancy and Buffer counters are not happy

Question

SQL 2008 Performance Counters gone MAD - Ample RAM but Page Life expectancy and Buffer counters are not happy

Maclarenf1

Grasshopper

Points: 23
More actions
April 13, 2011 at 2:50 am

#389263

Hi All, my first post here so be gentle
I got a Prod Physical SQL Server with 24 Core's and 64GB Free RAM !!!
but the PerfMon counters seem to indicate it's having RAM trouble, yet there is ample free RAM
buffer hit ratio is around 90-94 mark, but not above 97%
Almost next to no writes, but heck of a lot of reads
can any of the SQL guru's tell me where i should be looking at next, so solve this issue... the system is not Slow... but it a lot slow for what it should be
OS - WIndows 2008
DB - SQL 2008 SP1
DB's, there only 1 is used for ERP system by around 30 users

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

On first glance, it looks like you're swapping stuff out of memory a lot. I'd focus where I usually find myself focusing, the queries. Use a server-side trace to get information about the queries being run or query the cache to pull the latest queries out there and figure out which queries are running the longest or are most frequently called. Also, focus on which queries are moving the most data, because you really are swapping lots of data around there. Then figure out why. Are there things that can be done to the code or can you improve things with indexing?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Craig Purnell SSCrazy Points: 2720 More actions · Answer 2

Check your 'min server memory' and 'max server memory' to see that they were not set to some arbitrary value. Also, if you are on 64 bit, verify the "lock pages in memory' right is granted to the service account.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

There are MANY unanswered questions here. Chief among them:

1) what is max memory setting for sql server?

2) what else is running on the server?

3) what is the underlying IO subsystem?

4) how much total data is 'active' - i.e. being used regularly by the database apps/users?

Oh, and beware setting lock pages in memory! If you have improper setup on sql server you can cause bad stuff to happen. See here: http://support.microsoft.com/kb/918483

"If you assign this user right on systems that are configured incorrectly, the system may become unstable or experience a performance decrease of the whole system. "

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Maclarenf1 Grasshopper Points: 23 More actions · Answer 4

Thanks for kind reply guys

i will check the MAX and Min setting

this is dedicated SQL Server only used to host EPR System DB "basically live and Test DB"

No other App are active or hosted on this system

Drive setup is

C: - OS and SQL Program

D: - SQL data and Page file

E: - Log file

F; - SQL Temp DB

all RAID 10 Arrays and 15RPM drives

i will research in to lock pages in memory setting as well