64 bit sql server - out of control

Question

One of my 64 bit SQL Server boxes is running out of control. CPU just hits 100% and stays there. There isn't much workload on this box. I have 2 cpus and 8GB RAM. When I look at the task manager, SQL Server occupies around 1.5 GB of RAM whereas available RAM is only around 800 MB. Not sure where is the rest going. Task manager doesn't show anything else taking up even more than 100 MB of RAM. there is nothing else running on the box except mcafee. Which is now disabled.

I am using windows 2003 sp3. My SQL Server is SQL 2005 sp3. Pagefile grows up to around 7GB, not sure it would do that.

SQL Server starts up nicely.. but in few hours cpu usage goes high, RAM dwindles and server response starts to crawl.

What could be going on here??? how can I troubleshoot the issue.

---

Shahab

Answer 1

Hello Shahab,

Task Manager doesn't report accurate memory usage by sqlserver process on 64-bit OS. You can use performance counters or DBCC MEMORYSTATUS to check what do you actually have. In your configuration you should leave 2GB for OS and limit SQL Server memory.
You should check SQL Server processes running and try to determine which one is sucking up all your resources.

Regards,
Akim

Answer 2

Hi Shahab

Akim is right.

Set the SQL Max Server Memory setting to a sensible amount - 6GB in your case should be fine, and see if this addresses the problem.

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 3

Can you run these two queries, and report back on the results?

This will tell us what your top wait types are, which will help us zero in on the problem.

-- SQL and OS Version information for current instance
SELECT @@VERSION AS [SQL Version Info];

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
 FROM sys.dm_os_wait_stats
 WHERE wait_type NOT IN('SLEEP_TASK', 'BROKER_TASK_STOP', 
  'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  'LAZYWRITER_SLEEP')) -- filter out some irrelevant waits
SELECT W1.wait_type, 
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold for waits

---

http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.

Answer 4

Set the SQL Max Server Memory setting to a sensible amount - 6GB in your case should be fine, and see if this addresses the problem.

If you have the default settings for MaxServerMemory, I'd start with the recommendation above by Ewan.  I have seen SQL Server get into serious paging problems which can drive CPU utilization up.  If you have MaxServerMemory set already, then you need to run the Scripts by GlennAlanBerry to see where your highest waits are occuring.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!