How exactly can i troubleshoot MEMORY_ALLOCATION_EXT waits?

sql-server memory database-administration
tuxmania picture tuxmania · Oct 8, 2016 · Viewed 12.4k times · Source

We have a server environment in SQL Server 2016. The Server is virtualized meaning it shares its memory and cpu.

Lately if heavily under stress i.e. sorts building hashtables etc. we see heavy growth of memory usage that was expectable.

However we see many 

MEMORY_ALLOCATION_EXT 

RESERVED_MEMORY_ALLOCATION_EXT

Waits.

So the server had enough memory and we also doubled the memory recently but still the memory waits remain.

From monitoring the server state it doesn't seem it needs more memory but what would you suggest can be the cause of those waits?

Answer

Salvador L picture Salvador L · Aug 15, 2017

Paul Randall says that it is safe to ignore "MEMORY_ALLOCATION_EXT", and his guidance is very reliable; look at the predicate "WHERE [wait_type] NOT IN" on the query.

For RESERVED_MEMORY_ALLOCATION_EXT I'd setup an Extended Event that caches what specific transactions are having those waits and see their execution plan. I am not positive that it relates to actual system or user transactions.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More