<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
SQL Server 2008 Memory utilization
11 posts, Page 1 of 2
1
2
»»
SQL Server 2008 Memory utilization
Rate Topic
Display Mode
Topic Options
Author
Message
shailashree
shailashree
Posted Thursday, August 19, 2010 9:25 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, December 08, 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
GilaMonster
GilaMonster
Posted Friday, August 20, 2010 2:01 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 37,669,
Visits: 29,922
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
shailashree
shailashree
Posted Friday, August 20, 2010 3:52 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, December 08, 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
GilaMonster
GilaMonster
Posted Friday, August 20, 2010 4:08 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 37,669,
Visits: 29,922
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
chetanr.jain
chetanr.jain
Posted Friday, August 20, 2010 4:32 AM
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 465,
Visits: 437
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
vidhya sagar
vidhya sagar
Posted Sunday, August 22, 2010 6:54 AM
SSC-Addicted
Group: General Forum Members
Last Login: Tuesday, March 19, 2013 2:45 AM
Points: 416,
Visits: 521
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
shailashree
shailashree
Posted Sunday, August 22, 2010 11:02 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, December 08, 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
GilaMonster
GilaMonster
Posted Monday, August 23, 2010 2:03 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 37,669,
Visits: 29,922
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
Shark Energy
Shark Energy
Posted Tuesday, August 24, 2010 9:20 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Friday, March 22, 2013 9:32 AM
Points: 547,
Visits: 995
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
TheSQLGuru
TheSQLGuru
Posted Wednesday, August 25, 2010 7:10 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 3,574,
Visits: 5,110
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 »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
