Check Memory pressure on the server

When working with SQL Server, monitoring memory is crucial for optimal performance. These queries provide valuable insights into your system's memory state, helping you identify and address potential memory pressure issues.

Checking for Process Memory Low Conditions

The following query helps determine if your SQL Server process is experiencing low physical or virtual memory. Ideally, both values should be zero.

SELECT m.process_physical_memory_low
 ,m.process_virtual_memory_low
FROM sys.dm_os_process_memory m --both should be zero

Current Memory Usage by SQL Server Process

This query displays the amount of physical memory currently being used by your SQL Server instance in megabytes (MB).

SELECT m.physical_memory_in_use_kb / 1024 [Mem Used in MB]
FROM sys.dm_os_process_memory m

Overall System Memory Status

Gain a comprehensive overview of your system's memory, including total physical memory, available physical memory, and the current system memory state. The `system_memory_state_desc` provides a quick indicator of memory health.

SELECT m.total_physical_memory_kb / 1024 [Mem in MB]
 ,m.available_physical_memory_kb / 1024 [Available Mem MB]
 ,m.system_memory_state_desc
FROM sys.dm_os_sys_memory m

Committed Memory Information

Understand how much memory has been committed by the operating system to SQL Server and the target memory for the instance.

SELECT m.committed_kb / 1024 [Committed in MB]
 ,m.committed_target_kb / 1024 [Target Mem MB]
FROM sys.dm_os_sys_info m

Possible System Memory State Descriptions:

• Available physical memory is high: Your system has ample free physical memory.
• Physical memory usage is steady: Memory usage is stable, with no significant fluctuations.
• Available physical memory is low: Your system is running low on available physical memory.
• Available physical memory is running low: A critical state indicating very little free physical memory.
• Physical memory state is transitioning: The system's memory state is currently changing.

Detailed OS Memory Information

This query provides more detailed insights into your operating system's memory, including page file usage and system cache. Ideally, `System Memory State` should be "Available physical memory is high" for optimal performance.

SELECT total_physical_memory_kb/1024 AS [Total Memory (MB)],
       available_physical_memory_kb/1024 AS [Available Memory (MB)],
       total_page_file_kb/1024 AS [Total Page File (MB)],
       available_page_file_kb/1024 AS [Available Page File (MB)],
       system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State] --should be "Available physical memory is high"
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

Further Reading and Resources:

• sys.dm_os_sys_memory (Microsoft Docs)
• Get Any Memory Dumps from SQL Server
• Get Memory Clerk Usage
• Get In-Memory OLTP Index Usage
• Memory Grants Pending Value

Get lock waits for the current database

Hey there, fellow database enthusiasts! Ever wondered about those pesky database locks that can slow things down? Today, we're sharing a super useful SQL query to help you identify and analyze lock waits in your SQL Server databases. This script is a fantastic tool for pinpointing tables and indexes experiencing contention, allowing you to optimize your database performance.

Here’s the SQL query:

SELECT o.name AS [table_name]
 ,i.name AS [index_name]
 ,ios.index_id
 ,ios.partition_number
 ,SUM(ios.row_lock_wait_count) AS [total_row_lock_waits]
 ,SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms]
 ,SUM(ios.page_lock_wait_count) AS [total_page_lock_waits]
 ,SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms]
 ,SUM(ios.page_lock_wait_in_ms) + SUM(row_lock_wait_in_ms) AS [total_lock_wait_in_ms]
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
INNER JOIN sys.objects AS o WITH (NOLOCK) ON ios.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ios.[object_id] = i.[object_id]
 AND ios.index_id = i.index_id
WHERE o.[object_id] > 100
GROUP BY o.name
 ,i.name
 ,ios.index_id
 ,ios.partition_number
HAVING SUM(ios.page_lock_wait_in_ms) + SUM(row_lock_wait_in_ms) > 0
ORDER BY total_lock_wait_in_ms DESC
OPTION (RECOMPILE);

This query leverages sys.dm_db_index_operational_stats, a dynamic management function that provides detailed information about index activity, including lock waits. By joining it with sys.objects and sys.indexes, we can get a clear picture of which tables and their associated indexes are experiencing the most lock contention.

The results will show you the total wait counts and wait times for both row and page locks, helping you quickly identify bottlenecks. Remember, analyzing these lock waits is a crucial step in maintaining a healthy and performant SQL Server environment!

Columnstore index physical statistics

Ever wondered how to peek behind the curtains of your SQL Server's columnstore indexes? This handy query will give you a detailed look at their physical stats, helping you understand their health and performance.

SELECT OBJECT_NAME(ps.object_id) AS [TableName]
 ,i.[name] AS [IndexName]
 ,ps.index_id
 ,ps.partition_number
 ,ps.delta_store_hobt_id
 ,ps.state_desc
 ,ps.total_rows
 ,ps.size_in_bytes
 ,ps.trim_reason_desc
 ,ps.generation
 ,ps.transition_to_compressed_state_desc
 ,ps.has_vertipaq_optimization
 ,ps.deleted_rows
 ,100 * (ISNULL(ps.deleted_rows, 0)) / ps.total_rows AS [Fragmentation]
FROM sys.dm_db_column_store_row_group_physical_stats AS ps WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.object_id = i.object_id
 AND ps.index_id = i.index_id
ORDER BY ps.object_id
 ,ps.partition_number
 ,ps.row_group_id
OPTION (RECOMPILE);

This SQL script dives into sys.dm_db_column_store_row_group_physical_stats to provide insights like table and index names, row counts, size in bytes, and even a fragmentation percentage. It's a great tool for monitoring your columnstore indexes and ensuring they're performing optimally.