SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Low memory usage and low PLE on 23GB virtual server


Low memory usage and low PLE on 23GB virtual server

Author
Message
Stefan_G
Stefan_G
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1373 Visits: 961
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 ManagerBigGrinatabase Cache Memory (MB)
SQL Server Buffer ManagerTongueage 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
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33637 Visits: 8683
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 on googles mail service
Stefan_G
Stefan_G
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1373 Visits: 961
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
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33637 Visits: 8683
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 on googles mail service
kfqwang
kfqwang
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 89
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.
Stefan_G
Stefan_G
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1373 Visits: 961
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!
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33637 Visits: 8683
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 on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search