April 21, 2010 at 11:47 am
I forget this.
There is a way to tell SQL server to use x amount of RAM ( instead of using all the RAM )
Can someone help me with what that command ( or syntax ) is please ?
April 21, 2010 at 11:50 am
EXEC master.dbo.sp_configure 'max server memory (MB)', xx
or from the GUI, right click on the server and goto the memory tab..
CEWII
April 21, 2010 at 12:50 pm
You can also control the min memory
EXEC master.dbo.sp_configure 'min server memory (MB)', xx
which may be beneficial if you want to keep the buffer pool and proc pool filled. If this matters then you should also configure the account the runs the SQL instance to have lock pages in memory in local policies.
April 21, 2010 at 1:43 pm
We just upgraded to 32 GB ( The max for the server ).
I beleive the operating system needs about 2 GB or RAM.
So how much would you recommend ( the best ) for Sql server ?
Can we allocate 30 GB of ram ?
Any suggestions please ?
April 21, 2010 at 1:49 pm
mw112009 (4/21/2010)
We just upgraded to 32 GB ( The max for the server ).I beleive the operating system needs about 2 GB or RAM.
So how much would you recommend ( the best ) for Sql server ?
Can we allocate 30 GB of ram ?
Any suggestions please ?
Set a fixed amount of memory for 29 or 30 GB
If it's a 32 bit OS or SQL Version, enable AWE then /PAE in the boot.ini file
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 21, 2010 at 2:03 pm
Not sure what you mean by the following:
If it's a 32 bit OS or SQL Version, enable AWE then /PAE in the boot.ini file
April 21, 2010 at 2:06 pm
Is your O/S and SQL on 64 bit or 32 bit platform? What is the result of the following statement?
Select @@version
April 21, 2010 at 2:07 pm
What version of windows are you running 32 or 64 bit?
If your running 64 bit windows, then what version of sql are you running 32 or 64 bit?
If you are running 32 bit SQL, then you have to make extra changes so that SQL can see and use memory above 2GB
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 22, 2010 at 6:55 am
Here is the reply to your questions
1.) Our Server is a 64 bit OS
2.) This is what I get when I run the Select @@version command
Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
April 22, 2010 at 8:00 am
I would recommend setting min/max memory to 28GB if you have 2 cores. If you have one then I would set it to 30GB. I would also configure lock pages in memory:
Click Start, click Run, type gpedit.msc, and then click OK.
Note The Group Policy dialog box appears.
Expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Click User Rights Assignment, and then double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, click Add User or Group.
In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.
Close the Group Policy dialog box.
Restart the SQL Server service.
April 22, 2010 at 8:04 am
Can you explan further what you plan to do by "lock pages in memory".
What is the significance of adding an account and restarting sql server service
April 22, 2010 at 8:45 am
You are not adding and account, but rather granting the account that already runs SQL server the right to "lock pages in memory." The reason this is important is that without it the O/S can trim memory from SQL Server when it feels like it. In other words the memory that SQL Server is using can be canabalized by the O/S. For the article that goes more in depth about this please refer to:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply