Temos tido um problema em que o uso de memória de eventos estendidos aumenta para mais de 1 GB e não é limpo - mesmo quando paramos todas as sessões XEvent na instância. Isso ainda não foi um problema na maioria dos servidores, mas está realmente prejudicando alguns de nossos servidores menores de desenvolvimento/teste com apenas alguns GB de memória alocados para o SQL Server. Espero que alguém tenha algumas ideias sobre como corrigir isso, além de pular o SQL Server.
Algumas saídas de consulta relevantes:
SELECT TOP 5 type
,NAME
,sum(pages_kb) / 1024 AS pages_mb
FROM sys.dm_os_memory_clerks
GROUP BY type
,NAME
ORDER BY sum(pages_kb) DESC
------------------------------
type NAME pages_mb
MEMORYCLERK_XE XE Engine 1133
USERSTORE_TOKENPERM TokenAndPermUserStore 55
MEMORYCLERK_SOSNODE SOS_Node 43
MEMORYCLERK_SQLBUFFERPOOL Default 41
MEMORYCLERK_SQLGENERAL Default 18
------------------------------
SELECT NAME
,total_buffer_size / 1024 AS total_buffer_size_kb
FROM sys.dm_xe_sessions
ORDER BY total_buffer_size_kb DESC
------------------------------
name total_buffer_size_kb
system_health 4223
SpotlightResponseTimeWaitData_3843 4223
SpotlightWorkloadAnalysis_3843 4223
SpotlightDeadlockDetection_3843 4031
sp_server_diagnostics session 383
SELECT @@VERSION
Microsoft SQL Server 2012 (SP1) - 11.0.3437.0 (X64)
May 29 2014 16:03:40
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Divulgação completa - executamos o Dell Spotlight for SQL Server, que abre três sessões XEvents em cada instância por padrão. O código para essas 3 sessões está abaixo.
Até desativei o Spotlight nesta instância, descartei as três sessões XEvent Spotlight e interrompi a sessão system_health. O uso de memória MEMORYCLERK_XE permaneceu em 1120 mb. DBCC FREESYSTEMCACHE ('ALL') também não teve efeito.
As 3 Sessões Spotlight:
CREATE EVENT SESSION [SpotlightDeadlockDetection_3843] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.ring_buffer (SET occurrence_number = (0))
WITH (
MAX_MEMORY = 4000 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 10 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = OFF
,STARTUP_STATE = OFF
)
GO
CREATE EVENT SESSION [SpotlightResponseTimeWaitData_3843] ON SERVER ADD EVENT sqlos.wait_info (
ACTION(package0.collect_system_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE (
[opcode] = (1)
AND (
[duration] > (0)
OR [signal_duration] > (0)
)
AND [sqlserver].[query_hash] <> (0)
AND [package0].[counter] <= (1000)
AND [wait_type] <> (214)
)
)
,ADD EVENT sqlos.wait_info_external (
ACTION(package0.collect_system_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE (
[opcode] = (1)
AND [duration] > (0)
AND [sqlserver].[query_hash] <> (0)
AND [package0].[counter] <= (1000)
AND [wait_type] <> (589)
)
) ADD TARGET package0.ring_buffer (
SET max_events_limit = (4000)
,occurrence_number = (1000)
)
WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = ON
,STARTUP_STATE = OFF
)
GO
CREATE EVENT SESSION [SpotlightWorkloadAnalysis_3843] ON SERVER ADD EVENT sqlserver.sp_statement_completed (
ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.plan_handle, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE (
[sqlserver].[is_system] = (0)
AND [package0].[counter] <= (1000)
)
)
,ADD EVENT sqlserver.sql_statement_completed (
ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.plan_handle, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE (
[sqlserver].[is_system] = (0)
AND [package0].[counter] <= (1000)
)
) ADD TARGET package0.ring_buffer (
SET max_events_limit = (4000)
,occurrence_number = (1000)
)
WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = ON
,STARTUP_STATE = OFF
)
GO
Alguém tem alguma ideia do que pode estar causando isso? Existe alguma maneira de liberar essa memória usada?
Depois de postar esta pergunta e obter alguns comentários sobre a atualização do SQL Server para o SP mais recente, encontrei este artigo da base de conhecimento . O comportamento descrito no artigo parecia ser muito semelhante ao que estávamos vendo.
Seguimos em frente e aplicamos SP3 + CU3 para obter a versão mais recente do SQL 2012 que pudemos.
Depois de instalar o SP e o CU, o problema pareceu desaparecer. O uso de memória para MEMORYCLERK_XE parece ter se estabilizado e a remoção das sessões XEvent libera toda a memória usada por MEMORYCLERK_XE.
Espero que isso ajude alguém, pois nos deixou coçando a cabeça por um bom tempo.