March 28, 2010 at 12:54 pm
Hi, I'm monitoring a sqlservr.exe that is using 46GB of memory out of 48GB on the server. There are two user databases on the server. Can I tell what specificly is using the memory? It has been nearly maxed out for several days, maybe longer. I was told that if you give the server 46GB, it will take it all regardless if it is needed. How can I investigate this situation?
Thanks much.
March 28, 2010 at 5:36 pm
Are you on x64 hardware with SQL Server 2008 x64 version? Did you set the max memory for SQL Server to 46GB?
If so, then yes - that is as expected. SQL Server will take all of the memory it needs up to the max memory setting and use it. If you only have 48GB of memory I would recommend dropping the max memory to no more than 44GB and probably down to 42GB.
If you have not set the max memory option, I would recommend setting.
Oh, also - if this server is not dedicated to SQL Server then you probably need to drop the max memory even more.
If you are worried about SQL Server taking all available memory, don't - that is the expected behavior and exactly what you want to happen. Reading data from memory is a lot less expensive than having to go to disk all the time - and that is why the memory is being used (for the most part).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 28, 2010 at 5:55 pm
Thanks for responding. Yes, it is 2008 Enterprise x64.
I've checked server properties and see that the maximum server memory is 44032.
I think that another admin rebooted the server or recycled the service because now the memory is use is much less. Does it gradually fill up to the max and then stay there?
March 28, 2010 at 5:59 pm
Yes, the system will continue to take more memory as needed up to the max memory setting. If SQL Server is taking more the 44GB of memory, then you have a problem - it should not with that setting.
If you are maxing out on memory usage for the machine - you have other processes taking up the additional memory and will need to reduce the max memory setting in SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 28, 2010 at 6:05 pm
It may have just been changed to 44GB. We were having issues earier so I'll check with the other DBA to see if he just reduced it to 44GB in order to give the server some breathing room.
Thanks again.
March 28, 2010 at 11:46 pm
Jeffrey Williams-493691 (3/28/2010)
Yes, the system will continue to take more memory as needed up to the max memory setting. If SQL Server is taking more the 44GB of memory, then you have a problem - it should not with that setting.
The max server memory setting only applies to the size of the buffer pool.
Since SQL Server also uses memory outside of the buffer pool, memory usage by the whole process may exceed that value.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 29, 2010 at 7:04 am
Paul White NZ (3/28/2010)
Jeffrey Williams-493691 (3/28/2010)
Yes, the system will continue to take more memory as needed up to the max memory setting. If SQL Server is taking more the 44GB of memory, then you have a problem - it should not with that setting.The max server memory setting only applies to the size of the buffer pool.
Since SQL Server also uses memory outside of the buffer pool, memory usage by the whole process may exceed that value.
Right - I should have clarified it more. However, it should not be significantly more than the max setting to the point where the system is running out of memory. Unless you have set the max memory too high and end up starving the OS.
With 48GB of memory - I would not recommend anything higher than 44GB for the max setting.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 29, 2010 at 7:29 am
44GB on a 48GB box is too high as well. Christian Bolton, in his book SQL Server 2008 Internals and Troubleshooting recommends this formula, which I agree with:
2GB for the OS
xGB for worker threads (2MB each on x64)
1GB for Multi-page allocations, linked servers etc
1-3GB for other applications
Worker threads vary from 512 for 4 CPU COREs or less up to 960 for 32 CPU COREs.
This is assuming just the relational engine is running on the box.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 29, 2010 at 7:45 am
TheSQLGuru (3/29/2010)
44GB on a 48GB box is too high as well. Christian Bolton, in his book SQL Server 2008 Internals and Troubleshooting recommends this formula, which I agree with:2GB for the OS
xGB for worker threads (2MB each on x64)
1GB for Multi-page allocations, linked servers etc
1-3GB for other applications
Worker threads vary from 512 for 4 CPU COREs or less up to 960 for 32 CPU COREs.
This is assuming just the relational engine is running on the box.
That is very similar to the formula I would use too. (Thread stack size is 512KB on x86).
If SQLCLR is in use to any great extent, I would allow much more for the MPA.
I am implicitly assuming that no-one in their right mind would make significant use of the SQLCLR on x86.
Probably the best advice is to start off way too low on the max setting, and monitor, increasing it up to a sensible ceiling if empirical data supports it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 29, 2010 at 9:07 am
This is great information. Thanks everyone.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply