Troubleshooting MEMORY ALLOCATION EXT wait type

Question

Post reply

Troubleshooting MEMORY ALLOCATION EXT wait type

GA_SQL

Hall of Fame

Points: 3166
More actions
June 14, 2017 at 6:07 am

#322051

I am getting this wait type on my DB Server, please let me know the reason y it caused and also the troubleshooting steps

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are you getting it significantly? All the time, or just occasionally

https://www.sqlskills.com/help/waits/memory_allocation_ext/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GA_SQL Hall of Fame Points: 3166 More actions · Answer 2

Frequently getting this MEMORY ALLOCATION EXT wait type ,which takes upto 40 seconds...which comes with another wait type which is happening frequently PREEMPTIVE_XE_GETTARGETSTATE

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I wouldn't worry about the latter, it's considered a benign wait.

Are you seeing the MEMORY ALLOCATION EXT wait frequently on user sessions? Sessions that are actually running queries ones being run by the application?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GA_SQL Hall of Fame Points: 3166 More actions · Answer 4

Yes i am seeing more often in User session, which the time increases on daily basis

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Are you using filestream?
Are you seeing lots of page splits?
Are you seeing these when inserting or updating LOB data types?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GA_SQL Hall of Fame Points: 3166 More actions · Answer 6

GA_SQL

Hall of Fame

Points: 3166

June 14, 2017 at 6:54 am

#1946742

this is the statement

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

What monitoring tools are being used for the environment? I've seen this wait type happen frequently with DMV query execution, specifically often with sys.dm_exec_requests, and consider it to be a relatively benign wait type that I filter off as a result. If you have a 3rd party monitoring app that constantly polls the server, this would be a top wait on the instance. Using Activity Monitor in SSMS also polls the DMV's and would see this wait occurring. Paul and I were just discussing this last week when I was working in a client environment and could repeatedly reproduce the wait type occurring for sys.dm_exec_requests and then I could repeat it on my lab VM's so we could get additional call stacks to update the wait information library for this wait type:

https://www.sqlskills.com/help/waits/memory_allocation_ext/

GA_SQL Hall of Fame Points: 3166 More actions · Answer 8

Solarwinds is the tool showing this wait type, anyways to troubleshoot it, please help thanks in advance

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

Is it showing that wait type for it's own queries? Why do you think that it needs troubleshooting? Unless something changed with how Solarwinds collects it's data, it's constant polling of the DMV's is the most likely cause of this wait occurring and they haven't updated their app to filter it off like they do other benign waits. Grab the script from this blog post by Paul:
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
and you will see that it is one of the waits that get's filtered off as non-important and benign. Trying to troubleshoot this is chasing your tail, it's not a problem just because Solarwinds is showing it on their dashboard.

GA_SQL Hall of Fame Points: 3166 More actions · Answer 10

As per my understanding from your replies that MEMORY ALLOCATION EXT and PREEMPTIVE_XE_GETTARGETSTATE not going to be the issue, but still not able to understand the root cause for it , please explain more on the DMV's , which is not available much on internet, please help

/* (inserted by DPA)
Character Range: 0 to 272
Waiting on statement:

SELECT target_data
FROM sys.dm_xe_session_targets xet
WITH
(
nolock
)
JOIN sys.dm_xe_sessions xes
WITH
(
nolock
)
ON xes.address = xet.event_session_address
WHERE xes.name = 'telemetry_xevents'
AND xet.target_name = 'ring_buffer'

*/

/* BEGIN ACTIVE SECTION (inserted by DPA) */
SELECT target_data
FROM sys.dm_xe_session_targets xet
WITH
(
nolock
)
JOIN sys.dm_xe_sessions xes
WITH
(
nolock
)
ON xes.address = xet.event_session_address
WHERE xes.name = 'telemetry_xevents'
AND xet.target_name = 'ring_buffer'
/* END ACTIVE SECTION (inserted by DPA) */

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

Querying one of the DMVs requires access to resources. Reading the target state for an event session in the case of PREEMPTIVE_XE_GETTARGETSTATE and it's having to wait for that resource. I don't know any other way to explain it. The MEMORY_ALLOCATION_EXT wait is for a preemptive memory allocation during the execution. Every time you query the DMV it needs the resource and will wait to acquire it if it's not available so by the way DPA is designed to query DMVs in an active loop you can expect certain waits to be common that are not a problem or sign of an issue

GA_SQL Hall of Fame Points: 3166 More actions · Answer 12

Any Troubleshooting/resolution which we can apply to this wait types, please help

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

GA_SQL - Monday, June 19, 2017 8:24 AM
Any Troubleshooting/resolution which we can apply to this wait types, please help

Yes, ignore it.
As Jonathan has pointed out, it comes from acessing DMVs, meaning that monitoring tools (which access DMVs a lot) will incur this wait on their own queries, which means you should ignore it as it's not interesting, it's not hindering your actual application workload.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Youri S. Pikulin Old Hand Points: 337 More actions · Answer 14

I see a lot of waits of the type MEMORY_ALLOCATION_EXT when running DBCC CHECKDB (the lite version, NOINDEX and WITH PHYSICAL_ONLY) on the new database server.
I've tested this VM with PassMark and it shows low memory performance (1300 points against 2300 on my home PC). With simple words, we have enough memory, but it is slow.