Path: csiph.com!eternal-september.org!feeder3.eternal-september.org!news.eternal-september.org!eternal-september.org!.POSTED!not-for-mail
From: Anton Shepelev <anton.txt@g{oogle}mail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Memory usage: Working set much lower than Commit size
Date: Tue, 3 Jun 2025 11:51:59 +0300
Organization: A noiseless patient Spider
Lines: 41
Message-ID: <20250603115159.77d340c71ff78daa318f55ef@g{oogle}mail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Date: Tue, 03 Jun 2025 10:52:00 +0200 (CEST)
Injection-Info: dont-email.me; posting-host="9de44a9af89e424ef36e23a6937984f4"; logging-data="4113383"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1+8kTPGRq/g6SopTuOuqChe1OexHKhHQ8g="
Cancel-Lock: sha1:fZpp86xACV2rodAyXdhwOuvrnLs=
X-Newsreader: Sylpheed 3.7.0 (GTK+ 2.24.30; i686-pc-mingw32)
Xref: csiph.com comp.databases.ms-sqlserver:2265

Hello, all

One of our clients' servers (VMWare guest) was sagging under
RAM defficiency with nearly empty dm_exec_query_stats and
dm_exec_cached_plans until we unabled Lock Pages in Memory
for it:

  https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver17

Now things look OK inisde MSSQL:

SELECT
     info.physical_memory_kb  / 1024                    AS phys_mem_mb  , -- 24 575
     info.committed_kb        / 1024                    AS committed_mb , -- 12 287
     info.committed_target_kb / 1024                    AS ci_target_mb , -- 12 288
     buf .mb                                            AS buf_size_mb  , -- 10 246
     pmem.memory_utilization_percentage                 AS mem_util_perc, --    100
     pmem.process_physical_memory_low                   AS phys_mem_low , --      0
     (  SELECT COUNT(*) FROM sys.dm_exec_query_stats  ) AS qstats_n     , -- 11 887
     (  SELECT COUNT(*) FROM sys.dm_exec_cached_plans ) AS qplans_n       -- 17 774
FROM
     sys.dm_os_sys_info       info,
     sys.dm_os_process_memory pmem,
     (    SELECT
          COUNT(*) * 8/1024 mb
          FROM sys.dm_os_buffer_descriptors
     ) buf

But /Task Manager/ shows the following for sqlservr.exe:

  Working set:    254 084 K
  Commit size: 12 855 404 K

Does it mean MSSQL has allocated 13G of RAM but is using
only 254M?  Or does the Working set somehow not include a
lot of RAM that MSSQL is using with /Lock Pages in Memory/
on?

-- 
()  ascii ribbon campaign -- against html e-mail
/\  www.asciiribbon.org   -- against proprietary attachments