Memory leak?

Last Post 11 Apr 2002 11:55 AM by ledman. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ledman
New Member
New Member

--
10 Apr 2002 11:10 AM
SQL 7.0 server really bogs down. Reboot and it runs ok for several days usually, then slowly bogs down again.

512MG RAM on the box and 4 processors.

Performance Monitor shows good numbers for Buffer Cache Hit Ratio (98%+), and Cahe Hit Ratio for AdHoc Plans and Stored Procedures (98%+) at all times, which makes me think memory is sufficient.

When it's slow the Pages/sec is very active (not just some spikes, but pretty much 50, 100, 200+) and %usage on Paging File goes up (5% or less normally but 20%+ when slow). Processor usage also goes up when it's slow (4 processors all below 30% normally, but all 50%+, 70%+ when slow). Task manager available memory goes below 4000K. It's almost like there's a memory leak? I don't have diskperf turned on so no reading on that.

Nothing else runs on this box except SQL.

Is it normal for task manager to show that SQL has almost all the memory even though Total Server Memory under the SQL Server Memory Manager counter in PerfMon is way less than that? And if SQL has enough memory, and the chae hit ratios are all very good, why is there paging going on?

TomPullen
New Member
New Member

--
11 Apr 2002 05:38 AM
By default, SQL Server 7.0 is configured to dynamically use memory, i.e. it will consume memory and not release it unless other applications need it. You can stop this behaviour by fixing the amount of memory SQL Server uses. (You do this on the memory tab when you right click, configure a server in Enterprise Manager).

A good way of checking exactly how much memory SQL Server is using to to monitor the value of Process, Private Bytes for sqlservr.exe in Performance monitor.
ledman
New Member
New Member

--
11 Apr 2002 11:55 AM
Funny you should mention the Private Bytes because that's where I get confused. For example, Task manager may show that sqlservr.exe has 445MB of memory, yet Performance Monitor Private Bytes will show a higher number like 463MB+. And the private bytes fluctuates a little but not much. Is that significant?

And virtual bytes is at 970MB+ yet our page file is only 776MB...Not sure what that's trying to tell me...???

And I understand about SQL holding on to the memory until another process needs it, and maybe another process never needs any more...maybe it's all SQL activity....

....But the bottom line is that it seems contradictory for the cache hit ratio's to be 98%+, and SQL Server:Memory Manager:'Total server memory' to be 134MB, yet Memoryages/sec to be very active...it's like SQL has plenty of memory and it's finding pages it needs in memory, yet it's writing things in and out of disk (the paging file) like crazy.

Maybe my confusion is that cache hit ratio includes pages "found" in virtual memory (i.e., the paging file)? Is that the case? I guess that would explain why cache hit ratio is high yet pages/sec is also high...does that sound right? And if that's the case, maybe we just need more RAM so it can get the hits in RAM instead of going out to the paging file???
TomPullen
New Member
New Member

--
12 Apr 2002 01:59 AM
The thing about Cache Hit Ratio is that it's cumulative over time, from when the server is first started. So it doesn't teel you precisely how much recent data has been found in memory, just the total amount over time. So I don't rely on it as a counter on its own.

I can't explain the discrepancies with your memory counts between perfmon and task manager, but it doesn't sound significant. Also the amount of paging SQL Server is doing doesn't sound good!

I would fix the amount of memory for SQL Server and see how it behaves after that. Maybe you do need more RAM, but 512MB seems like a reasonable amount.
You are not authorized to post a reply.

Acceptable Use Policy