Locked Pages, Working Set, and Private Bytes

One of the great things about writing presentations is that it spurs you to “clean up” your definitions. When it comes to writing a slide about something, I ask myself, “Do I really know what that is?” I check my assumptions, and clarify how I think about something.

This week I was working with SQL Server memory settings, and I “cleaned up” my understanding of the following definitions.

Locked Pages in Memory (setting)

“Locked pages” must be kept in physical memory

  • This memory can’t be “paged out”
  • In other words, disk space (aka the Windows Page File), can’t be swapped in for this memory

Working Set of a process

For a process in Windows, this shows…

  • How much is physically resident in memory
  • May include some memory shared with other applications
  • Does NOT include ‘locked’ pages / large pages

Private Bytes for a process

For a process in Windows, this shows…

  • How much memory is allocated
  • This includes page file space allocated and standby list

How do I use these metrics?

My favorite way to get comfortable with these metrics is to set yourself up with a test SQL Server environment far, far away from production. Get your SQL Server using memory, download TestLimit64.exe and Process Explorer from Microsoft Sysinternals, and get to creating memory pressure and watching your metrics!

Want to know how things change when you lock pages, or change your Windows page file configuration? These tools will let you see it in action, rather than guessing.

Sources / further reading

This classic blog post by Bob Ward talks about Locked Pages for SQL Server in this classic post from 2009.

This msdn page defines what the Working Set for a process is.

This Stack Overflow question and answer compares Private Bytes, Working Set, and Virtual Bytes — and explains what’s tricky about these measurements.

If you want to go way deeper, the Windows Internals books and Mark Russinovich’s blog posts are for you!

3 thoughts on “Locked Pages, Working Set, and Private Bytes

  1. This was helpful, thank you!!!

  2. Hi Kendra, I read your post on RCSI, we had been getting tons of deadlocks on a particular select query from an application that uses the replication subscript DB as the data source. https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

    Would you be able to point out if it’s a doable/”Safe” solution when the database is the busy subscriber of multiple push transactional replication? How would RCSI affect the replication processes if we were to convert to RCSI to ease the deadlocks from the select query? Thank you!

    1. Hi Missy,

      I’m not really sure what reassurance you’re looking for here that I can give outside of that post. If you have a specific general technical question, please send it via my support form at https://sqlworkbooks.com/support/– this is an unrelated post, and I don’t want to start a comment thread on an unrelated topic here, it confuses folks.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.