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:

  1. This wait type is new to SQL Server 2012.
  2. The waits accounted in this new type already happened in previous versions of SQL Server, only that they were not accounted under any wait type.
  3. SOS_PHYS_PAGE_CACHE accounts for the time a thread waits to acquire the mutex (there is one of these mutexes per memory node) it must acquire before it allocates physical pages (via AllocateUserPhysicalPages) or before it returns those pages to the OS (via Free.) Waits on that type will only appear if your instance is using the AWE memory model. That is when you are using an SKU that supports it, and the SeLockMemoryPrivilege is enabled in the service’s account token.

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.)