This document explains how to review and optimize a Cloud SQL for SQL Server instance if that instance is identified
by the underprovisioned instance recommender as having high memory consumption.

## SQL Server memory

SQL Server memory can be divided into the following:

- [Caches](https://docs.cloud.google.com/sql/docs/sqlserver/optimize-high-memory-usage.md.txt#cache)
- [Fixed memory](https://docs.cloud.google.com/sql/docs/sqlserver/optimize-high-memory-usage.md.txt#fixed-memory)
- [SQL Server overhead](https://docs.cloud.google.com/sql/docs/sqlserver/optimize-high-memory-usage.md.txt#sql-server-overhead)
- [In-Memory online transactional processing (OLTP)](https://docs.cloud.google.com/sql/docs/sqlserver/optimize-high-memory-usage.md.txt#oltp)

### Caches

These are objects on a disk that can be reloaded, such as database pages and stored procedures. As a result,
the SQL Server can grow and shrink these objects based on memory utilization. Caches include buffer pools and plan caches.

### Fixed memory

Fixed memory can grow and shrink. It only shrinks when not in use; for example, when the number of connections drops or the number of queries executing decreases.
It's different from caches. If there is not enough fixed memory, SQL Server can run out of memory.
Fixed memory includes connection memory and memory grants.

### SQL Server overhead

SQL Server overhead includes threads and stacks.

### In-Memory OLTP

In-Memory OLTP includes In-Memory tables and In-Memory filegroups.

The memory consumption by SQL Server is controlled by setting `maximum server memory` and `memory.memory.limitmb`. The `memory.memory.limitmb` parameter is set by Cloud SQL automatically.

To learn more about `memory.memory.limitmb`, see the [Microsoft documentation](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver16#memorylimit).

## Memory optimization options

To determine if an instance needs more memory tuning, do the following:

- [Check the value of the `max server memory (mb)`](https://docs.cloud.google.com/sql/docs/sqlserver/flags.md.txt#special-flags) flag.

  We recommend you let Cloud SQL manage the value of this flag.
  If you must manually manage this value, use the `max_server_memory (mb)`
  usage formula outlined on [Best practices](https://docs.cloud.google.com/sql/docs/sqlserver/best-practices.md.txt#sqlserver_settings)
  to help prevent SQL Server from consuming all memory.

  For more information, see [Special flags](https://docs.cloud.google.com/sql/docs/sqlserver/flags.md.txt#special-flags).
- Monitor the `Page life expectancy` flag.

  `Page life expectancy` indicates the amount of time, in seconds, that the oldest page stays in the buffer pool.
  This value should be more than 300 as recommended by Microsoft. If it consistently falls under
  300, it could be an indication that the instance is facing high memory utilization.
  Run the following query to monitor `Page life expectancy`.

  ```bash
      SELECT
        [object_name],
        [counter_name],
        [cntr_value]
      FROM
        sys.dm_os_performance_counters
      WHERE
        [object_name]
      LIKE
        '%Manager%'
      AND
        [counter_name] = 'Page life expectancy'
      
  ```
- Check the `Memory Grants Pending` flag.

  `Memory Grants Pending` specifies the total number of processes waiting for a workspace memory grant.
  Run the following query to check `Memory Grants Pending`. If this query consistently shows grants pending,
  then it indicates high memory utilization. You can reduce it by querying the database
  waits and tuning any statement that's waiting on memory.

  ```bash
      SELECT
        @@SERVERNAME AS [Server Name],
        RTRIM([object_name]) AS [Object Name],
        cntr_value AS [Memory Grants Pending]
      FROM
        sys.dm_os_performance_counters WITH(NOLOCK)
      WHERE
        [object_name]
      LIKE
        N'%Memory Manager%'  -- Handles named instances
      AND
        counter_name = N'Memory Grants Pending'
      
  ```

## Use Metrics Explorer to identify the memory usage

You can review memory usage of the instance with the
[`database/memory/components.usage`](https://docs.cloud.google.com/sql/docs/sqlserver/admin-api/metrics.md.txt) metric in
[Metrics Explorer](https://docs.cloud.google.com/monitoring/charts/metrics-explorer.md.txt).

> [!NOTE]
> **Note:** If you have less than 10% memory in `database/memory/components.cache` and `database/memory/components.free` combined, the risk of an out-of-memory (OOM) event is high. To monitor the memory usage and to prevent OOM events, we recommend that you set up an [alerting policy](https://docs.cloud.google.com/monitoring/alerts.md.txt) with a metric threshold condition of 90% in `database/memory/components.usage`.

## What's next

- [Google Cloud recommenders](https://docs.cloud.google.com/recommender/docs/recommenders.md.txt)