Microsoft SQL Server Best Practices - Part 2
Part 2 – Memory Best Practices
Minimum and Maximum Server Memory
These two configuration options control how the SQL Memory Manager handles memory growth and reduction for a SQL Server process used by an instance of SQL Server. This is specifically the amount of memory allocated to “the buffer pool and other caches of the Database Engine.” -1 It is important to note that a SQL Server process will allocate memory outside of the minimum and maximum server memory settings for a variety of other operations such as backups, thread stack memory and database mail. When a SQL Server process starts it allocates only the memory it needs to start. As queries are run and work is performed, memory in the Buffer Pool is allocated and utilized. The Buffer Pool will grow until it reaches the value for Maximum Server Memory and the SQL dynamic memory management will free up memory in the Buffer Pool as needed once the Minimum Server Memory is reached.
There is way more to memory utilization in SQL Server than this simplified explanation, but the Microsoft best practice is to set the Maximum Server Memory to a value that leaves 25% of OS Memory and “the equivalent of potential SQL Server memory allocations outside the max server memory control, which is comprised of stack size * calculated max worker threads.” -2 The Microsoft recommendation for Minimum Server Memory is “setting a min_server_memory value is essential in a virtualized environment to ensure memory pressure from the underlying host does not attempt to deallocate memory from the buffer pool on a guest SQL Server virtual machine (VM) beyond what is needed for acceptable performance.” -2
Let's try and simplify this. My personal best practice is to leave the Minimum Server Memory at the default setting of 0, even in a virtual environment. The risk pointed out by Microsoft occurs when memory on a virtual host is over-committed to virtual machines. For example, the virtual host has 512 GB of RAM but 756 GB of RAM has been assigned to virtual machines running on the host. If the host is deallocating memory from virtual machines you will have bizarre issues with the host and the virtual machines on the host. It is my personal best practice to never assign more RAM to VMs than the host has and also to reserve 4 GB of RAM for host operations. This is an extreme stance in the virtualization world but is a solid best practice in my humble opinion because nothing causes issues on a virtual host faster than running out of free memory.
My personal best practice for Maximum Server Memory is to start by setting it to 70% of installed OS memory. For example, if the OS has 256 GB of RAM, then I would set Maximum Server Memory to 179 GB to start. I would then monitor the performance counter “Memory - Available Mbytes” over time and if normal operations are not utilizing all but 2 GB of RAM, I would increase the Maximum Server Memory setting periodically. For example, if the performance counter “Memory – Available Mbytes” never drops below 58 GB after 90 days of server operations then I would adjust Maximum Server Memory to?235 GB (179 GB + 56 GB). Keep in mind that these best practices do not take multiple instances running on a single server into consideration. They also do not take into consideration running other applications on the SQL Server which is not a recommended practice.
Lock Pages in Memory
The Lock Pages in Memory setting changes buffer pool memory allocation to use AWE allocation instead of the default SQL Virtual memory allocation. AWE memory is not allowed to be paged to disk/the OS page file. This will keep SQL from paging SQL buffer pool memory out to the OS page file due to OS memory pressure issues but will not stop SQL from performing voluntary memory paging operations in which modified database pages in memory are written back out to their respective database file.?
The benefit of using Lock Pages in Memory is to keep SQL performance from being affected by OS memory pressure and paging out large portions of the buffer pool to disk. The negative affect of using Lock Pages in Memory is that OS memory pressure issues can go unnoticed and unresolved due to their seeming to be no problems occurring in SQL.
My personal best practice is to enable Lock Pages in Memory for SQL. In my experience, when administering dozens of SQL servers and instances, operations will be more stable with this setting enabled as OS memory pressure issues will not cause SQL to have issues. There is a very important caveat to following this best practice and that is the performance counter “Memory – Available Mbytes” must be set up for monitoring and alerting in case it drops below 2 GB of free RAM on a server. Adjusting the Maximum Server Memory setting in SQL does not require a restart and takes effect immediately. If you receive an alert that you have dropped below 2 GB of free RAM on a server, you can adjust the setting down and free up memory to the OS immediately. This can even be automated if need be. You will need to review the server to determine why memory usage increased and if a permanent change to the setting is required.
Optimize for Ad Hoc Workloads
On the SQL Server, the optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small, compiled plan stub in the plan cache when a batch is compiled for the first time. (This step is performed instead of the full compiled plan.) It helps to relieve memory pressure by not allowing the plan cache to be filled with compiled plans that are not reused. The compiled plan stub allows the Database Engine to recognize that this ad hoc batch has been previously compiled but has only stored a compiled plan stub. When this batch is invoked (compiled or executed) again, the Database Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache.
If your SQL database is utilized by end users that frequently create custom queries it is a Microsoft best practice to enable the option to Optimize for Ad Hoc Workloads.
?
领英推荐
Max Worker Threads
“The max worker threads option configures the number of worker threads that are available SQL Server-wide to process query requests, login, logout, and similar application requests. The default value for max worker threads is 0. This enables SQL Server to automatically configure the number of worker threads at startup. The default setting is best for most systems.” -3
This is an advanced feature and best practice is to leave it at the default setting of 0.
Index Create Memory
“The index create memory option controls the maximum amount of memory initially allocated for sort operations when creating indexes. The default value for this option is 0 (self-configuring). If more memory is later needed for index creation and the memory is available, the server will use it; thereby, exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.” -4
This is an advanced feature and best practice is to leave it at the default setting of 0.
Min Memory Per Query
“The min memory per query option specifies the minimum amount of memory (in kilobytes) that will be allocated for the execution of a query. This is also known as the minimum memory grant. For example, if min memory per query is set to 2,048 KB, the query is guaranteed to get at least that much total memory. The default value is 1,024 KB. The minimum value 512 KB, and the maximum is 2,147,483,647 KB (2 GB). The amount of min memory per query has precedence over the index create memory option. If you modify both options and the index create memory is less than min memory per query, you receive a warning message, but the value is set. During query execution, you receive another similar warning.” -5
This is an advanced feature and best practice is to leave it at the default setting of 1,024 KB.
?
References: