My colleague Assaf Fraenkel asked a few days ago about that wait type he noticed was showing up in some of the instances he works with, and that he hadn’t seen before. Where that wait type is used inside the engine and what it accounts for wasn’t publicly documented, so I took a peep into the source code to understand where it was being used and why it didn’t show up before.
What I’ve found is this:
Q: Hang on Nacho… But, what do you mean when the instance uses the AWE memory model? Wasn’t it announced that “awe enabled” became deprecated back in SQL Server 2008 R2 and that 32-bit instances of SQL Server 2012 are not able to allocate more memory than that of the size of its VAS (Virtual Address Space)? Besides, the instance where I’m observing those values is 64-bit, not 32-bit.
A: And that is indeed the case. That assertion is still true. Only that, internally, when all the necessary conditions are satisfied so that a 64-bit instance of SQL Server can use locked pages, it basically uses the same set of Windows Memory Management APIs that were used by 32-bit instances in previous versions when “AWE enabled” was set to true (and LPIM was granted.) So, when a 64-bit version of SQL Server 2012 uses locked pages for the buffer pool, it is perfectly normal to see waits on that type. And, as I said, those waits also existed in previous versions of SQL, just that in those versions there wasn’t a mechanism to tell the aggregated waiting cost it put to the normal function of the instance (i.e. how many times, and for how long, a thread had to wait for this reason.)