Improving SQL Server 2008/R2 Performance
Set SQL Server MAX Memory
By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 - 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
You can establish upper and lower limits to the amount of memory (buffer pool) used by the SQL Server database engine with the min server memory and max server memory configuration options.
- Connect to the SQL Server Database engine
- GO to the SQL Server Properties
- Select Memory Tab & Set Maximum serve memory as per Server Load/Usage & click OK.
- Restart the SQL Server to apply the above settings.
Set SQL Server MAX Degree of Parallelism
You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors.
- Connect to the SQL Server Database engine
- GO to the SQL Server Properties
- Select Advanced Tab & Set the Max Degree of Parallelism. In my case its 1, so it will force each query to execute against one CPU core at a time.
- Restart SQL Server to apply the above settings.
