April 4, 2008 at 1:27 am
With SQL Server 2005, I have the problem that sqlserv.exe keeps using more and more memory. Now I did some research and found the following anwers in different places:
*It seems to be the way that SQL Server works. It just uses all the memory which it can get. I have two remarks about this:
1) I that is true, then why is a fresh installation of SQL Server not consuming all the memory it can get?
2) After a few weeks, it consumes so much memory that the whole system (even SQL Server itself) start running very slow. I don't think that's the intended behavior?
So I thought about restricting the maximum memory that SQL Server uses, but people keep saying that's not the way it should be done: SQL Server should be allowed to allocate memory dynamically.
Can anyone help me with my questions?
One other thing that may be of relevance: This instance is a disitributor for replication and the distribution.MDF file also seems to be increasing over time. It became much larger than the application database.
Thanks in advance for replies.
April 4, 2008 at 2:14 am
It's not strictly true that SQL Server grabs all the memory it can get (not in one go anyway). What happens is, if SQL Server needs 200 mb then it grabs it, and it will continue grabbing memory (as it needs it) until it has reached the max memory limit or the available memory on the server. This explains your observations. In the long run though SQL Server does end up grabbing all of the available memory.
Typically, what should happen is, if the OS needs memory to service a request from another application then SQL Server will release some memory to the OS. If this isn't happening then the likliehood is that the OS doesn't need that memory. Although it is possible for this to not happen as it should if SQL Server is handling many i/o requests.
Restricting the maximum memory doesn't change the fact that SQL Server is handling memory dynamically. It's when you specify a fixed memory value that you go from dynamic to static memory management, which isn't advised. That said, it sounds like it's possibly SQL Server that is memory bound rather than the OS so it might not make much sense to reduce the max memory limit.
Looks like you're going to need to get performance monitor running and start monitoring other system counters to get a handle on what's happening here.
About the distribution database being so big - a couple of things come to mind. Either some subscribers aren't getting their data replicated to them, or the clean up jobs are failing. Check the MSmerge_history table. If that's large then it points to the latter cause.
April 4, 2008 at 5:06 am
Thank you very much for your answers. They are really helpfull.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply