Saturday, February 1, 2014

Best practice on sql server memory configuration

1. SQL Server Memory Configuration 
Configure the SQL Server “max server memory (MB)” setting by taking the amount of memory allocated to the database system and subtracting one GB for every four cores (round up). This leaves the operating system with enough memory to work efficiently without having to “grab” memory back from SQL Server. For example, if the server has 64 GB of RAM and 24 cores, set the maximum memory to 58 GB (64 GB minus 6 [24 cores divided by 4]).
2. Lock Pages in Memory
To reduce SQL Server paging, you can grant the SQL Server service account “Lock Pages in Memory” privilege through the Windows Group Policy editor.