Sql server memory usage

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  

 
LVL 1
Tom_HickersonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ABaruhCommented:
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
Tom_HickersonAuthor Commented:
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
ABaruhCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

caball88Commented:
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
muzzy2003Commented:
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
SjoerdVerweijCommented:
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
ShogunWadeCommented:
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
caball88Commented:
4GB is a lot but it depends on what amount of data and number of users you are supporting.
0
ShogunWadeCommented:
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
SjoerdVerweijCommented:
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
caball88Commented:
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
Tom_HickersonAuthor Commented:
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
muzzy2003Commented:
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
ShogunWadeCommented:
" 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
caball88Commented:
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
ShogunWadeCommented:
"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
SjoerdVerweijCommented:
Max the SQL Server memory out at, oh, 256MB or so and see how it goes!
0
ShogunWadeCommented:
"256MB or so and see how it goes!"    or not as the case may be
0
SjoerdVerweijCommented:
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
ShogunWadeCommented:
:)
0
ShogunWadeCommented:
To me the glass is always 1/2 empty rather than 1/2 full , because it makes to stand nearer the bar :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.