ms-sqlserver >> Why is SQL Server using so much Virtual Memory?

by e.wiesenekker » Mon, 06 Dec 2004 19:03:37 GMT

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?

Thanks for any information.

Evert Wiesenekker

PS
Besides the northwind database I only have one simple extra database
(70 Mb in size) installed.

ms-sqlserver >> Why is SQL Server using so much Virtual Memory?

by Erland Sommarskog » Mon, 06 Dec 2004 19:53:40 GMT


Evert Wiesenekker ( XXXX@XXXXX.COM ) writes:

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, XXXX@XXXXX.COM

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

ms-sqlserver >> Why is SQL Server using so much Virtual Memory?

by Evert Wiesenekker Wiesenekker » Tue, 07 Dec 2004 01:49:00 GMT

Thank you very much for your reply!

Yes, I did a reconfigure.

I did some Googling around but did not find a good answer

Thanks!







*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Similar Threads

1. SQl server using high Virtual Memory

2. SQL Server Using ONLY virtual memory

I have MS SQL 2000 SP3 on a Win2k3 box with 8 GB ram and 4 CPUs.  The
only thing running on this server is SQL, and no other processes are
significant in size.

PROBLEM:  The SQL server is running only in virtual memory and will not
switch to physical memory.  This can be seen in the task manager after
adding the virtual memory column.

HELP!!!!!

3. Why is my SQL server not using more than 1.7 GIG memory - SQL Server

4. SQL using all virtual memory

I've been searching for an hour and didn't find an answer to this one.
Running a Quad Xeon 550 system with 2GB Physical RAM and a 2GB pagefile,
with SQL 7 SP4 on NT4 Enterprise.
According to Task Manager, as of this moment, sqlserver.exe is using
1590220K of RAM.  I have the VM Size column up, and it is at 1586092K of
RAM.  Now, to me that says nearly ALL of the RAM that SQL is using, is
Virtual Memory.  That seems horribly inefficient (and, I'm thinking, perhaps
the reason why sqlservr.exe causes anywhere from 50 - 400 Page Faults / sec
when it gets busy).

Can someone add some wisdom here and explain what is going on?  Wouldn't it
be best for SQL to use physical RAM?  Can I force it to?


Thanks


5. why when using =* and *= , i am getting the same records - SQL Server

6. Virtual memory and Physical memory for SQL

Can someone explain in lay man terms, what does SQL do with virtual memory 
and what does it do with physical memory? what info is contained in these 
memory?

Is Virtual memory always on disk ?

Btw, whats the difference between the 2 ?

Thanks 


7. find out how much virtual memory is used up - SQL Server

8. Sql Server, Virtual Memory, Cursors

Greetings All, I have an operation that involves the use of a cursor.
This cursor is looping over a table that has about 20,000 rows.  If I
run this operation when SQL server is just started and total virtual
memory used by SQL is low the operation takes about 3 minutes.  If I
wait and do some other work which causes SQL server to take up about
1.2 Gb of Virtual memory, 800MB of my 1Gb physical RAM and then run my
cursor operattion it will take about 5 hours?  Can anyone explain to me
the relationship between virtual memory and cursor performance?  Or, if
there is no relationshiop what is the best way to see why the cursor
operation takes 3 minutes one day and 5 hours the next day.  The
machine I am using is exclusive to me and I am not running anything
else on it outside of Outlook, Sql Server, and internet explorer?  Any
and all help would be greatly appreciated.

TFD