Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Server is using almost all Memory problem

Author  Topic 

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 13:43:54
Hi guys.

We have 15.7 GB installed on the server. The memory usage is going up and up and up throughout the day. We're currently at 15.2 used according to Windows Task Mangager. Are there any commands that i can issue that will lower this memory consumption and make us feel better?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 13:53:00
In SSMS object explorer, right click on the server name, properties, memory and set maximum memory to a lower value. In your case assuming there are no other applications running on the server, about 12000 MB would probably be appropriate.
Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 14:53:25
Thanks sunitabeck I'll try that.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-06 : 15:36:27
Is that total memory? What Edition of SQL Server and Windows Server? Is it 64-bit?
Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 15:55:11
Enterprise edition 64 bit, OS Microsoft Windows Win NT 6.1(7601), Memory 15868 MB, Processors 4.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-06 : 16:26:51
quote:
Originally posted by sunitabeck

In SSMS object explorer, right click on the server name, properties, memory and set maximum memory to a lower value. In your case assuming there are no other applications running on the server, about 12000 MB would probably be appropriate.




We shouldn't change directly like this in Production Server. Analysis as to what version/edition is required. How much is set for OS?
Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 16:29:02
Ok thanks. I'll get back with the OS question soon.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-06 : 16:29:07
quote:
Originally posted by viperbyte

Enterprise edition 64 bit, OS Microsoft Windows Win NT 6.1(7601), Memory 15868 MB, Processors 4.



Ok.3GB for OS should be ok. Can you check Total Server Memory in Perfmon to see what is actually used by SQL.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-06 : 16:42:58
quote:
Originally posted by viperbyte

Hi guys.

We have 15.7 GB installed on the server. The memory usage is going up and up and up throughout the day. We're currently at 15.2 used according to Windows Task Mangager. Are there any commands that i can issue that will lower this memory consumption and make us feel better?



This is completely normal. Set a high upper limit for SQL Server as mentioned previously and that's it. SQL Server will consume this entire amount eventually. This is normal.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 16:44:04
15.5 GB memory installed on Windows Server 2008 R2 64 bit. I'll have to try to get this perfmon thing to work.
Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-06 : 17:48:34
Still can't get Perfmon to work. I'll give it a rest for now and try to focus on wait stats. My boss hit the panich button and restarted the Server. That made the the memory consumption look good but it's slowly rising and I have a feeling it will be way up there again sometime tommorow.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-12-06 : 19:02:14
I don't understand why you think this is bad. If I had a database using half the memory and/or half the processors, I'd be assuming I'd over speced the machine and wasted money!
What else were you hoping to do with the memory?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-06 : 19:51:46
quote:
Originally posted by viperbyte

Still can't get Perfmon to work. I'll give it a rest for now and try to focus on wait stats. My boss hit the panich button and restarted the Server. That made the the memory consumption look good but it's slowly rising and I have a feeling it will be way up there again sometime tommorow.



Again, this is completely normal. There is nothing to fix here.

What issue are you having that you are trying to resolve?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2012-12-07 : 08:59:38
Good morning. Thanks all for the feedback. Now after being educated by all of you I understand that it is normal for most of the memory to be used up by SQL Server. I'm not trying to resolve an issue, it's just that I'm the new guy here and new at this position and my boss is the one who gets worried when he sees that almost all of the memory being is used up and involves me in his concern, even though I pointed out to him an article on the internet that backs up what is being said here about the subject. I understand that you can't totaly believe everything you read on the internet and must be sure that your resources are creditable. So that may be why he doesn't just believe in what is being said. I guess I need some kind of MicroSoft white paper or something that backs this up to show to him so he'll stop worring about it.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-07 : 09:17:44
This page and links on that page may be a good start:

http://msdn.microsoft.com/en-us/library/ms177455(v=sql.105).aspx
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts

Posted - 2012-12-07 : 09:28:18
do you have any other applications working on the same server, if so thats not good practice.

But you shouldn't worry about sql server taking memory its well knows to gobble as much memory as you'll throw at it,but I do tend to set the max memory restriction to about 1 gig below so the server doesn't shut down or freeze up causing users to be locked and managers to panic and restart servers when its not necessary.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-07 : 10:24:52
I am probably a little bit more conservative than Neil. On a 64-bit system and server, I would suggest about 3-5 G below the installed memory. So on a 16GB box, set it to 12 or so.

The rationale is as follows:

The max limit that you set is for the buffer pool. All the other things - worker threads, backups, extended stored procs etc. are outside of that.You can do precise calculations on how many max worker threads you may have and how much memory they will consume etc. (max_workers_count from sys.dm_os_sys_info, each needs 2MB), but 2-3GB is a good rule.

Then, I want to give about 2GB for Windows OS.

So assuming it is a dedicated SQL Server box, 4-5 Gigs below the installed memory. If it is a 32 or 64 GB box, I would leave 5 Gigs, if it is 16 GB box, leave about 4GB. You could leave a lower amount, if you are monitoring the performance diligently.
Go to Top of Page
   

- Advertisement -