How exactly can i troubleshoot MEMORY_ALLOCATION_EXT waits?

Question

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?

Solution

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.

Comments

[TheGameiswar]

It seems your server runs in vm,did you allocate RAM as Dram or static ram.

[tuxmania]

Good question. I am only the developer not the DBA nor the systemadmin so I cannot tell you.

[TheGameiswar]

check this image and update question ,for any further issues:google.com/imgres?imgurl=http://sqlmag.com/content/co‌​ntent/…

[TheGameiswar]

You can see those settings,if you right click hyper V