SQL Server – Troubleshoot memory pressure with Default Trace and Server Memory Change event class

29 November,2012 by Jack Vamvas

Troubleshooting external memory pressure for SQL Server can be done using Perfmon or the sys.dm_os_memory_clerks DMV.    Another method for external memory troubleshooting , is the Server Memory Change event class , reported as part of the Default Trace

SQL Server BOL  defines the Server Memory Change event class  as  “ occurs when Microsoft SQL Server memory usage has increased or decreased by either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever is greater.”

The Server Memory Change event class  reports on the variances in the delta changes. Using this information the DBA can identify if large changes occurred in the Max Server Memory. This relates only to the Buffer Pool.

 If the SQL Server is experiencing internal memory pressure – such as stolen pages , focus on other methods – such as SQL Server – Troubleshooting Internal Memory pressure – stolen pages and buffer count  or SQL Server Performance ,memory pressure and memory usage  

Use this query to list the instances in the current default trace of the Server Memory Change events class.

This technique is one of many  SQL Server – DBA tactics , useful in troubleshooting SQL Server performance issues

select e.name as eventclass,
EventSubClass, --1 = increase,2=decrease
IntegerData , --new memory size (mB)
IsSystem --1=system,0=user
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), 
( SELECT TOP 1 f.[value]FROM sys.fn_trace_getinfo(NULL) f 
WHERE f.property = 2)), DEFAULT) T 
inner join sys.trace_events e on 
t.eventclass = e.trace_event_id where eventclass=81


Read More

 SQL Server – default trace FAQ - SQL Server DBA

SQL Server – Measuring SQL memory usage - SQL Server DBA

SQL Server – Memory: Pages sec, memory pressure and thrashing ...


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server – Troubleshoot memory pressure with Default Trace and Server Memory Change event class

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer