March 27, 2013 at 5:24 am
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
March 27, 2013 at 8:46 am
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
March 27, 2013 at 9:12 am
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
March 27, 2013 at 11:20 am
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
September 26, 2013 at 12:08 pm
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.
September 27, 2013 at 2:13 am
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!
September 27, 2013 at 3:42 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply