1

Running SQL Server 2008R2 STD 64bit edition on Windows 2008 R2 64 bit edition.

The Windows server is a Hyper-V VM with its memory set as follows

enter image description here

When I run the following query;

SELECT
[physical_memory_in_bytes] AS [PhysMemBytes],
[physical_memory_in_use_kb] AS [PhysMemInUseKB],
[available_physical_memory_kb] AS [PhysMemAvailKB],
[locked_page_allocations_kb] AS [LPAllocKB],
[max_server_memory] AS [MaxSvrMem],
[min_server_memory] AS [MinSvrMem]
FROM
sys.dm_os_sys_info
CROSS JOIN
sys.dm_os_process_memory
CROSS JOIN
sys.dm_os_sys_memory
CROSS JOIN (
SELECT
[value_in_use] AS [max_server_memory]
FROM
sys.configurations
WHERE
[name] = 'max server memory (MB)') AS c
CROSS JOIN (
SELECT
[value_in_use] AS [min_server_memory]
FROM
sys.configurations
WHERE
[name] = 'min server memory (MB)') AS c2

It gives the output below;

enter image description here

Task Manager shows the same 1Gb in use for the sqlserver.exe process

I do not understand why SQL Server is not grabbing the minimum or maximum memory set in SQL server when the VM is configured with dynamic memory and able to grow to 16Gb?

EDIT

First lines of error log below

LogDate ProcessInfo Text
2018-06-17 06:18:21.660 Server  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) 
Mar 26 2015 21:18:04 
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
2018-06-17 06:18:21.680 Server  (c) Microsoft Corporation.
2018-06-17 06:18:21.680 Server  All rights reserved.
2018-06-17 06:18:21.680 Server  Server process ID is 1684.
2018-06-17 06:18:21.680 Server  System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
2018-06-17 06:18:21.680 Server  Authentication mode is MIXED.
2018-06-17 06:18:21.680 Server  Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2018-06-17 06:18:21.690 Server  This instance of SQL Server last reported using a process ID of 1504 at 17/06/2018 6:16:11 AM (local) 16/06/2018 8:16:11 PM (UTC). This is an informational message only; no user action is required.
2018-06-17 06:18:21.690 Server  Registry startup parameters: 
 -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
 -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
 -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2018-06-17 06:18:21.700 Server  SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2018-06-17 06:18:21.700 Server  Detected 2 CPUs. This is an informational message; no user action is required.
2018-06-17 06:18:22.040 Server  Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2018-06-17 06:18:22.350 Server  Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2018-06-17 06:18:22.550 spid7s  Starting up database 'master'.
2018-06-17 06:18:22.710 spid7s  Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2018-06-17 06:18:22.910 spid7s  FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2018-06-17 06:18:23.000 spid7s  SQL Trace ID 1 was started by login "sa".
2018-06-17 06:18:23.000 spid7s  Starting up database 'mssqlsystemresource'.
2018-06-17 06:18:23.030 spid7s  The resource database build version is 10.50.4042. This is an informational message only. No user action is required.
2018-06-17 06:18:23.370 spid10s Starting up database 'model'.
2018-06-17 06:18:23.370 spid7s  Server name is 'DB01'. This is an informational message only. No user action is required.
2018-06-17 06:18:23.630 spid10s Clearing tempdb database.
2018-06-17 06:18:23.750 Server  A self-generated certificate was successfully loaded for encryption.
2018-06-17 06:18:23.750 Server  Server is listening on [ 'any' <ipv6> 1433].
2018-06-17 06:18:23.750 Server  Server is listening on [ 'any' <ipv4> 1433].
2018-06-17 06:18:23.760 Server  Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2018-06-17 06:18:23.760 Server  Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2018-06-17 06:18:23.760 Server  Server is listening on [ ::1 <ipv6> 1434].
2018-06-17 06:18:23.760 Server  Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-06-17 06:18:23.760 Server  Dedicated admin connection support was established for listening locally on port 1434.
2018-06-17 06:18:23.800 Server  The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/DB01 ] for the SQL Server service. 
2018-06-17 06:18:23.800 Server  The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/DB01:1433 ] for the SQL Server service. 
2018-06-17 06:18:23.800 Server  SQL Server is now ready for client connections. This is an informational message; no user action is required.
2018-06-17 06:18:23.990 spid12s A new instance of the full-text filter daemon host process has been successfully started.
2018-06-17 06:18:24.050 spid12s Starting up database 'msdb'.
2018-06-17 06:18:24.050 spid18s Starting up database 'SQL_D'.
    2018-06-17 06:18:24.050 spid16s Starting up database 'INVOICES'.
2018-06-17 06:18:24.050 spid14s Starting up database 'BAS'.
2018-06-17 06:18:24.050 spid17s Starting up database 'COLLEC'.
2018-06-17 06:18:24.250 spid10s Starting up database 'tempdb'.
2018-06-17 06:18:24.370 spid15s The Service Broker protocol transport is disabled or not configured.
2018-06-17 06:18:24.370 spid15s The Database Mirroring protocol transport is disabled or not configured.
2018-06-17 06:18:24.550 spid15s Service Broker manager has started.
2018-06-17 06:18:24.640 spid17s Recovery is writing a checkpoint in database 'COLLEC' (8). This is an informational message only. No user action is required.
2018-06-17 06:18:26.160 spid12s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
    2018-06-17 06:18:27.970 spid7s  Recovery is complete. This is an informational message only. No user action is required.
2018-06-17 06:18:28.060 Logon   Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection made using Windows authentication. [CLIENT: <local machine>]
2018-06-17 06:18:29.430 spid51  Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:18:29.480 spid51  Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2018-06-17 06:18:29.640 Logon   Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection made using Windows authentication. [CLIENT: <local machine>]
2018-06-17 06:18:29.930 spid51  Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:18:30.000 spid51  Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2018-06-17 06:18:30.440 spid51  Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:18:31.240 spid51  Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2018-06-17 06:22:26.230 spid70  Configuration option 'blocked process threshold (s)' changed from 30 to 30. Run the RECONFIGURE statement to install.
2018-06-17 06:22:26.230 spid70  FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2018-06-17 06:22:27.060 spid70  Attempting to load library 'odsole70.dll' into memory. This is an informational message only. No user action is required.
2018-06-17 06:22:27.900 spid70  Using 'odsole70.dll' version '2009.100.1600' to execute extended stored procedure 'sp_OACreate'. This is an informational message only; no user action is required.
Improve this question
4
  • Stop looking at task manager its a liar, it will not show correct utilization if SQL Server service account has LPIM privilege.
    – Shanky
    Jun 18, 2018 at 9:57
  • How big is the database ?, Can you try running multiple select * queries on the server and see if memory increases. Can you also add output of sp_readerrorlog the top 100 lines should do. This will tell us about how SQL Server is configured and licensing.
    – Shanky
    Jun 18, 2018 at 10:02
  • Thanks Shanky. I also used process explorer and still cannot see any other processes that get anywhere near the maximimum memory or minimum memeory settings.
    – Ian
    Jun 18, 2018 at 22:47
  • I have aqdded the error log to the post, the databases are only around 30G in total and runing multiple select * statements hasn't caused the memory to increase.
    – Ian
    Jun 18, 2018 at 23:02

3 Answers 3

Reset to default
2

Are you aware that you have just 5.2 GB allocated to windows machine. Look at the below output and the column PhysicalMemBytes which you get from DMV sys.dm_os_sys_info is Total physical memory present on windows machine in bytes and this comes to approx 5.2 GB.

enter image description here

Out of this 5GB the windows OS and other components would be using 4GB and hence only 1 GB is left for the SQL Server. This is the reason SQL Server memory consumption cannot move beyond 1 GB. There is no point in keeping max server memory to 10GB and min server memory to 6 GB because RAM for SQL Server is not there. I would suggest add more RAM on the system.

Improve this answer
3
  • 1
    Exactly what I has observed too after simplifying the values.
    – John K. N.
    Jun 19, 2018 at 8:32
  • @shanky - So my quiestion is why does it only have 5GB when at the Hyper-V level, and as per the screen shot in the original post, it is configured with Dynamic memory and maximum RAM og 16G? I would expect this to be honoured when SQL server Maximum memeory is set to 10G and that it would grow to use that 10G? I presume something must be preventing this at the Hyper-V level maybe?
    – Ian
    Jun 19, 2018 at 22:56
  • @Ian I agree this is something mis-configured from Hyper V level, I am sorry I do not have much experience with Hyper V's so I would request you to consult your team
    – Shanky
    Jun 20, 2018 at 6:51
2

Simplifying Your Situation

If I take all the values for memory that you supplied in your question and convert them all to a GB value, then we are presented with the following situation.

Input

SELECT 
-- Convert all values to GB
/*Bytes*/   5611511808  / 1024 / 1024 / 1024    AS PhysMemGB, 
/*kBytes*/  1116492     / 1024 / 1024           AS PhysMemInUseGB,
/*kBytes*/  1187560     / 1024 / 1024           AS PhysMemAvailGB,
/*MBytes*/  10240       / 1024                  AS MaxSvrMemGB,
/*MBytes*/  6144        / 1024                  AS MinSrvrMemGB

Output

PhysMemGB | PhysMemInUseGB | PhysMemAvailGB | MaxSvrMemGB | MinSrvrMemGB
----------+----------------+----------------+-------------+--------------
5.22612.. | 1              | 1              | 10          | 6

That leaves us with a slightly different awareness of your situation. Your "virtual" server has currently been assigned 5.2 GB RAM of which 1 GB is still available.

Explanation of Values You Are Retrieving

physical_memory_in_bytes (PhysMemGB)

Specifies the total amount of physical memory on the machine. Not nullable.

Reference: sys.dm_os_sys_info (Transact-SQL) (Microsoft Docs)
The above value is an Operating System value.

physical_memory_in_use_kb (PhysMemInUseGB)

Indicates the process working set in KB, as reported by operating system, as well as tracked allocations by using large page APIs. Not nullable.

Reference: sys.dm_os_process_memory (Transact-SQL) (Microsoft Docs)
The above value is a SQL Server Instance value.

available_physical_memory_kb (PhyMemAvailGB)

Size of physical memory available, in KB.

Reference: sys.dm_os_sys_memory (Transact-SQL) (Microsoft Docs)
The above is an Operating System value.

max server memory(MB) (MaxSvrMemGB)

Use max_server_memory to guarantee the OS does not experience detrimental memory pressure. To set max server memory configuration, monitor overall consumption of the SQL Server process in order to determine memory requirements. To be more accurate with these calculations for a single instance:

  • From the total OS memory, reserve 1GB-4GB to the OS itself.
  • Then subtract the equivalent of potential SQL Server memory allocations outside the max server memory control, which is comprised of stack size 1 * calculated max worker threads 2 + -g startup parameter 3 (or 256MB by default if -g is not set). What remains should be the max_server_memory setting for a single instance setup.

(Emphasis mine)
Reference: Server Memory Server Configuration Options (Microsoft Docs)

min server memory(MB) (MinSrvMemGB)

Use min_server_memory to guarantee a minimum amount of memory available to the SQL Server Memory Manager for an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory unless the value of min server memory is reduced. For example, when several instances of SQL Server can exist concurrently in the same host, set the min_server_memory parameter instead of max_server_memory for the purpose of reserving memory for an instance. Also, setting a min_server_memory value is essential in a virtualized environment to ensure memory pressure from the underlying host does not attempt to deallocate memory from the buffer pool on a guest SQL Server virtual machine (VM) beyond what is needed for acceptable performance.

(Emphasis mine)
Reference: Server Memory Server Configuration Options (Microsoft Docs)

Analysing Your Situation

  • Your OS has 5.2 GB of virtual RAM assigned.
  • The SQL Server is currently using 1 GB of RAM.
  • The OS has 1 GB of free RAM to assign to a process.
  • Your SQL Server instance has not reached the min server memory(MB) setting and will still release memory to the OS if the OS requires more memory.

If you look at the graphic in Task Manager, is your server consuming 5.2 GB of RAM in total? Try looking the process details and calculate the sum of all processes.

Assumptions

  • Your SQL Server instance will possibly never reach the max server memory(MB) setting due to the fact that the "virtual" OS memory has been set to dynamic and the Windows Server (containing the SQL Server instance) is probably competing with other "virtual" servers on the same machine for RAM.

  • Your virtual environment might have 64 GB of physical RAM but adding up all the virtual machines and the RAM configurations is resulting in an over commitment of for example 100% (e.g. you have assigned 128 GB RAM to the virtual servers but only have 64 GB of physical RAM).

Determining Memory Pressure

You can have a look at the following counters to determine if your single SQL Server instance is under memory pressure:

  • High number of Checkpoint pages/sec
  • High number of Lazy writes/sec
  • High number of Page reads/sec
  • Low Buffer cache hit ratio
  • Low Page Life Expectancy

Your SQL Server instance might be wanting to consume more RAM but is unable to do so due to the configuration of the "virtual" RAM.

Reference: How to Identify Microsoft SQL Server Memory Bottlenecks (MSSQLTips.com)

Workarounds

  1. Try to configure the "virtual" minimum value of the Windows Server's RAM to be 8 GB. This is the min server memory(MB) setting of 6 GB with an additional 2 GB or RAM for the OS. Don't forget to set the "virtual" maximum value to 18 GB (SQL Server max server memory(MB) + 2 GB for the OS).

  2. Try to configure the "virtual" Windows Server to use a Static RAM. Set the value to 18 GB.

Final Statement

SQL Server likes to consume lots of memory...

...if it can. You have to give the SQL Server instance the ability to consume as much RAM as possible. Try the workarounds to see if you can increase your SQL Server instance's memory consumption.

If you need more proof, read some of the articles I provided in the Addtional Resources section of this answer.

Additional Resources

Improve this answer
3
  • thanks for the detaioled repsonse. As per my comment above to Shnky, I dont understand why it only has 5GB when at the Hyper-V level, and as per the screen shot in the original post, it is configured with Dynamic memory and maximum RAM og 16G? I would expect this to be honoured when SQL server Maximum memeory is set to 10G and that it would grow to use that 10G? I presume something must be preventing this at the Hyper-V level maybe?
    – Ian
    Jun 19, 2018 at 22:58
  • Please read my assumptions about overcommitment. Just because you set a higher RAM setting for your server doesn’t mean your server will receive that amount of memory. Did you try setting a higher value? Observations?
    – John K. N.
    Jun 20, 2018 at 5:38
  • You could try shutting down other servers to see if your DB server is then able to use more memory and then check the memory usage of the SQL Server instance.
    – John K. N.
    Jun 20, 2018 at 5:42
1

So it turns out that SQL Server 2008R2 Standard Edition does not support Dynamic memory (Hot add memory) as per note Here

This is why SQL server is only using the 1Gb that is allocated as the startup RAM and cannot grow as the Dynamic memory grows.

As per the suggestion from hot2use, I will get Hyper-V configured to use a Static RAM Value

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.