SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Server is using almost all Memory problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 12/06/2012 :  13:43:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/06/2012 :  13:53:00  Show Profile  Reply with Quote
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

USA
132 Posts

Posted - 12/06/2012 :  14:53:25  Show Profile  Reply with Quote
Thanks sunitabeck I'll try that.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/06/2012 :  15:36:27  Show Profile  Reply with Quote
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

USA
132 Posts

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

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/06/2012 :  16:26:51  Show Profile  Reply with Quote
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

USA
132 Posts

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

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/06/2012 :  16:29:07  Show Profile  Reply with Quote
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

USA
36797 Posts

Posted - 12/06/2012 :  16:42:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
132 Posts

Posted - 12/06/2012 :  16:44:04  Show Profile  Reply with Quote
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

USA
132 Posts

Posted - 12/06/2012 :  17:48:34  Show Profile  Reply with Quote
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 - 12/06/2012 :  19:02:14  Show Profile  Reply with Quote
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

USA
36797 Posts

Posted - 12/06/2012 :  19:51:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
132 Posts

Posted - 12/07/2012 :  08:59:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/07/2012 :  09:17:44  Show Profile  Reply with Quote
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

United Kingdom
530 Posts

Posted - 12/07/2012 :  09:28:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/07/2012 :  10:24:52  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000