Question from my class: How do I know if SQL Server is using Large Pages?

SQL Server supports Large Page Allocations if the following conditions are true:

  • You are running SQL Server Enterprise Edition
  • Your box has 8 GB RAM or more
  • “Lock Pages in Memory” privilege is set for the SQL service account (which I see is generally set for most installations)

Windows Memory Page size if 4 KB on a 64 bit system and with Large Pages, it is 2 MB.

You can find out if your SQL instance is using Large Page with the help of sys.dm_os_process_memory DMV.

SQL Server Large Pages_1

You can observe that large_page_allocations_kb column has a value 0 in which case this instance (my fresh SQL VM) is not using it. If your SQL instance is using Large Page Memory Model, even the ERRO LOG will show something like “Large Page Extensions Enabled”.