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. |
|
|
viperbyte
Posting Yak Master
132 Posts |
Posted - 2012-12-06 : 14:53:25
|
Thanks sunitabeck I'll try that. |
|
|
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? |
|
|
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. |
|
|
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? |
|
|
viperbyte
Posting Yak Master
132 Posts |
Posted - 2012-12-06 : 16:29:02
|
Ok thanks. I'll get back with the OS question soon. |
|
|
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. |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
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. |
|
|
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. |
|
|
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? |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
|