SQL Server – мощная система управления базами данных, которая требует значительного объема памяти для эффективной работы. Однако могут возникать ситуации, когда возникает конфликт памяти, что приводит к проблемам с производительностью и пропущенным срокам. В таких случаях крайне важно устранить неполадки и определить причину проблем с памятью.

Одна из продвинутых техник для наблюдения и устранения проблем с памятью в SQL Server – использование расширенных событий (XE). Расширенные события предоставляют гиковский и увлекательный способ погрузиться во внутренности SQL Server и получить представление о событиях, связанных с памятью.

Давайте начнем с выбора нескольких событий, которые могут помочь нам отслеживать проблемы с памятью:

SELECT xo.name AS EventName, xo.capabilities_desc, xo.description
FROM sys.dm_xe_objects xo
WHERE xo.name IN ('large_cache_memory_pressure','buffer_manager_page_life_expectancy'
,'buffer_node_page_life_expectancy'
,'buffer_pool_eviction_thresholds_recalculated','server_memory_change');

Изучая эти события более подробно, мы можем определить, предоставляют ли они необходимую информацию для наших потребностей в устранении неполадок. Мы также можем искать связанные события с помощью ключевых слов:

DECLARE @Keyword VARCHAR(64) = 'memory'

SELECT oc.OBJECT_NAME AS EventName
,oc.name AS column_name, oc.type_name
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('\',REVERSE(olm.name))-1)) AS DLLName
,olm.file_version
,xp.name AS PackageName
,mv.map_value AS SearchKeyword
,ch.Channel
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_map_values mv
ON oc.type_name = mv.name
AND oc.column_value = mv.map_key
AND oc.object_package_guid = mv.object_package_guid
AND oc.name = 'KEYWORD'
INNER JOIN sys.dm_xe_packages xp
ON oc.object_package_guid = xp.guid
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
INNER JOIN (SELECT c.object_name AS EventName,c.object_package_guid AS PkgGuid, v.map_value AS Channel
FROM sys.dm_xe_object_columns c
INNER JOIN sys.dm_xe_map_values v
ON c.type_name = v.name
AND c.column_value = CAST(v.map_key AS NVARCHAR)
WHERE c.name = 'channel') ch
ON ch.EventName = oc.object_name
AND ch.PkgGuid = oc.object_package_guid
WHERE mv.map_value = @Keyword
ORDER BY oc.object_name;

После того, как мы определили соответствующие события, мы можем создать сеанс событий для отслеживания проблем с памятью:

USE master;
GO

-- Создание сеанса событий
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'BuffMemPressure' )
DROP EVENT SESSION BuffMemPressure
    ON SERVER;
GO

EXECUTE xp_create_subdir 'C:\Database\XE';
GO

CREATE EVENT SESSION [BuffMemPressure] ON SERVER
ADD EVENT sqlos.large_cache_memory_pressure (
ACTION ( sqlserver.database_id, 
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
),
ADD EVENT sqlserver.buffer_manager_page_life_expectancy (
ACTION ( sqlserver.database_id, 
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
),
ADD EVENT sqlserver.buffer_node_page_life_expectancy (
ACTION ( sqlserver.database_id, 
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
),
ADD EVENT sqlserver.buffer_pool_eviction_thresholds_recalculated (
ACTION ( sqlserver.database_id, 
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
),
ADD EVENT sqlserver.server_memory_change (
ACTION ( sqlserver.database_id, 
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
)
ADD TARGET package0.event_file (  SET filename = N'C:\Database\XE\BuffMemPressure.xel'
, max_rollover_files = ( 5 ) )
WITH (  MAX_MEMORY = 4096 KB
, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY = 5 SECONDS
, MAX_EVENT_SIZE = 0 KB
, MEMORY_PARTITION_MODE = NONE
, TRACK_CAUSALITY = ON
, STARTUP_STATE = ON );
GO

ALTER EVENT SESSION BuffMemPressure ON SERVER
STATE = START;

Запуск этого сеанса событий приведет к генерации большого количества событий, которые можно проанализировать для выявления проблем, связанных с памятью. Рекомендуется тщательно отслеживать файлы трассировки и запускать сеанс на короткие периоды, чтобы избежать перегрузки системы.

Изучая события, сгруппированные по имени события, мы можем получить представление о влиянии операций индексирования и плохо оптимизированного кода на память:

[Вставить изображение групп событий]

Имея эту информацию, мы можем точно определить запросы, вызывающие давление на память, и оптимизировать их соответствующим образом.

Исследование памяти SQL Server с помощью расширенных событий – мощный инструмент для устранения проблем с памятью. Он позволяет нам погрузиться во внутренности SQL Server и получить более глубокое понимание событий, связанных с памятью. Попробуйте его на своих серверах и дайте нам знать, как это работает!

Для получения дополнительной информации о расширенных событиях и их различных применениях ознакомьтесь с нашей серией статей, созданных для помощи в изучении XE постепенно. Вам также может быть интересно сравнить мощность расширенных событий с профилировщиком.

Спасибо за прочтение девятой статьи в нашей серии “12 дней Рождества” 2018 года. Полный список статей можно найти на этой странице.