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, t.eventsequence, EventSubClass, --1 = increase,2=decrease IntegerData , --new memory size (mB) IsSystem --1=system,0=user RequestID, ServerName, SessionLoginName, SPID, StartTime 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