When you are using SQL2005, maybe you'll meet the SQL error log like is
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 921 seconds. Working set (KB): 2486348, committed (KB): 14694960, memory utilization: 16%%.
It may meet several times/ tens times each day and it lasts day by day.
SQL also write the error into event viewer as an information with the same information and ID is 17890.
As checked some document from MS. We can find the issue happened both SQL2005 32bit and 64bit version.
Here are some suggestions:
A, To assign the Lock pages in memory user right, follow these steps:
- 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.
B, Enable AWE for SQL.
1. Enable AWE on SQL
2. Set min/max server memory
Although 64bit SQL can hand all memory automatically, but actually, we still suggest we set up memory for SQL manually.
When you take B-2 item, you should consider your total memory and all application included multi SQL instance on your server.
Please allocate memory as your application and insure that no one application exhaust all memory.
A and B done, our 64bit SQL hasn't reported the issue again.
Of course, you can refer some document:
http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
http://support.microsoft.com/kb/918483
