0

I have a SQL Server cluster with 80+Gb of RAM and multiple production enterprise databases and applications running. We are encountering memory-pressure errors and are trying to troubleshoot what's happening that leads up to this. Our indications are that some CLR libraries are being unloaded and re-loaded, followed by a system slowdown. I have read through some other items

We are setting up an Extended Event session for rpc_completed, sql_statement_completed and sp_statement_completed, filtering all where duration > 1s.

I have also read through Marked for unload due to memory pressure as well as some other articles and still have some questions:

  • What other events should we listen for?
  • Are there any concerns with running this session on a SQL Server with 80+ GB of RAM?
  • What are some tips on narrowing down major causes to Memory Pressure errors on enterprise servers hosting multiple production databases?

This is our current event session:

CREATE EVENT SESSION [SlowRunningQueries] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text
    ,sqlserver.username
    )
    WHERE ([duration]>=(1000000))
    )
,ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text
    ,sqlserver.username
    )
    WHERE ([duration]>=(1000000))
    ) 
,ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text
    ,sqlserver.username
    )
    WHERE ([duration]>=(1000000))
    ) 
ADD TARGET package0.event_file(
    Set filename='C:\Traces\ProductionTrace.xel'
    ,max_file_size=500 --in MBs, default is 1Gb
    ,max_rollover_files=10
)
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=OFF,STARTUP_STATE=OFF)
GO

migrated from stackoverflow.com Jul 7 '15 at 18:10

This question came from our site for professional and enthusiast programmers.

  • Do you have max server memory configured for your SQL instances? By default, SQL server will consume as much memory as it can unless you limit it. – Kris Gruttemeyer Jul 7 '15 at 18:48
  • @KrisGruttemeyer Yep, the Server has 96Gb and SQL Server is limited to ~82Gb. – Brent D Jul 7 '15 at 19:18

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.