August 19, 2010 at 9:25 pm
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
August 20, 2010 at 2:01 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 3:52 am
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.
August 20, 2010 at 4:08 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 4:32 am
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.
August 22, 2010 at 6:54 am
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
August 22, 2010 at 11:02 pm
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.
August 23, 2010 at 2:03 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2010 at 9:20 am
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.
August 25, 2010 at 7:10 am
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 on googles mail service
August 25, 2010 at 11:25 am
Have you customised the " Degree of Parallelism" on SQL server side or your query resricting /limit the number of processors to use in parallel plan execution? This is also one of the cause to peg the CPU...
Thanks
Jay
http://www.sqldbops.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply