CODEWITHSUNDEEP
sql-servermemorydatabase-administration
tuxmania
tuxmania

Reputation: 956

How exactly can i troubleshoot MEMORY_ALLOCATION_EXT waits?

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?

Upvotes: 6

Views: 31818

Answers (2)

farhad hossain
farhad hossain

Reputation: 1

It occurs while allocating memory from either the internal SQL Server memory pool or the operating system. So, it's better to check the load when the query runs.

Upvotes: 0

Salvador L
Salvador L

Reputation: 121

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.

Upvotes: 5

Related Questions