Managing Memory & Processors within SQL Server
As I work my way through “Administrating Microsoft SQL Server 2012″ I thought it may be useful to make a note of transact statements used within the course as well as listing the key points and some useful links on the subject.
To modify the memory settings we can either browse to the server properties and select memory or we can use T-SQL.
Note that the minimum reserve will not apply if the minimum reserved memory is not used. Below is an example of Transact-SQL code that is use to configure the minimum and maximum memory settings:
EXEC sys.sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sys.sp_configure 'min server memory', 1024; GO EXEC sys.sp_configure 'max server memory', 4096; GO RECONFIGURE; GO
Configuring Processor and I/O Affinity
Processor affinity assigns specific server processors to specific threads eliminating processor reloads and reducing thread migration across processors.
To distribute SQL worker threads across CPUs we can use the following:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3
To distribute SQL worker threads across all CPUs based on server workload, use the following command:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO
I/O affinity binds an instance’s disk I/O to a specific set of CPUs.
There is a great video blog on this which warns about the dangers of modifying these settings.
It is recommended by brentozar.com that we never modify the following:
The affinity mask option can only be used on servers that have between 32 to 64 processes and should be avoided.
The fill factor is a useful tool to improve performance and it determines the percentage of space on each leaf-level is filled with data when an index is created or rebuilt. The default setting is 0 although 0 is the same as 100.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'fill factor', 90; GO RECONFIGURE; GO
Quick Review Questions
- To ensure that the default instance can use all processors available to the host, which of the commands would you use to accomplish this goal?
- What is the T-SQL commands to configure the instance so that the maximum amount of memory the instance uses does not exceed 4,096 GB?
- What do you need to configure if you wish to ensure that disk input/output operations of an instance are bound to a specific processor?
- You want to ensure that all future databases created on a SQL 2012 instance are configured to Auto Shrink. Which of the system databases do you modify to accomplish this goal?
KEY POINTS TO REMEMBER
I/O affinity enables you to bind disk input/output operations to a specific processor.
Related Posts via Categories
- Cycle SQL Server Error Logs
- Resource Governor Classification
- Resource Pools
- Workload Groups
- Configuring Resource Governor
- Deploying Software Updates and Patch Management
- Database Mail
- Database Configuration and Standardisation
- Performance Monitor
- Policy-Based Management