2

On one of our servers we have problems with high stolen server memory (around 70% as reported by SCOM).

There are Sharepoint databases on the server and there are 2 extended events sesions called SharePoint_Diagnostics with ring buffers configured as a target.

CREATE EVENT SESSION [SharePoint_Diagnostics_3067bd7806cd459ebdbf3eb257c43267_0] ON SERVER 
ADD EVENT sqlserver.module_end(
    ACTION(sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[Greater_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:00000000-0000-0000-0000-000000000000') AND [package0].[Less_than_equal_unicode_string]([sqlserver].[client_app_name],'SharePoint Diagnostics:ffffffff-ffff-ffff-ffff-ffffffffffff'))) 
ADD TARGET package0.ring_buffer(SET max_memory=(49152))
WITH (MAX_MEMORY=98304 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

They seem to have max_memory set, but MEMORYCLERK_XE is taking around 9GB of memory (the server itself has 16GB).

Version of SQL Server is:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Is there any way to free memory without restarting SQL?

Could it be the issue with memory leak as described in: Memory leak occurs... ?

  • 1
    Yes it could be because there was memory leak and other memory related fixes in SQL Server 2012 Sp1 CU4. Testing and applying SQL Server 2012 Sp3 is highly recommended – Shanky Aug 22 '16 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.