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 12»»

SQL Server 2008 Memory utilization Expand / Collapse
Author
Message
Posted Thursday, August 19, 2010 9:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 1:47 AM
Points: 6, Visits: 42
We have our application using SQL Server 2008 running on windows 2008 64 bit machine. The application was working fine for some days. Now the client has started reporting that the application hangs after certain transactions and that the cpu utilization of SQL Server 2008 is very high.

After analyzing this problem, we increased the max memory settings, AWE enabled options on that machine. After doing the setting changes, the application worked fine for two weeks. After two weeks the same problem has appeared again. The application has started hanging when the SQL Server cpu utilization is high.

RAM on the machine is 8GB. So we have configured 7 GB of RAM (max server settings) for SQL Server 2008.

Is this known issue wiith SQL Server 2008? Is there any solution to this problem? What can be done to stop my application from hanging?

Thanks in advance

Post #972266
Posted Friday, August 20, 2010 2:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,614, Visits: 37,079
Is SQL also 64 bit? If so, there's no need for AWE.

I'd drop the max memory down to 6GB. Letting SQL take just over 7 GB (the 7 is the size of the buffer pool, SQL also takes a small amount of non-paged memory) leaves the OS with very little and may cause problems.

High CPU may not be related to memory. It's most likely caused by poorly written queries and/or poor indexing. Is doing an optimisation exercise on the SQL databases possible, or is this vendor apps?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #972344
Posted Friday, August 20, 2010 3:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 1:47 AM
Points: 6, Visits: 42
Yes SQL is also 64 bit. Will there be any problem by enabling AWE on the machine? The application works fine on 32 bit OS, with SQL Server 2000/2005. This problem is happening with SQL Server 2008 only. The number of users using the system is more that 50 users.
Post #972393
Posted Friday, August 20, 2010 4:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,614, Visits: 37,079
shailashree (8/20/2010)
Yes SQL is also 64 bit. Will there be any problem by enabling AWE on the machine?


AWE is ignored on 64 bit SQL. It is ONLY needed for 32 bit SQL Server.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #972398
Posted Friday, August 20, 2010 4:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 5:06 AM
Points: 491, Visits: 555
Hi,
try to run performance monitor with memory related counter.
( Mostly Memory Manager -> Lock Memory, SQL Cache Memory, Target server Memory and Total Server memory).

Target server memory is the Memory which you are setting as max memory whereas total server memory is the current memory consumption.

Post #972403
Posted Sunday, August 22, 2010 6:54 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:31 PM
Points: 416, Visits: 573
Have you enabled Lock pages in Memory for SQL Service account? If not provide the permission to SQL Service account. We faced similar problem (i.e memory is always full and paging out) and after adding the service account it got resolved

More info http://blogs.msdn.com/b/slavao/archive/2005/08/31/458545.aspx


Regards..Vidhya Sagar
SQL-Articles
Post #973042
Posted Sunday, August 22, 2010 11:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 8, 2010 1:47 AM
Points: 6, Visits: 42
Lock pages in memory is enabled for the sql server account. After enabling this option, the application worked fine for two weeks without any deadlock. The problem resumed again after two weeks.
Post #973185
Posted Monday, August 23, 2010 2:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,614, Visits: 37,079
shailashree (8/22/2010)
After enabling this option, the application worked fine for two weeks without any deadlock. The problem resumed again after two weeks.


Deadlocks are unlikely to be a result of memory configuration. Deadlocks are typically a result of badly-written queries and/or poor indexing. That's probably a better avenue to investigate for resolving the deadlocks.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #973254
Posted Tuesday, August 24, 2010 9:20 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 27, 2014 1:17 AM
Points: 549, Visits: 1,017
If you literally meant deadlocks - thats a whole different story (monitor the locking).

If you meant freezing up - You may want to set a min server memory as well (plus your max is too high as mentioned for an 8gb machine). We had an issue where the system cache was eating into the SQL memory if it wasn't in use, and then it wouldn't reclaim the memory when needed even with locked pages switched on. Small chance this could be your reason.
Keep an eye on the Target and Total server memory counters in perfmon (as mentioned) and the system cache counters too.
Post #974216
Posted Wednesday, August 25, 2010 7:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 4,470, Visits: 6,399
Deadlock troubleshooting and resolution:

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Note there are 2 additional parts to this blog series.


You can also get a professional on board who could probably resolve the issue in a matter of minutes or hours as opposed to many days like this has been going on.


Best,

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

Add to briefcase 12»»

Permissions Expand / Collapse