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!