3 совета по быстрому устранению неполадок памяти для SQL Server

Tags: SQL Server

Friday, September 20, 2019 3:56:24 PM

Есть много неправильных представлений о SQL с использованием памяти (RAM) на физическом сервере. Самое частое, что можно услышать, - это то, что пользователь беспокоится о том, что ОЗУ сервера будет максимально заполнено. SQL Server предназначен для использования как можно большего объема памяти. Единственным ограничением является количество памяти, на которое установлен экземпляр (Максимальная память), и объем оперативной памяти на сервере.

Например, представьте, что ваш сервер SQL работает оптимально, только с 8 ГБ памяти, а сервер показывает ~ 95% от общего объема используемой оперативной памяти. Вы можете удвоить ОЗУ на машине, удвоить настройку Max Memory экземпляра SQL, а затем наблюдать, как сервер медленно поднимается до 95%. Это не обязательно проблема. SQL просто кэширует столько временных данных, сколько может с тем, что ему дано.

Ниже приведены наши краткие сведения о том, есть ли на самом деле проблема с памятью или SQL Server просто выполняет то, что предполагается сделать:

  1. Проверьте параметр Max Memory в свойствах экземпляра и сравните его с общей памятью сервера. SQL нужно давать как можно больше, но каждая среда отличается. Есть также много факторов, которые необходимо учитывать (количество экземпляров, приложений, нагрузка, состояние кластера и т. д.). По крайней мере, убедитесь, что для операционной системы осталось несколько ГБ. Кроме того, убедитесь, что все остальное, что для этого нужно, на этой машине.
  2. Если максимальная память установлена на 2147483647, измените ее прямо сейчас. Это значение по умолчанию, с которым устанавливается SQL, и говорит о необходимости использовать столько, сколько ему нужно. Это может вызвать проблемы с производительностью для ОС и других приложений на сервере и замедлить все, если это когда-либо будет узким местом.

 

  1. Запустите встроенный отчет о потреблении памяти из свойств экземпляра. Полезные детали для немедленного поиска - это высокое значение PLE и низкое значение ожидающих предоставления памяти. Page Life Expectancy - это количество секунд, в течение которых страница будет оставаться в пуле буферов, прежде чем ее можно будет «повторно использовать» на сервере. Общая рекомендация - иметь 300 секунд или больше, но эта рекомендация экспоненциально увеличивается, когда на сервере больше оперативной памяти. Memory Grants Pending - это число процессов, ожидающих предоставления памяти рабочей области. Ноль - это лучшее значение, поскольку оно означает, что все, что работает, может сделать это с достаточным объемом памяти, который ему необходим.

 

 

  1. Запустите приведенный ниже запрос, чтобы проверить текущие счетчики памяти. Третий набор результатов покажет временную метку, когда произошло изменение памяти. Следите за любыми «низкими» предупреждениями о памяти и с этого момента определяйте, следует ли дополнительно исследовать нагрузку на память, если SQL использует соответствующее количество.

 

SELECT @@SERVERNAME AS [Server Name]

,total_physical_memory_kb / 1024 AS [Total Physical Memory (MB)]

,available_physical_memory_kb / 1024 AS [Available Physical Memory (MB)]

,total_page_file_kb / 1024 AS [Total Page File Memory (MB)]

,available_page_file_kb / 1024 AS [Available Page File Memory (MB)]

,system_memory_state_desc AS [Available Physical Memory]

,CURRENT_TIMESTAMP AS [Current Date Time] 

FROM sys.dm_os_sys_memory

OPTION (RECOMPILE);

GO

SELECT physical_memory_in_use_kb / 1024 AS [Physical Memory In Use (MB)]

,locked_page_allocations_kb / 1024 AS [Locked Page In Memory Allocations (MB)]

,memory_utilization_percentage AS [Memory Utilization Percentage]

,available_commit_limit_kb / 1024 AS [Available Commit Limit (MB)]

,CASE WHEN process_physical_memory_low = 0 THEN 'No Memory Pressure Detected' ELSE 'Memory Low' END AS 'Process Physical Memory'

,CASE WHEN process_virtual_memory_low = 0 THEN 'No Memory Pressure Detected' ELSE 'Memory Low' END AS 'Process Virtual Memory'

,CURRENT_TIMESTAMP AS [Current Date Time] 

FROM sys.dm_os_process_memory

OPTION (RECOMPILE);

GO

WITH RingBuffer

AS (

SELECT CAST(dorb.record AS XML) AS xRecord

,dorb.TIMESTAMP

FROM sys.dm_os_ring_buffers AS dorb

WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

)

SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS Notification

,CASE 

WHEN xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 1

THEN 'High Physical Memory Available'

WHEN xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 2

THEN 'Low Physical Memory Available'

WHEN xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 4

THEN 'Low Virtual Memory Available'

ELSE 'Physical Memory Available'

END AS 'Process Memory Status'

,CASE 

WHEN xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') = 1

THEN 'High Physical Memory Available'

WHEN xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') = 2

THEN 'Low Physical Memory Available'

WHEN xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') = 4

THEN 'Low Virtual Memory Available'

ELSE 'Physical Memory Available'

END AS 'System-Wide Memory Status'

,DATEADD(ms, - 1 * dosi.ms_ticks - rb.TIMESTAMP, GETDATE()) AS NotificationDateTime

FROM RingBuffer AS rb

CROSS APPLY rb.xRecord.nodes('Record') record(xr)

CROSS JOIN sys.dm_os_sys_info AS dosi

ORDER BY NotificationDateTime DESC;

 

No Comments

Add a Comment