It's necessary to keep SQL from paging to disk to reduce physical I/O. One way is to keep the SQL working set in Memory. The steps below will set you up for this. Before performing this, ensure that your SQL Server has enough ram to handle your current production load. You wouldn't want your primary indexes dropping out of memory to replace 1 off Jobs.
I generally deploy new servers with Lock Pages in Memory set for the SQL Server Service start-up account .
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.