Memory related issue in SQL Server
Memory Pressure:
When SQL Server's memory is under pressure, it can lead to paging, slowdowns, and potential crashes. Here's what you can do
Check Memory Usage:
Identify memory-consuming queries and overall memory usage:
SELECT
(physical_memory_in_use_kb / 1024) AS Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb / 1024) AS Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb / 1024) AS Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low FROM sys.dm_os_process_memory;
Identify Problematic Queries:
Use DMVs to find queries consuming the most memory:Note:- Not Running in SQL Server 2019 Version
SELECT TOP 10
total_worker_time/execution_count AS Avg_CPU_Time,
total_elapsed_time/execution_count AS AVG_Run_Time,
(total_logical_reads+total_physical_reads)/execution_count AS AVG_Reads,
(total_logical_writes+total_physical_writes)/execution_count AS AVG_Writes,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
Query to Find Top Memory-Consuming Statements:
SELECT TOP 10
qs.creation_time,
qs.execution_count,
qs.total_elapsed_time,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
qs.total_worker_time,
qs.total_physical_reads,
qs.total_logical_reads,
qs.total_logical_writes,
qs.total_clr_time,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_physical_reads DESC; -- Change to total_logical_reads or total_elapsed_time as needed
Optimize Queries:
- Rewrite queries to use less memory.
- Add appropriate indexes to improve query performance.
Adjust Server Configuration:
- Set appropriate max server memory for SQL Server to avoid excessive memory usage.
- Configure 'min server memory' and 'max server memory' settings
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 8192; -- Set appropriate value
RECONFIGURE;
Restart SQL Server Service:
In some cases, a restart of the SQL Server service can release memory and clear up any issues.
Tvenkat
I have 16+ years of experience in IT and 11+ years of experience in training. I am currently working in Bangalore and occasionally in Hyderabad as well. I have worked in various domains including Telecom, Healthcare, Pharma, Insurance, Retail, and Storage. Currently, I am working as a Cloud Principal Consultant in an MNC Product based company.
