October 21, 2008 at 9:20 am
Afternoon,
First post on here so apologies in advance if I missed any kind of 'sticky' to other topics that could answer my question!
I've recently joined the company I work at now and have found on the Production Server environment that the amount of RAM available on the server and the amount being used by the sqlservr.exe process is remarkably high (I've already read that once the SQL Server uses more than 2GB of physical memory it is bundled into one process titled 'sqlserver.exe')
The server is of the following specification:
Microsoft Windows Server 2003R2
Enterprise x64 Edition
Service Pack 2
Dell Server PE2950
Intel(R) Xeon CPU
E5420 @ 2.50GHZ
15.9GB of Ram
Version of SQL2005 used:
Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
As I understand it 64 bit systems actually allow SQL to allocate as much memory as it wants to the maximum of the Server 'max server memory (MB)' setting, correct? What I am worried about is that the total amount of physical memory for the server is 15.9GB and as the setting for the max memory has not yet been defined (its still at the crazily high value by default prior to my arrival)SQL is constantly hovering around the 14.8/9GB and leaving us around ~200MB free when all other processes are considered.
What I am wondering is:
- Should the setting for max memory for SQL be changed to a value around 14GB to allow more room for other applications (including the OS) and to avoid the server stalling after reaching max amount of RAM?
- If this setting is changed will it mean more data is paged out and consequently slow down any changes/access to the data/databases? (imagine this is yes) and therefore applications accessing this data?
- Am I worrying over nothing? Performance is generally very good and although the amount of DBs we currently have on the Production server is very high (200+) they appear to be performing very well (because they are all using the Physical RAM and because requests are not paged out?)
I understand that the amount of info is here probably minimal and I have attempted to use Perf mon but I am not an expert in this area and as such the outputs of it are a little hard to follow. So if additional information is needed from there or other sources (connections etc) please say.
Thanks in advance,
Dan Bridgman
October 21, 2008 at 9:28 am
Limiting the Max-Memory for SQL Server to physical memory of the box minus 1.7 GB has proven
quite a good value whenever there are other apps running on the same box
(like IIS, some virusprotection, some hardware inventory software)
Especially when one or two admins tend to open remote desktops on the box.
devloping robust and performant databaseapplications with Microsoft SQL-Server
October 21, 2008 at 10:04 am
Age2uN (10/21/2008)
Limiting the Max-Memory for SQL Server to physical memory of the box minus 1.7 GB has provenquite a good value whenever there are other apps running on the same box
(like IIS, some virusprotection, some hardware inventory software)
Especially when one or two admins tend to open remote desktops on the box.
Thanks for the speedy response.
I can see how this would help, fortunately, however no other applications beside Redgate SQL Response1 and other associated SQL Processes reside on the server so this is not really an issue I think.
What I was concerned about is if I should restrict the amount of RAM allowed for SQL to stop it from suffocating the OS or other SQL Processes when it needs more memory (when heavy duty scheduled jobs are running for example)
October 23, 2008 at 8:29 am
Anyone else able to help with this?
Thanks
October 23, 2008 at 8:46 am
It seems like the questions were answered so, a bit confused. However, setting the max server memory to be 14 GB should be good and allow plenty for the OS and the other processes. Could this impact the instance performance and increase paging? Possibly but most likely not if you are not seeing it already I doubt that this subtil reduction will cause it. If it does then find that sweet spot and go from there. It may mean more memory in the long run but I doubt it.
Are you worrying over nothing? 🙂 Doubt it but definitely worth keeping your eye on after you make the change.
If there is another question that was missed please post it as I didn't see anything further.
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 23, 2008 at 8:52 am
I presume your memory allocation is set to "Dynamic".
If the box is dedicated to SQL Server, then don't worry about it.
With memory allocation set to "Dynamic", if the OS needs more memory, SQL Server will give it back. If more OS memory becomes available, then SQL Server will grab it back.
SQL Server will leave a certain amount of free memory, so if the OS needs more, it's available, without the OS having to wait for SQL Server to give it back.
October 23, 2008 at 9:58 am
Thanks David & Ian. Yes, the question was mostly answered but you have both given me peace of mind in what you have said.
Yes, the memory is set to dynamic management, however during busy periods it still hits almost 100% of the available RAM and can cause the server to stop responding. I think after reading the posts I am going to lower the max setting little by little (15GB) and see how affects the performance in general, and if it does I can always change back again!
Thanks again
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply