Memory usage grows

Last Post 25 Jan 2007 03:13 AM by JHunter. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
plantrol
New Member
New Member

--
24 Jan 2007 08:10 AM
Hi!

I have an issue with sql server 2005. It weems that the memory usage grows forever and never decreases. If I get users on they use a certain amount of memory. If they all get off the system, the memory usage never goes down. If they get back on it then grows. Eventually the machine starts to thrash because it runs out of physical memory. What's up with that??

Joe
plantrol
New Member
New Member

--
24 Jan 2007 09:50 AM
Hi!

THe machine is dedicated. It has 4gb. It is set with AWE off.

Joe
SQLUSA
New Member
New Member

--
24 Jan 2007 11:59 PM
If you are using sp_configure to set max memory,
execute

reconfigure

for the new setting to take.

Doing it from SSMS Object Explorer, restart SQL Server.

Chewing up memory is not typical though. Something else maybe going on.

Kalman Toth
SQLUSA: http://www.sqlusa.com
JHunter
New Member
New Member

--
25 Jan 2007 03:13 AM
You don't mention if your 32 or 64 bit, or which OS you're running.

Assuming you're on 32bit, you'll need to add the /3GB flag to the boot line to allow SQL Server to address above the 2GB limit.

It is normal for SQL to consume memory, it will keep all memory (it will reuse "free" memory), until the OS requests it to release some. I'd expect to see any dedicated SQL server having their sqlservr.exe process consuming the max available memory.

What are your cache/buffer counters sitting at? Is your cache effective? Are the queries optimal? (if every query is doing a table scan, caching will be a nightmare and once physical memory has be consumed performance will suffer even more).

I'd suggest monitoring the allocated memory is not the best metric to measure relating to performance issues...

Jamie
plantrol
New Member
New Member

--
25 Jan 2007 10:14 AM
Thanks for all the direction! It look slike a problem with several queries. I setup a test server and have been playing all day. I can narrow it down to several reports that have VERY BAD sql in them.

Thanks Again!
Joe
You are not authorized to post a reply.

Acceptable Use Policy