Evert Wiesenekker (e.***********@wisl.nl) writes:
I have two instances of SQL Server running on my Development machine.
I am having some performance problems and while investigating the
problem I saw with the Process Explorer form Sysinternals that both
instances consume each 800 Mbytes of memory!
I experimented with sp_configure and by giving both instances a fixed
memory size. Both methods do not seem to have any effect.
Can anybody explain me why SQL Server is using so much memory?
SQL Server is designed to grab as much memory as it can get. The idea
is that normally run you run SQL Server on a dedicated server, so no
other applications compete. And the purpose for that all memory is simply
cache. The more data SQL Server can have in cache, the better the response
times.
Of course, on a development machine, this behaviour is not really suitable,
and limiting the memory for SQL Server is a very good idea. SQL Server will
yield memory, if other applications are asking for memory, but it may not
yeild fast enough, and anyway, it's not fun to have all other apps paged
out.
I can't say why SQL Server does not yield memory despite your change. The
setting does not control all memory allocated by SQL Server, only the buffer
pool as I recall. Then again, that is the main body of allocated memory
normally. But a quick check: you did run RECONFIGURE after your change,
didn't you?
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
