July 1, 2010 at 4:29 am
Try giving permission of your SQL service startup Account in Lock pages in memory.
To find Lock Pages in Memory
goto cmd type ->secpol.msc
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.
Note: Commonly this is used for sql server memory paged out issues, but in ur case this might help u.
July 8, 2010 at 5:05 am
Yep,I too agree 2Gb is OK for OS.
July 8, 2010 at 9:15 am
Agreed with SSCoach. 10 GB and leaving 6 for the OS is risking your SQL Server to run out of memory at one point or another. Better setting 8 for SQL and leave 2 for the OS.
Now the memory setting as well as CPU depends in many things. There is one rule for all. What is the server for? Does the server has any other application installed? Is a dedicated data server? CPU? Is there only one instance in the server (with 10 GB I am assuming is not); all these items have an impact on how you set your server and how it will perform. Run sp_configure to check what is the server settings for SQL and go from there (remember to set advance options first to get all your server configuration)
Rule of thumb? I suggest you use something similar or close to what SSCoach mentioned. Good luck
:w00t: :hehe:
July 8, 2010 at 2:33 pm
Fernando-235287 (7/8/2010)
10 GB and leaving 6 for the OS is risking your SQL Server to run out of memory at one point or another.
Technically it won't run out of memory. Performance will most probably be worse due to less space for buffer and procedure cache (which is the most memory hungry "parts").
July 10, 2010 at 5:17 pm
The recommendation we received from a MSoft consultant recently was reserve 2Gb for the OS for each 16Gb of memory - if the server is dedicated. Just another guideline to add to the mix.
However remember that this only reserves the buffer pool, it's not a SQL Server max memory value. If you have many connections the figure you reserve for the buffer pool may need to be less.
Set a value and monitor paging, reduce until the paging stops.
pcd
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply