O SQL Server é um poderoso sistema de gerenciamento de banco de dados que requer uma quantidade substancial de memória para operar de forma eficiente. No entanto, pode haver casos em que ocorra contenção de memória, levando a problemas de desempenho e prazos perdidos. Nesses casos, torna-se crucial solucionar problemas e identificar a causa raiz dos problemas de memória.

Uma técnica avançada para observar e solucionar problemas de memória no SQL Server é usar Eventos Estendidos (XE). Os Eventos Estendidos fornecem uma maneira geek e divertida de mergulhar nos detalhes internos do SQL Server e obter insights sobre eventos relacionados à memória.

Vamos começar selecionando alguns eventos que podem nos ajudar a rastrear problemas de memória:

SELECT xo.name AS NomeEvento, 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');

Ao investigar esses eventos com mais detalhes, podemos determinar se eles fornecem as informações necessárias para nossas necessidades de solução de problemas. Além disso, podemos pesquisar eventos relacionados usando palavras-chave:

DECLARE @Keyword VARCHAR(64) = 'memória'

SELECT oc.OBJECT_NAME AS NomeEvento
,oc.name AS nome_coluna, oc.type_name
,oc.column_type AS tipo_coluna
,oc.column_value AS valor_coluna
,oc.description AS descricao_coluna
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('',REVERSE(olm.name))-1)) AS NomeDLL
,olm.file_version
,xp.name AS NomePacote
,mv.map_value AS PalavraChavePesquisada
,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 NomeEvento,c.object_package_guid AS PkgGuid, v.map_value AS Canal
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.NomeEvento = oc.object_name
AND ch.PkgGuid = oc.object_package_guid
WHERE mv.map_value = @Keyword
ORDER BY oc.object_name;

Depois de identificar os eventos relevantes, podemos criar uma sessão de eventos para rastrear problemas de memória:

USE master;
GO

-- Crie a Sessão de Eventos
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'BuffMemPressure' )
DROP EVENT SESSION BuffMemPressure
    ON SERVER;
GO

EXECUTE xp_create_subdir 'C:DatabaseXE';
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:DatabaseXEBuffMemPressure.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;

Ao executar esta sessão de eventos, será gerada uma série de eventos que podem ser analisados para identificar problemas relacionados à memória. É recomendável monitorar de perto os arquivos de rastreamento e executar a sessão por curtos períodos para evitar sobrecarregar o sistema.

Ao examinar os eventos agrupados por nome do evento, podemos obter insights sobre os efeitos das operações de índice e do código mal otimizado no uso da memória. Essas informações podem nos ajudar a identificar as consultas que causam pressão de memória e otimizá-las adequadamente.

Explorar a memória do SQL Server com Eventos Estendidos é uma ferramenta poderosa para solucionar problemas de memória. Isso nos permite mergulhar nos detalhes internos do SQL Server e obter uma compreensão mais profunda dos eventos relacionados à memória. Experimente em seus servidores e veja como ele pode ajudar a otimizar o desempenho.

Para obter mais informações sobre o uso de Eventos Estendidos, confira nossa série de artigos projetados para ajudá-lo a aprender XE aos poucos. Você também pode explorar o poder dos Eventos Estendidos em relação ao Profiler neste artigo.

Obrigado por ler o nono artigo de nossa série “12 Dias de Natal” de 2018. Para ver a lista completa dos artigos, visite esta página.