SQL Server Memory Usage Optimization

Microsoft SQL Server utilizes memory to cache data and improve the server’s performance. By design, SQL Server reads data pages from disk into memory. It stores as many pages as you tell it to store and only evicts them as needed. When the server receives queries requiring pages it’s used before, it can retrieve them quickly from memory instead of having to create them from scratch.

It’s a common misconception that SQL Server uses up all your memory or hogs your memory resources. Because SQL Server works to optimize performance, it uses as much of the memory allocated to it as possible. However, it’s entirely up to you how much memory you allocate to SQL Server and how much you leave for other applications or the OS.

When it comes to system performance, there are three key elements working together to keep the system performing optimally: CPU, storage, and memory. Memory needs to be allocated effectively to prevent performance bottlenecks.

Determining how much memory to allocate SQL Server is a challenging task requiring you to consistently monitor SQL Server memory usage. By monitoring and maximizing your SQL Server memory, you can improve your SQL Server performance and the server’s query response times.

SQL Server memory usage monitoring involves your Database Administrator (DBA) looking at measurements of memory usage within your entire computer system—not just for SQL Server—and using this data to properly allocate your finite memory resources.

The main goal of SQL Server memory usage monitoring is to determine how much memory should be designated for SQL Server without compromising the performance of the operating system or other applications. To achieve this, you need to monitor your system’s performance metrics and use this data to influence your SQL Server memory tuning.

Several factors can influence your larger computer system’s memory usage. Monitoring and optimizing SQL memory usage involves locating data on these factors so you can determine which SQL queries need your attention. Here are some of the factors influencing memory usage:

• Page life expectancy (PLE is an important metric for monitoring memory usage. Drops in PLE indicate pages aren’t staying in memory for other uses. This behavior usually occurs when SQL Server runs low on buffer memory.)

• Plan cache size
• OS memory usage
• Buffer cache size
• Buffer cache hit ratio
• Memory paging rate
• Log bytes per second
• Log flushes per second
• Procedure cache hit ratio
• SQL compilations

When it comes to SQL Server memory usage, monitoring involves looking at the SQL Server memory consumption report you can find in any system with a SQL Server. This report provides information in sections and includes the following:

• Top memory-consuming components
• Buffer pages distribution
• Memory changes over the last seven days
• Memory usage by component

Monitoring this data allows you to better determine what you should set as the max memory for your SQL Server and how you should adjust this maximum as your needs and uses change.

Though it’s possible to monitor SQL Server memory usage manually, it can be challenging to constantly locate and compare metrics, all of which are found on different screens and within different tools. Instead, it’s best to use a single tool to help with your SQL Server memory monitoring needs. SolarWinds^® Database Performance Analyzer, which is built to collect relevant data, correlate it, and present it in a single pane of glass, is one such tool.

Monitoring SQL Server memory usage is important because it’s directly correlated with SQL Server performance. It has a major impact on how quickly queries are executed.

By monitoring PLE as part of your general SQL Server memory usage monitoring, you can determine whether SQL Server is running low on buffer memory. This can indicate you’re missing indexes, or your SQL statements are poorly written.

Unfortunately, the default memory usage settings in SQL Server are usually far from ideal. If you don’t optimize these settings, you might feel like the only way to improve your performance is by investing in more memory. This costly endeavor won’t fix the problem. Instead, it’ll just act as a Band-Aid until you run out of memory again and are forced to purchase more and more.

By monitoring SQL Server memory usage and applying the insights you gain from it, you can tackle the problems causing your performance issues head-on. By optimizing your memory usage, you can even improve your SQL Server performance without wasting money on more memory.

A SQL Server memory usage monitoring tool provides you with the necessary insights into your system to optimize your SQL Server performance and improve the server’s memory usage. These tools don’t just let you see information about the different factors influencing memory usage; they give you visibility into the performance metrics required to optimize bad or expensive queries.

SQL Server memory usage monitoring tools help DBAs identify which SQL queries to focus on by pulling and collating second-by-second data from active sessions. They also rank SQL statements by their response times, so you can better identify where problems lie.

With a monitoring tool, you can see the direct correlation between response time and system resources—such as CPU, storage, and memory—from a single pane of glass. By providing visibility into performance metrics such as average execution, query duration, CPU time, logical/physical reads, and more, monitoring tools help you gather information, establish baselines, and maintain a history of the best execution methods for effective SQL query optimization.

When it comes to memory, one of the biggest benefits of using a tool for SQL Server monitoring is the tool is designed to constantly collect information about page life expectancy, plan cache size and more.

Page life expectancy, or PLE, is the number of seconds a page stays in the buffer pool without references. The longer the page stays in memory, the greater the chances the page will be reused. Drops in PLE indicate pages aren’t staying in memory for other uses. This behavior usually occurs when SQL Server is running low on buffer memory. Typically, poorly written SQL statements or missing indexes can cause SQL Server to use too much memory because the query is reading too much data to return a much smaller result set. Proper indexing and well-written queries can reduce memory stress, optimizing memory usage.

The plan cache looks at the buffer pool size and shows the memory size configured. Every time a SQL statement is executed, the engine searches to see whether the plan for the SQL statement already exists. If it does, the engine makes use of it instead of wasting time compiling a new plan. This is how the plan cache improves the efficiency of the SQL Server engine. Storing a query plan for a statement executed only once is called a single-use plan. Too many single-use plans can exhaust SQL Server memory and result in poor SQL Server performance.

By consistently monitoring and providing detailed data on these two metrics, a SQL Server memory usage monitoring tool can provide DBAs with the information they need to effectively adjust and optimize their SQL Server memory allocation and their SQL queries in general.

SolarWinds SQL Sentry provides comprehensive tools and real-time monitoring to help database administrators optimize SQL Server memory usage. It collects data on various memory-related factors, identifies problematic queries, allows you to set performance baselines, and streamlines performance analysis. Additionally, it extends its monitoring to VMware environments, enabling proactive management and providing a holistic view of database health beyond memory metrics. SQL Sentry's features assist in maintaining peak performance and stability in SQL Server environments.

• SQL Server Memory : Buffer Cache Size
• SQL Server Memory : Plan Cache Size (SQL)
• SQL Server Memory : Plan Cache Size (Objects)
• SQL Server Memory : Other Cache Size
• SQL Server Memory : In-Mem OLTP
• SQL Server Memory: Query Grants
• SQL Server Memory: Columnstore
• SQL Server Memory : Plan Cache Hit Ratio (SQL)
• SQL Server Memory : Plan Cache Hit Ratio (Objects)
• SQL Server Memory : Stolen Buffer
• SQL Server Memory : Page Reads
• SQL Server Memory : Page Writes
• SQL Server Memory : Page Life Expectancy
• SQL Server Memory : Memory Grants Pending
• System Memory : Memory by SQL Server / SSAS instance
• System Memory : Other Processes Memory
• System Memory : File Cache Memory
• System Memory : Ballooned memory**
• System Memory : Available Memory
• System Memory : Read Faults
• System Memory : Write Faults
• System Memory : Page File Usage

You can find the full list in the SolarWinds documentation.