Sql-server – MEMORYCLERK_XE Memory Usage Will not Clear

extended-eventssql serversql-server-2012

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.

Related Question