Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Low memory usage and low PLE on 23GB virtual server Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 5:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
I am trying to help a customer with a performance problem on a SQL server that runs several different OLTP systems. The system is continuously handling hundreds of batches per second. CPU load is very low, the disk is 100% busy almost all the time.

Users are complaining about longer response times than expected. This is a brand new server, so we have no previous baseline.

This is a virtual server using Hyper-V, the physical server is only used for this virtual server.

16 logical CPU:s
28GB memory

23GB allocated to SQL server using minimum and maximum server memory

SQL Server 2012, Standard edition

Lock Pages in memory is NOT enabled

When looking at memory usage on the machine, there does not seem to be any external memory pressure - there is no paging and there seems to always be physical memory available.

When trying to diagnose the problem I have found something that I think might be the cause of the heavy disk read activity.
I watch the following perfmon counters:
SQL Server Memory Manager:Free Memory (MB)
SQL Server Memory Manager:Database Cache Memory (MB)
SQL Server Buffer Manager:Page Life Expectancy

When the server is loaded I would expect Free memory to be very low, Database Cache memory to be close to the maximum, and Page Life expectancy to be high.

What I am seeing is instead that Free memory is constantly about 16 GB, Database Cache memory varies between 200 MB and 2 GB. Page Life expectancy repeatedly drops to 0, climbs to 100, and drops to 0 again.

So, there is lots of free memory available, but it is not getting used!

I have used SQL Profiler to look for any SQL commands that reads has a large amount of reads, but I ave not found anything significant. There are simply a very large number of small requests.

Do you have any ideas about what could be causing this behavior?
Any ideas about how to continue trying to find the cause of the problem?

/SG
Post #1435836
Posted Wednesday, March 27, 2013 8:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 4,469, Visits: 6,398
Given that profiler shows no queries with large numbers of reads took away my normal response.

Complete guesses here:

1) someone accidentally put windows/sqlserver 32 bit on the system (yes, I have seen that happen!)

2) the IO subsystem is completely insufficient and/or grossly misconfigured. Have you done IO stall analysis yet?

3) did you do a wait stats analysis yet?


Note: I have been a professional SQL Server tuner for 15 years and I pretty much never care about PLE unless it is taking a precipitous dive to 0 indicating a likely bug somewhere on the system (drivers/firmware/3rd party apps, etc).

Speaking of professional, there really are a LOT of things that could be at play here and you could hunt and peck on a forum for days without resolution. Consider getting a good tuner to remote into the system. It could be just a matter of minutes to a few hours before the cause is isolated and corrected. Client wins, and you win by getting some mentoring to enable you to better troubleshoot the next time you come across something like this.




Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1435955
Posted Wednesday, March 27, 2013 9:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
TheSQLGuru (3/27/2013)
Given that profiler shows no queries with large numbers of reads took away my normal response.

Complete guesses here:

1) someone accidentally put windows/sqlserver 32 bit on the system (yes, I have seen that happen!)

2) the IO subsystem is completely insufficient and/or grossly misconfigured. Have you done IO stall analysis yet?

3) did you do a wait stats analysis yet?


Note: I have been a professional SQL Server tuner for 15 years and I pretty much never care about PLE unless it is taking a precipitous dive to 0 indicating a likely bug somewhere on the system (drivers/firmware/3rd party apps, etc).

Speaking of professional, there really are a LOT of things that could be at play here and you could hunt and peck on a forum for days without resolution. Consider getting a good tuner to remote into the system. It could be just a matter of minutes to a few hours before the cause is isolated and corrected. Client wins, and you win by getting some mentoring to enable you to better troubleshoot the next time you come across something like this.


* SQL Server is 64 bit
* The IO System is overloaded because we are issuing much more reads per second than would have been necessary if the cache had worked as expected. We are using an IBM SAN, we have had IBM experts involved to verify that there are no problems on the SAN side.
* Wait stats analysis indicates that the only signigificant waits are for IO
* I also usually do not care about PLE, but the combination of lots of free memory (over 16 GB) and very low PLE (going down to 0 almost every minute) indicates that something is really wrong here.
* I am an SQL tuning professional myself, but I have never seen this behavior before.

If you have any concrete ideas on how to isolate the cause of this behavior I really want to hear about it.

/SG
Post #1435977
Posted Wednesday, March 27, 2013 11:20 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 4,469, Visits: 6,398
A) does DBCC memorystatus reveal anything?

B) review the SQL and windows error logs (especially around startup) for anything unusual

C) review hyperV and host config, especially everything related to memory (dynamic memory stuff would be first place to look). http://msdn.microsoft.com/en-us/library/hh372970.aspx.

D) move sql to a physical host to confirm that hyperV or host config is cause?



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1436058
Posted Thursday, September 26, 2013 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:39 PM
Points: 4, Visits: 57
Any luck with this issue?
I am having same problem, VM server 64 bit Enterprise SQL 2012, SQL Server Memory Manager:Free Memory is constantly high and Database Cache Memory, PLE keep on being low.
Was not able to find the cause.
Post #1498993
Posted Friday, September 27, 2013 2:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
The problem went away when we installed CU4.

http://support.microsoft.com/kb/2845380

See this thread for more info:

http://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx

Good luck!
Post #1499246
Posted Friday, September 27, 2013 3:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 4,469, Visits: 6,398
Stefan_G (9/27/2013)
The problem went away when we installed CU4.

http://support.microsoft.com/kb/2845380

See this thread for more info:

http://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx

Good luck!


Glad you got it fixed! Well, glad MICROSOFT fixed it!!

I am coming across this more and more in my consulting business. Nice to have a magic bullet for the client!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1499280
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse