April 2, 2014 at 10:33 am
Been monitoring SQL server 2008 for a bit now and seems the memory usages sits between 95-95% memory usage. After reading multiple forums and recommendations from various sources, I have set the max memory to 13.5GB while I have 16GB of physical memory.
Used memory for sqlservr.exe is sitting at over 14.2 GB. How long does it normally take to start seeing the memory tick down to the max configured memory for the server?
April 2, 2014 at 11:02 am
It depends. SQL might not give up any memory until the OS requests it, if you have lock pages in memory set SQL won't give it up.
You may have to restart SQL if it never comes down, but if there is no sign of memory pressure no need to hurry it.
---------------------------------------------------------------------
April 2, 2014 at 11:04 am
Just remember, SQL Server will always use every bit of memory that let it have. It's very normal and by design to see all the memory in use. You've done the right thing in retaining some memory for the OS. You should see the memory used change shortly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2014 at 11:06 am
Depends on the queries that are running, jobs, etc. Sometimes it quick, sometimes it takes a long time.
April 2, 2014 at 11:52 am
thanks for the replies. Memory usage ticked down 2% but still relatively high. I am continuing to monitor.
"Lock pages in memory"
What is the recommended setting for this? Any suggestions? Does this require a restart of the services. I will google on where this setting is located.
April 2, 2014 at 12:51 pm
yes, it requires a SQL (not server) restart to enable it. If this is 64bit probably not set or required but it can be, especially if you see messages about SQL working set being trimmed in the SQL errorlog.
Its set in local security settings (secpol.msc) - local policies - user rights assignments. Assign it to the SQL service account.
NOTE: I am not recommending you set it, just that if you wanted SQL to give up memory, this setting it would stop it.
some light reading -
http://bradmcgehee.com/2011/03/10/do-you-enable-lock-pages-in-memory/
---------------------------------------------------------------------
April 3, 2014 at 3:30 am
Think it worth sayingthat, as you haven't mentioned what SQL version you are using there are some caveats with Lock Pages in Memory (LPM).
First, if you're running SQL 2008 Enterprise then, setting LPM in the Local Sec Policy and re-starting SQL Service is fine, but if you're using Standard Ed, then you need to be on at least SQL Server 2008 Cumulative Update 2 for Service Pack 1 and you will need to set the Trace Flag T845 in the startup parameters to take advantage of LPM.
Also, LPM will give up memory to the OS if asked, but it will do it much more slowly. This great article by Slava Oks explains this very well and though the artile is old, I believe it is still very much relevant today. There is also a good discussion on memory allocation and LPM in this article - note Jonathan Kehayias also confirms that SQL with LPM enabled will release memory back to the OS if the OS is under pressure memory-wise.
HTH
April 3, 2014 at 3:36 am
phil.wong (4/2/2014)
Been monitoring SQL server 2008 for a bit now and seems the memory usages sits between 95-95% memory usage. After reading multiple forums and recommendations from various sources, I have set the max memory to 13.5GB while I have 16GB of physical memory.Used memory for sqlservr.exe is sitting at over 14.2 GB. How long does it normally take to start seeing the memory tick down to the max configured memory for the server?
Max Memory is for the Buffer pool only. It will use more than that for other parts of the process so 14.2GB with a Max Memory setting of 14.2 seems acceptable.
EDIT:
These are the kind of tasks that are not included in the Bufferpool
COM Objects
Linked Server OLEDB Providers
Extended Stored Procedures
Threadstack
Memory consumed by memory clerks if memory request >8K SQL CLR DB Mail
http://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/[/url]
April 3, 2014 at 4:09 am
humbleDBA (4/3/2014)
Think it worth sayingthat, as you haven't mentioned what SQL version you are using there are some caveats with Lock Pages in Memory (LPM).First, if you're running SQL 2008 Enterprise then, setting LPM in the Local Sec Policy and re-starting SQL Service is fine, but if you're using Standard Ed, then you need to be on at least SQL Server 2008 Cumulative Update 2 for Service Pack 1 and you will need to set the Trace Flag T845 in the startup parameters to take advantage of LPM.
Also, LPM will give up memory to the OS if asked, but it will do it much more slowly. This great article by Slava Oks explains this very well and though the artile is old, I believe it is still very much relevant today. There is also a good discussion on memory allocation and LPM in this article - note Jonathan Kehayias also confirms that SQL with LPM enabled will release memory back to the OS if the OS is under pressure memory-wise.
HTH
Also, with LPM enabled you can no longer rely on basic monitoring to determine how much memory is used. For example, task Manager only displays memory allocated through the VirtualAlloc() api call.
Enabling LPM SQL Server will use the AllocateUserPhysicalPages() API call for the bufferpool and you need to use the SQLServer:Memory Manager/Total Server Memory (KB) performance counter to get an accurate figure.
April 3, 2014 at 6:10 am
We have decided to change one variable at a time and see how the changes look on the server and monitor if it impacts performance on both the SQL side or the OS side. This is a pair of servers with identical configurations running SQL Server 2008 R2 with 16 GB of physical memory. After monitoring the memory usage on the server and memory was still sitting well over the threshold of 14.2 GB the decision was made to restart the services in the off hours. Once restarted memory usage seems well within range. Returning in the AM to review one server is under the set max but one server is sitting back above 14.5 GB of memory used.
April 3, 2014 at 6:17 am
phil.wong (4/3/2014)
Returning in the AM to review one server is under the set max but one server is sitting back above 14.5 GB of memory used.
Like I said it is expected behavior.
April 3, 2014 at 10:04 am
phil.wong (4/3/2014)
We have decided to change one variable at a time and see how the changes look on the server and monitor if it impacts performance on both the SQL side or the OS side. This is a pair of servers with identical configurations running SQL Server 2008 R2 with 16 GB of physical memory. After monitoring the memory usage on the server and memory was still sitting well over the threshold of 14.2 GB the decision was made to restart the services in the off hours. Once restarted memory usage seems well within range. Returning in the AM to review one server is under the set max but one server is sitting back above 14.5 GB of memory used.
Restarting SQL Server to release memory gives you only a temporary relief. But if SQL Server is in need of that much amount of memory, it will again reach to that level. Check if the server is experiencing any memory pressures and do adjustments accordingly.
--
SQLBuddy
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply