May 13, 2010 at 12:41 pm
I have sql server 2005 sp3 on windows server 2003 sp2. I am curious if I have free memory available on OS level and I want to increase SQL max memory, Will it need a SQL recycle or SQL will start using increased memory immediately?
May 13, 2010 at 1:18 pm
If using a 64 bit operating system you will not, otherwise you will need to restart SQL Server.
May 13, 2010 at 1:55 pm
SQL 2005 and up memory settings are dynamic and do not need a recycle. Look up sp_configure in BOL to prove it to yourself.
---------------------------------------------------------------------
May 13, 2010 at 3:57 pm
BharatBhushan (5/13/2010)
I have sql server 2005 sp3 on windows server 2003 sp2. I am curious if I have free memory available on OS level and I want to increase SQL max memory, Will it need a SQL recycle or SQL will start using increased memory immediately?
how much RAM does the server have?
how many instances are there?
is it 32 or 64 bit?
what memory settings are you currently using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 13, 2010 at 8:13 pm
What OS are you using and is it Enterprise or Standard edition you are asking about. If you the OS is enterprise and the SQL as Standard edition then you will only see approx 3.5GB memory regarless what you have in the OS memory. So your adding to SQL could be mute. In a nut shell you dont have to cycle the services it will pick upon the run =]
-D-
DHeath
May 13, 2010 at 9:54 pm
What are you seeing in PerfMon?
SQL is memory hungry, SQL 2005 Standard Edition's memory usage is only limited by the OS.
On 64 bit servers if you are on SP3 and have applied the latest Hot Fix (possibly the latest General Distiribution Release - GDR) you should be able to set Lock Pages In Memeory. This is similar to the old AWE.
It's a good idea to set SQL's MAX Memory in such a way that the OS will ALWAYS have between 1GB and 1.5GB AT LEAST. If you have other apps on the server besides SQL you may need to give the OS a bit more.
Also set the Application Response to "Background Services" and the Memory Allocation to "Programs"
If you want more detailed help you need to supply more details
Leo
Trying to provide a better service 😀
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 14, 2010 at 11:19 am
Thanks guys for your responses. Here are the specs of the server. Its dedicated to just one instance of SQL server.
OS NameMicrosoft(R) Windows(R) Server 2003 Enterprise x64 Edition,
Version5.2.3790 Service Pack 2 Build 3790,Total Physical memory is 30 GB,
SQL Server 2005 SP3,Enterprise edition, AWE is enabled, Max allocated memory to SQL is 16 GB
I just want to configure SQL max memory to 20 GB. will it require SQL restart? and is there any way we can do it without sql restart.
May 14, 2010 at 12:19 pm
BharatBhushan (5/14/2010)
I just want to configure SQL max memory to 20 GB. will it require SQL restart? and is there any way we can do it without sql restart.
As I posted before, restart not required. did you check out BOL?
---------------------------------------------------------------------
May 14, 2010 at 12:23 pm
As George stated, there is no restart needed. To set max memory at 20GB:
EXEC sys.sp_configure N'max server memory (MB)', N'20480'
GO
RECONFIGURE WITH OVERRIDE
GO
Also AWE does not need to be enabled on 64 bit systems.
May 14, 2010 at 1:54 pm
George and Edogg, I really appreciate you guys for your help.. Thanks a lot
May 14, 2010 at 3:31 pm
Edogg (5/14/2010)
Also AWE does not need to be enabled on 64 bit systems.
No, but lock pages in memory probably should be!
If you start seeing the following in the SQL Server log
a significant part of the sql server process memory has been paged out...
You will likely need to assign the lock pages in memory policy to the SQL Server service account.
Check the following KB article for more info
http://support.microsoft.com/kb/918483
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply