May 26, 2009 at 7:32 am
i have a server 2003 with 4.00GBs of RAM, 2.8 GB GHZ, i only have one instance in that server.
this is what sp_configure shows:
name minmaxconf_valuerun_value
max server memory (MB) 16214748364721474836472147483647
min memory per query (KB) 512214748364710241024
min server memory (MB) 0214748364700
i always have timeouts in the server, and when i go check the taskmanager is see that the
the sqlserver service consuming is consuming 3.5GB of memory, i restart the service, everything is good for a couple of minutes then again goes to 3.5 GBs, is this normal? Should i change the min and max of memory used?
May 26, 2009 at 9:14 am
You need to set the max server memory to cap the usage off at a certain point.
SQL will suck up as much as it feels it needs if you don't do so.
May 26, 2009 at 9:21 am
set up the max server memory
sp_configure 'max server memory(MB)',3000
reconfigure with override
May 26, 2009 at 9:54 am
thank you!, question, so how i had it before it was using all the memory all the time? why is this bad?, and now the max will be 3 GBs out of the 4 GBs? also, should'nt the sql server only use about 2 GBS of memory, why does it spike up to 3.5? , is there anything else i should check,please advise 🙂
i also get this error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'max server memory(MB)' does not exist, or it may be an advanced option.
May 26, 2009 at 12:46 pm
Why is using all of the memory on a system bad? Well, you could answer that yourself couldn't you? 😉 You have to restart the system all the time because it becomes slow.
The real question is why does this happen? It happens because the OS needs memory to manage the system and other processes. SQL Server is taking that memory and the OS then has to page out to disk, causing the system to get slower and slower. By limiting SQL Server, you are making sure the OS has enough resources to do its job.
The error you are getting is because you have not enabled advanced options. Issue the following:
sp_configure 'Advanced Options', 1; Reconfigure with override; -- enable advanced options
Then you can issue just 'sp_configure' and it will list all of the available options.
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
May 26, 2009 at 1:37 pm
thank you again, i am executing this:
sp_configure 'Advanced Options', 1; Reconfigure with override
i get:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
then i exec this:
sp_configure 'max server memory(MB)',3000
and i get the same error...
May 26, 2009 at 1:45 pm
What is the output of SELECT @@VERSION?
I am assuming that you are on a 64bit SQL Server based on the amount of memory being used by the process. In that case, setting max server memory is recommended by Microsoft so that you leave at least 100MB of available memory at all time. You have to monitor your performance counters to find the sweet spot for your server, but generally it is between 2.5-3GB.
The reason your server slows is because as it chews up memory, it begins to page the SQL Server process into virtual memory, and eventually, you end up in a constantly paging system as it trys to swap physical memory into virtual memory so it can read virtual memory back into physical memory. This is a known issue on 64 bit servers without max server memory set. If you are on the Enterprise SKU, you can set Lock Pages in Memory to prevent having the SQL working set trimmed/paged like this, but that doesn't eliminate the need to have your max memory set appropriately to prevent starvation of the OS. If you are on Standard Edition, you can apply the latest Cumulative Update which includes a Trace Flag that can be set to Lock Pages in Memory.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
May 26, 2009 at 1:46 pm
Execute just sp_configure. Scroll down in the results and find the setting for max server memory. Copy that entry into your command, which will be:
sp_configure 'max server memory (MB)', 3000;
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
May 26, 2009 at 1:56 pm
thank you it worked :), this is what i have now :
max server memory (MB)16214748364730002147483647
Jonathan regarding
What is the output of SELECT @@VERSION?
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
May 26, 2009 at 2:04 pm
Glad we could help and you were able to get it set. Jonathan was spot on with regards to x64 - and the requirement to set the max memory.
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
May 26, 2009 at 2:29 pm
thank you but in task manager i still see:
sqlsvr.exe SYSTEM 00 3,490,568K , do i need to restart services?
May 26, 2009 at 2:34 pm
As has been noted in various articles and blogs - SQL Server does not like to give up memory once it has it. With this change, what will happen is when SQL Server does give up memory it won't get it back, but there is nothing that will force SQL Server to give up that memory right away.
If you are experience performance issues - I would schedule a restart of SQL Server. You don't have to restart the machine, just cycle SQL Server and SQL Server Agent (and any other dependent services).
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
May 26, 2009 at 2:46 pm
thanks, i restarted the services, sqlsvr was using 2.2 gbs for a while but now i checked and is using 3.2 gbs of memory how is this possible? does sqlsvr usually take a little bit more that specifiied in the max memory? i am looking in task manager.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply