?
Solved

Sql server memory usage

Posted on 2004-11-29
21
Medium Priority
?
940 Views
Last Modified: 2011-04-14
sqlservr.exe is using up a lot of memory.  When I reboot it starts at around 20 mb, but every day it keeps climbing.  Last time I rebooted I had been running for about 20 days, and sqlservr.exe was using 1.2 GB of ram.  Currently it has been running for 6 days, and is up to 440,904 kb.  There is a file called tempdb.mdf that grows with the memory usage.  The tempdb.mdf file is in the same directory as the only active database on the server, it is called viewpoint, and it is a sonicwall router usage monitor.  The viewpoint database is only 20mb in size  

 
0
Comment
Question by:Tom_Hickerson
  • 7
  • 4
  • 4
  • +3
21 Comments
 
LVL 7

Expert Comment

by:ABaruh
ID: 12699222
Although the RAM Usage does rise continuously for the SQLServr.exe process, this is not an indication of a memory leak or that RAM is becoming less and less available to other processes on the server.  It is just another quirk in a Microsoft product.  Every single installation of SQL Server will show this same behaviour.

The tempdb is SQL's temporary database.  Every time SQL Server is restarted, this database is recreated.  It is normal for this database to increase in size.  All behind the scenes processing is mostly done in this database, such as cursors for example.
0
 
LVL 1

Author Comment

by:Tom_Hickerson
ID: 12699543
In this instance it seems to be hurting the servers performance.  This is a terminal server also, and after it got up to 1gb things really started slowing down.  I have had this setup for over 1 year, and have not noticed this behavior before now.

Tom  
0
 
LVL 7

Accepted Solution

by:
ABaruh earned 750 total points
ID: 12700307
CHeck the SQL Server properties Memory setup.  It should be set up to dynamically allocate memory.  If it is, try adjusting it to use a certain amount of your server's memory.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 9

Expert Comment

by:caball88
ID: 12700386
yeah the large memory is normally what happens on microsoft server apps, it will consume as much as it can. if you check out exchange it does the same thing. this does not mean there is a memory leak problem.
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12701354
Yes, basically when SQL Server has grabbed memory, even though it might stop needing that amount, it doesn't give it back. Work out how much you need to keep Terminal Services happy (!!!!) and then make sure SQL Server can't take too much for this to happen. I have to say you are being a bit optimistic trying to combine these two products on a single server in a production environment.

tempdb will grow. You can always shrink it using DBCC SHRINKFILE - there is often spare space within the file, in much the same way as SQL Server doesn't always actively use all the memory it has grabbed.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12701472
ABaruh: it is not a 'quirk' -- it is grabbing all the memory it can to optimize performance.

Tom H: it is a Bad Idea(TM) to run Terminal Server and SQL Server on the same machine. But if you must, to clarify ABaruh's answer:

- Open Enterprise Manager
- Navigate to your server, right-click on it and select 'Properties'
- Go to the Memory tab
- Move the bottom slider to the left (try halfway first, and experiment from there)

Note that you'll have to restart SQL Server (not the machine, just stop and start SQL Server through the Service Manager) for these changes to take effect.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12704766
Concur with must of this.

But one question is How much RAM have you got on the server Tom ?   IMHO  if this is standad sql server you should ideally got for 4GB.    2GB for sql and 2 for OS + other stuff.
0
 
LVL 9

Expert Comment

by:caball88
ID: 12706469
4GB is a lot but it depends on what amount of data and number of users you are supporting.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12706931
I dont agree that 4G of ram is a lot.   SQL Standard edition can use max 2gb.    If you only have 2GB on the server SQL's performance will be hampered by having to have less memory as the OS requires its share and other applications require theirs.

My opinion is that SQL should be max'ed out on memory, for all but the most trivial installations.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12707716
That all depends on your data size. I'm working with a fairly complex database (400+ tables), but the total data size (including audit et cetera) is about 3GB. For this kind of installation (small but complex sets), 2GB is quite sufficient. Besides that, you can have a 2TB database of which only 100MB is active (i.e., commonly used).
0
 
LVL 9

Expert Comment

by:caball88
ID: 12707955
i completely agree with you SjoerdVerweij, yuo don't necesarrily need to max out of memory for SQL. it all depends on what the server needs.
0
 
LVL 1

Author Comment

by:Tom_Hickerson
ID: 12713507
Thanks for all the feedback.  I realize SQL server and terminal services may seem like a lot for one server, but our SQL server database is in my opinion is a tiny database.  Here is some info... I have 2 gb of memory on this server.  The single active database sql server is using is only 20mb in size.  It just does not make sense that SQL server needs 1+gb of ram to process this 20mb databse?  Does this still all sound like "normal" sql server behavior.  
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12713651
It all really depends how the data is used. It is very easy to write big greedy queries on a 20MB database. It certainly doesn't sound abnormal behaviour, although I would have a think about the sorts of queries involved.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12714291
" It just does not make sense that SQL server needs 1+gb of ram to process this 20mb databse? "

Basically SQL Server will grab memory if it needs it up until the max threashold.   It wont give it back.  Chances are that if youve got 2gb on your server and 1.2Gb was grabbed by sql and given what else youve got on the server.   1.2G is probably all sql can get its hands on.

0
 
LVL 9

Expert Comment

by:caball88
ID: 12716441
like i said this is normal for sql, it will consume as much memory as it can just like exchange. even tho your database is it will build up memory over time. the server does not consume all of it at once right? i thought this was a problem on our sql server but through many searches and articles i ended up with the conclusion this was normal. the server is not responding slow in any way, i was just paranoid.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12716814
"the server does not consume all of it at once right?"  strictly speaking it can do.

lets say you stop and start sql server and the first this you do is take a reasonable sized table and perform a cartisiean join on itself ....... bang goes the free memory :)
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12718171
Max the SQL Server memory out at, oh, 256MB or so and see how it goes!
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12718190
"256MB or so and see how it goes!"    or not as the case may be
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12718250
True, but "Max the SQL Server memory out at 256MB and hope it doesn't slow down to a pathetic crawl!" sounds so pessimistic...
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12718255
:)
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12718277
To me the glass is always 1/2 empty rather than 1/2 full , because it makes to stand nearer the bar :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question