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
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 ...
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |