We've been having an issue where memory usage from Extended Events grows to over 1 GB and then will not clear – even when we stop all XEvent session on the instance. This hasn't been an issue yet on most servers, but it is really hurting some of our smaller Dev/Test servers with only a few GB's of memory allocated to SQL Server. I'm hoping someone out there might have some ideas on how to fix this other than bouncing SQL Server.
Some relevant query output:
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)
Full disclosure – we run Dell Spotlight for SQL Server which opens up three XEvents session on each instance by default. The code for these 3 sessions is below.
I even disabled Spotlight on this instance, dropped the three XEvent Spotlight sessions, and stopped the system_health session. MEMORYCLERK_XE memory usage stayed at 1120 mb. DBCC FREESYSTEMCACHE ('ALL') also had no effect.
The 3 Spotlight Sessions:
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
Does anyone have any ideas what might be causing this? Is there any way to release this used memory?
Best Answer
After posting this question and getting some comments about upgrading SQL Server to the latest SP, I found this KB article. The behavior described in the article seemed to be very similar to what we were seeing.
We went ahead and applied SP3 + CU3 to get to the most recent version of SQL 2012 that we could.
After installing the SP and CU, the problem seemed to disappear. The memory usage for MEMORYCLERK_XE seems to have stabilized and removing the XEvent sessions releases all memory used by MEMORYCLERK_XE.
I hope this helps someone else out as it had us scratching our heads for quite a while.