august 14, 2024
Optimising RAM usage in SQL Server
Optimising the use of RAM in SQL Server is a key aspect of ensuring high database performance.
Incorrect memory configuration can lead to serious performance issues that negatively impact the end-user experience. Here’s how it can manifest:
- Slower query response times
- Increased wait times
- Reduced overall system performance
- Higher likelihood of failures
SQL Server uses various mechanisms to manage memory usage, such as page buffers, query cache, and other components. In this article, we will look at the main approaches to optimising RAM usage in SQL Server.
Page buffers
Page buffers are one of the primary mechanisms by which SQL Server manages memory usage. They are memory areas where recently read or written database data and objects are stored. The main goal of page buffers is to minimize the number of disk accesses, significantly increasing query processing speed. Let’s look at how page buffers work and what «buffer cache hit ratio» means.
How page buffers work
- Data storage: Data and index pages are read from the disk into SQL Server’s buffer pool. Modified pages (known as «dirty pages»), which have been changed since they were last saved to disk, are also stored in the buffer before being written back to the disk.
- Memory management: The buffer pool serves as the main memory allocation source for SQL Server. Buffer management involves two key mechanisms: the buffer manager, which accesses and updates database pages, and the buffer pool itself, which reduces database I/O operations.
- Processing «dirty» pages: When the system experiences memory pressure, active «dirty» pages are flushed from the buffer cache, written to disk, and then read back into the cache. This helps manage memory usage and prevents buffer overflow.
Optimising buffer cache hit ratio
«Buffer cache hit ratio» is a metric that indicates the efficiency of page buffer usage. This metric is expressed as a percentage and ideally should approach 100%, which indicates that almost all data accesses occur from the buffer, minimizing the need for physical reads from the disk.
To maximize the «buffer cache hit ratio», you can take the following measures:
1. Proper server memory configuration: Ensure that SQL Server has enough memory to effectively use page buffers. The «maximum server memory» (in MB) parameter in SQL Server Management Studio allows you to set the maximum amount of memory SQL Server can use. To do this, follow these steps:
- Launch «SQL Server Management Studio».
- Right-click on the database server.
- In the opened menu, select «Properties».
- In the «Server Properties» window, select the «Memory» page (1).
- In the «Maximum server memory» (in MB) field, enter the desired amount of memory in megabytes (2).
- Click the «OK» button (3).
2. Updating statistics: regularly updating data statistics helps SQL Server make more informed decisions about which data to load into the buffer. This is especially important for large databases where data changes frequently.
To update statistics in SQL Server, use the UPDATE STATISTICS command. This command updates statistical data for the specified database object.
Example of updating statistics for the Sales table:
If you want to update statistics for all objects in a specific schema, use the keyword ALL:
3. Query optimisation: avoid unnecessary scans and use indexes wherever possible. This helps reduce the amount of data that needs to be loaded into the buffer, increasing the likelihood of a «cache hit»—when the system or application successfully retrieves data from the memory cache rather than the main data storage.
Suppose we have a table Employees with millions of rows, and we want to find all employees older than 30 years. If we don’t have an index on the Age column, SQL Server will perform a full table scan, which is highly inefficient for large datasets.
To optimise this query, we can add an index on the Age column, allowing SQL Server to quickly find the relevant rows without scanning the entire table.
Memory grants
What are “Memory Grants” and “Memory Grants Pending”?
In the context of SQL Server, «memory grants» refer to the amount of memory that SQL Server allocates to execute individual query operations. For example, a sort operation might require a certain amount of memory to temporarily store data, while a hash operation might require a different amount of memory. These memory grants are typically allocated from the overall pool of memory available to SQL Server.
The «Memory Grants Pending» metric in SQL Server refers to the situation where requests for memory to perform operations are waiting for memory allocation. The optimal value for this metric should be 0. This condition can occur when the system reaches its maximum memory usage level, and new queries cannot obtain the required amount of memory for their execution until enough memory is freed up.
Causes of "Memory Grants Pending"
- High system load: If numerous queries simultaneously attempt to use memory and the total available memory is limited, some queries will wait until enough memory is freed up.
- Improper memory configuration: If SQL Server’s memory settings are misconfigured, such as having a too-low memory limit (max server memory), this can frequently lead to a «Memory Grants Pending» state.
You can monitor «Memory Grants Pending» using Performance Monitor:
- Click on «Performance Monitor» (1).
- Click the «Add» button (2).
- Find and select «Memory Manager» (3).
- Choose «Memory Grants Pending» (4) from the list and click «OK» (5).
The metric will be reflected in the monitoring window.
Using Hints. Option (Recompile)
What are Hints?
«Hints» in MS SQL Server are used to provide additional instructions to SQL Server on how a query should be executed. They allow developers and database administrators to fine-tune SQL Server’s behavior, optimizing query performance and managing system resources.
- The use of hints should be justified: indiscriminate use of hints can lead to unexpected results and degraded performance.
- Testing: Before deploying hints in a production environment, always test them in a controlled environment to ensure they achieve the desired outcomes.
Option (Recompile)
The «Option(Recompile)» hint instructs SQL Server to recompile the execution plan for each execution of the query, instead of using the saved execution plan from the plan cache. This hint is used in the following situations:
- Dynamic queries: In cases of dynamic SQL or queries whose conditions can vary significantly, OPTION (RECOMPILE) can help ensure optimal memory usage.
- Handling large data volumes: When queries process large volumes of data and statistics can change rapidly, using OPTION (RECOMPILE) can help avoid the negative effects of outdated statistics.
You can specify «Option(Recompile)» when creating a stored procedure:
You can also use the hint when calling a stored procedure:
Using «Option(Recompile)» can improve memory usage up to 20 times. Below are the results of executing a complex query with and without Recompile:
As we can see, using Option(Recompile) improved memory usage by almost 10 times.
