Monitoring Memory Usage: A Guide For Sql Server 2008

Monitoring memory usage in SQL Server 2008 is crucial to ensure optimal performance and resource allocation. SQL Server is designed to utilise memory efficiently, and by default, it manages its memory requirements dynamically based on available system resources. However, understanding memory consumption patterns is essential for database administrators (DBAs) to fine-tune performance and prevent potential issues.

There are various methods and tools available to monitor memory usage in SQL Server 2008. One common approach is to use Dynamic Management Views (DMVs) such as sys.dm_os_process_memory and sys.dm_os_sys_memory, which provide detailed information about memory allocation and usage. Additionally, performance counters like Memory: Available Bytes and Memory: Pages/sec can help identify low-memory conditions and excessive paging activity.

Furthermore, Microsoft SQL Server Management Studio, introduced in SQL Server 2014, offers built-in standard reports to monitor memory consumption by in-memory tables and individual memory-optimised tables.

By utilising these tools and techniques, DBAs can gain valuable insights into memory usage, identify potential bottlenecks, and optimise the performance of their SQL Server instances.

Monitor memory usage with SQL Server Management Studio

Monitoring memory usage with SQL Server Management Studio is a straightforward process. Here is a detailed, step-by-step guide:

Launch SQL Server Management Studio

Firstly, open SQL Server Management Studio. You can skip this step if you already have the program open.

Connect to your SQL Server or SQL Managed Instance

In SQL Server Management Studio, connect to your chosen SQL Server or SQL Managed Instance.

Access the Object Explorer

Right-click on the database you want to generate reports for. This can be found in the Object Explorer.

Generate Reports

In the context menu, select 'Reports', followed by 'Standard Reports', and then 'Memory Usage by Memory-Optimised Objects'.

Interpret Reports

These reports will show memory consumption by the database.

Monitor Memory Usage with DMVs

Dynamic Management Views (DMVs) can also be used to monitor memory consumption by memory-optimised tables, indexes, system objects, and run-time structures.

Memory Consumption by Memory-Optimised Tables and Indexes

To find memory consumption for all user tables, indexes, and system objects, query 'sys.dm_db_xtp_table_memory_stats'.

Memory Consumption by Internal System Structures

To find memory used for system objects, such as transactional structures and buffers, query 'sys.dm_xtp_system_memory_consumers'.

Memory Consumption at Run-Time

To determine the memory consumed by run-time structures, such as the procedure cache, use the following query:

SELECT memory_object_address

Pages_in_bytes

Bytes_used

Type

FROM sys.dm_os_memory_objects

WHERE type LIKE '%xtp%''

Memory Consumed by In-Memory OLTP Engine

To find all the memory used by the In-Memory OLTP engine, use the following query:

SELECT type

Name

Memory_node_id

Pages_kb/1024 AS pages_MB

FROM sys.dm_os_memory_clerks

WHERE type LIKE '%xtp%''

Use sys.dm_os_performance_counters or sys.dm_os_process_memory to isolate memory used by SQL Server

To isolate the memory used by SQL Server, you can use the dynamic management views sys.dm_os_performance_counters and sys.dm_os_process_memory. These tools allow you to monitor SQL Server memory usage and identify any memory bottlenecks.

The sys.dm_os_performance_counters dynamic management view provides detailed information about various performance counters, including those specific to SQL Server. By querying this view, you can access a wealth of data about the system's performance, such as the buffer cache hit ratio, page life expectancy, and the number of database pages in the buffer pool. This information can help you identify areas where SQL Server may be experiencing memory pressure or not utilising memory efficiently.

On the other hand, the sys.dm_os_process_memory dynamic management view offers insights into the memory usage of the SQL Server process itself. It provides metrics such as physical memory in use, locked-page allocations, virtual address space committed, and more. By analysing these metrics, you can determine if high memory usage is due to internal SQL Server processes or other factors.

Sql

- Using sys.dm_os_performance_counters to check buffer cache hit ratio

SELECT cntr_value AS Buffer_Cache_Hit_Ratio

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio';

Sql

- Using sys.dm_os_process_memory to get memory usage details

SELECT physical_memory_in_use_kb AS Actual_Usage,

Large_page_allocations_kb AS Large_Pages,

Locked_page_allocations_kb AS Locked_Pages,

Virtual_address_space_committed_kb AS VAS_Committed

FROM sys.dm_os_process_memory;

By regularly monitoring memory usage with these dynamic management views, you can identify trends, detect potential memory leaks, and optimise memory allocation for better SQL Server performance.

Use sys.dm_os_sys_memory to monitor operating system memory

To monitor operating system memory, you can use the following Windows server counters:

• Memory: Available Bytes – Indicates how many bytes of memory are currently available for use by processes. This can be queried via T-SQL using `sys.dm_os_sys_memory.available_physical_memory_kb`. Low values may indicate an overall shortage of memory.
• Memory: Pages/sec – Indicates the number of pages retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults. A high rate could indicate excessive paging.
• Memory: Page Faults/sec – Indicates the rate of page faults for all processes, including system processes. A low but non-zero rate of paging to disk (and page faults) is typical, even with plenty of available memory. This is because the Microsoft Windows Virtual Memory Manager (VMM) takes pages from SQL Server and other processes as it trims the working-set sizes of those processes, which tends to cause page faults.
• Process: Page Faults/sec – Indicates the rate of page faults for a given user process. Monitoring this counter will help determine if disk activity is caused by paging by SQL Server.

To monitor SQL Server memory usage, use the following SQL Server object counters:

• SQL Server: Memory Manager: Total Server Memory (KB) – Indicates the amount of the operating system's memory the SQL Server memory manager currently has committed to SQL Server. This number is expected to grow as required by actual activity and following SQL Server startup. Query this counter using the `sys.dm_os_sys_info` dynamic management view, observing the `committed_kb` column.
• SQL Server: Memory Manager: Target Server Memory (KB) – Indicates the ideal amount of memory SQL Server could consume, based on recent workload. Compare this to Total Server Memory after a period of typical operation to determine whether SQL Server has the desired amount of memory allocated. After typical operation, Total Server Memory and Target Server Memory should be similar. If Total Server Memory is significantly lower, the SQL Server instance may be experiencing memory pressure. Query this counter using the `sys.dm_os_sys_info` dynamic management view, observing the `committed_target_kb` column.

You can also use the `sys.dm_os_sys_memory` view to determine the current memory allocation:

SELECT (total_physical_memory_kb/1024) AS Total_OS_Memory_MB,

Available_physical_memory_kb/1024) AS Available_OS_Memory_MB

FROM sys.dm_os_sys_memory;

This will return the total size of physical memory available to the operating system in megabytes, as well as the size of physical memory available in megabytes.

Note that by default, a SQL Server instance may consume most of the available Windows operating system memory over time. The memory will not be released unless memory pressure is detected. This is by design and does not indicate a memory leak in the SQL Server process. You can use the `max server memory` option to limit the amount of memory that SQL Server is allowed to acquire.

Use sys.dm_db_xtp_table_memory_stats to monitor memory consumption by memory-optimised tables and indexes

Monitoring memory usage with DMVs (Dynamic Management Views) is a great way to monitor memory consumption by memory-optimised tables and indexes. DMVs allow you to monitor memory consumption at both the system and database level, and they can help prevent problems due to memory exhaustion.

To monitor memory consumption by memory-optimised tables and indexes, you can use the DMV `sys.dm_db_xtp_table_memory_stats`. This DMV returns memory usage statistics for each In-Memory OLTP table (user and system) in the current database. The system tables have negative object IDs and are used to store run-time information for the In-Memory OLTP engine.

Sql

SELECT object_name(object_id) AS [Name]

, *

FROM sys.dm_db_xtp_table_memory_stats;

The output of this query will include the following columns:

• `object_id`: The object ID of the table.
• `memory_allocated_for_table_kb`: Memory allocated for this table.
• `memory_used_by_table_kb`: Memory used by the table, including row versions.
• `memory_allocated_for_indexes_kb`: Memory allocated for indexes on this table.
• `memory_used_by_indexes_kb`: Memory consumed for indexes on this table.

It is important to note that you need the ``VIEW DATABASE STATE`` permission on the current database to see all rows in the output. Without this permission, you will only see the columns for the tables you have ``SELECT`` permission on.

Use sys.dm_xtp_system_memory_consumers to monitor memory consumption by internal system structures

The dynamic management view (DMV) sys.dm_xtp_system_memory_consumers reports system-level memory consumers for In-Memory OLTP. The memory for these consumers comes from either the default pool (when the allocation is in the context of a user thread) or from the internal pool (when the allocation is in the context of a system thread).

The following query will return the memory consumed by internal system structures:

Sql

SELECT memory_consumer_desc, allocated_bytes/1024 AS allocated_bytes_kb, used_bytes/1024 AS used_bytes_kb, allocation_count

FROM sys.dm_xtp_system_memory_consumers;

The output will show all memory consumers at the system level. For example, there will be consumers for transaction look aside, the system heap, and lookaside heaps.

The `memory_consumer_desc` column describes the type of memory consumer, such as "VARHEAP", "LOOKASIDE", or "PGPOOL". The `allocated_bytes` column shows the number of bytes reserved for each consumer, while the `used_bytes` column shows the number of bytes used by each consumer. The `allocation_count` column displays the number of allocations.

To see the total memory consumed by system allocators, you can use the following query:

Sql

SELECT SUM(allocated_bytes) / (1024 * 1024) AS total_allocated_MB, SUM(used_bytes) / (1024 * 1024) AS total_used_MB

FROM sys.dm_xtp_system_memory_consumers;

This will give you the total amount of memory allocated and used by all system-level memory consumers.

Frequently asked questions