2

I have Microsoft SQL server 2019 installed on my Windows Server 2019 server. The memory on the server is 512 GB. When the MSSQLSERVER service starts, MS SQL 2019 immediately consumes 100 GB of memory without load.

How to fix it?

Server srv01 where MS SQL Server 2019 Enterprise is installed and there is a problem. MSSQLSERVER service starts and immediately consumes memory without load of 100gb

min server memory (MB) 16
max server memory (MB) 200000

Server srv02 where MS SQL Server 2019 Standard is installed and there is no problem. MSSQLSERVER service starts and immediately consumes memory without load of 4-6gb

min server memory (MB) 16 max server memory (MB) 400000

Let me clarify. This behavior is without load on the problematic server. I stop the service, start it and the memory grows to 100GB without any productive load

srv01
name    SizeInBytes SizeinGb
base1   65168744448 60.693
base2   26113998848 24.321
base3   20720132096 19.297
base4   12110331904 11.279
base5   8717148160  8.118
base6   8346607616  7.773
base7   6780428288  6.315
base8   2021392384  1.883
tempdb  75497472    0.070
msdb    22347776    0.021
Test    16777216    0.016
model   16777216    0.016
master  7733248 0.007

srv02
name    SizeInBytes SizeinGb
base1   927603818496    863.898
base2   853679210496    795.051
base3   777133162496    723.762
base4   715267178496    666.144
base5   694295658496    646.613
base6   665459818496    619.758
base7   388049666048    361.399
base8   385021640704    358.579
base9   208142008320    193.847
base10  169332441088    157.703
base11  147376635904    137.255
base12  132120576000    123.047
msdb    140771328   0.131
master  33619968    0.031
model   16777216    0.016
tempdb  29506928640 27.480

On srv01:

SELECT
    COUNT(*) AS TotalPages,
    COUNT(*) * 8 / 1024 AS TotalSizeMB
FROM
    sys.dm_os_buffer_descriptors;

TotalSizeMB TotalPages
27275       3491289
PetroIvanov
  • 21
  • 2
  • Does this answer your question? Why is SQL Server consuming more server memory? – John K. N. Sep 22 '23 at 06:30
  • I have another server. On it, when the service starts, it consumes 4GB of memory without load. Why is the behavior different? There is a limit on memory usage everywhere – PetroIvanov Sep 22 '23 at 06:46
  • 2
    Welcome to the DBA.SE community. Could you add your comment to the question by clicking on [edit]? What is the memory configuration of your SQL Server instance? Right-click instance | Properties | Memory. What is your Minimum server memory (in MB) setting and your Maximum server memory (in MB) setting? How large are the database on the server? Is this a production environment? How may users access this server? It might be just a normal behaviour because of the settings and the load the SQL Server has to accommodate for. Add as much details as possible to your question. Thanks. – John K. N. Sep 22 '23 at 07:00
  • I have edited the question text Thank you – PetroIvanov Sep 22 '23 at 07:38
  • SQL Server Enterprise Edition offers additional features that can be memory-intensive, such as In-Memory OLTP and Columnstore indexes. You might wanna check configs to see if any of these features are enabled and consuming memory. Also SQL Server Enterprise Edition includes features like Buffer Pool Extension and Resource Governor that allow for dynamic memory management. Could also check those configs. – Timmetje Sep 22 '23 at 10:05
  • I installed ms sql server 2019 enterprise on another server for the test. Added the same bases. When the service starts, it consumes 2gb for me – PetroIvanov Sep 22 '23 at 10:57
  • Curious, why does it matter?...why do you consider it a problem? – J.D. Sep 22 '23 at 12:41
  • 2
  • Not sure why this is a problem, per se? 2) list the registry startup flags for the instance 3) look at any jobs that start when SQL Server starts 4) look at the memory clerks and brokers to see where the memory is being used
    • – Sean Gallardy Sep 22 '23 at 14:11