When managing multiple SQL Server instances, programmatically retrieving memory configuration is crucial for performance monitoring and capacity planning. The memory settings fundamentally impact how SQL Server utilizes system resources.

The most reliable way to get memory settings is through the sys.configurations system view:

SELECT 
    name AS 'Configuration Option',
    value AS 'Current Value',
    value_in_use AS 'Running Value',
    description AS 'Description'
FROM sys.configurations
WHERE name IN ('min server memory (MB)', 'max server memory (MB)')

For legacy compatibility, you can also use the sp_configure stored procedure:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'min server memory';
EXEC sp_configure 'max server memory';

For environments with multiple instances, consider this PowerShell-TSQL hybrid approach:

$servers = Get-Content "C:\ServerList.txt"
$query = @"
SELECT 
    @@SERVERNAME AS ServerName,
    (SELECT value FROM sys.configurations WHERE name = 'min server memory (MB)') AS MinMemoryMB,
    (SELECT value FROM sys.configurations WHERE name = 'max server memory (MB)') AS MaxMemoryMB
"@

$servers | ForEach-Object {
    Invoke-Sqlcmd -ServerInstance $_ -Query $query -Database "master"
}

Key points about these values:

• Min memory represents the floor SQL Server will maintain once allocated
• Max memory controls the ceiling for buffer pool usage
• Settings are in megabytes (MB)
• Changes require RECONFIGURE to take effect

When managing multiple SQL Server instances (like your environment with 90 instances), it's crucial to monitor memory allocation settings. SQL Server uses two key memory parameters:

• min server memory (MB): The minimum amount of memory SQL Server will try to maintain
• max server memory (MB): The upper limit of memory SQL Server can allocate

To retrieve these values across all your instances, you'll need to query the sys.configurations system view:

SELECT 
    name AS 'Configuration Option',
    value_in_use AS 'Current Value (MB)',
    description
FROM 
    sys.configurations
WHERE 
    name IN ('min server memory (MB)', 'max server memory (MB)')
ORDER BY 
    name;

For more comprehensive monitoring, this extended script provides additional context:

SELECT 
    SERVERPROPERTY('ServerName') AS ServerName,
    SERVERPROPERTY('InstanceName') AS InstanceName,
    c.name AS ConfigName,
    c.value AS ConfiguredValue,
    c.value_in_use AS CurrentRunningValue,
    c.description,
    c.is_dynamic,
    c.is_advanced
FROM 
    sys.configurations c
WHERE 
    c.name IN ('min server memory (MB)', 'max server memory (MB)');

For environments with multiple instances, consider creating a Central Management Server (CMS) or using PowerShell to execute this query across all instances:

-- Create a linked server query for cross-instance monitoring
EXEC sp_addlinkedserver @server = 'INSTANCE2';
GO

SELECT 
    'INSTANCE2' AS InstanceName,
    name AS ConfigName,
    value_in_use AS ValueMB
FROM 
    [INSTANCE2].master.sys.configurations
WHERE 
    name IN ('min server memory (MB)', 'max server memory (MB)')
UNION ALL
SELECT 
    'LOCAL' AS InstanceName,
    name AS ConfigName,
    value_in_use AS ValueMB
FROM 
    sys.configurations
WHERE 
    name IN ('min server memory (MB)', 'max server memory (MB)');

• The value_in_use column shows the currently active setting
• The value column shows what's configured (might differ if changes require restart)
• For instances with multiple NUMA nodes, check sys.dm_os_nodes for node-specific memory
• For Azure SQL DB, use DATABASEPROPERTYEX with different parameters

Create a scheduled job that logs these values to a central repository table for trend analysis:

CREATE TABLE dbo.SQLMemoryConfigHistory (
    CollectionDate DATETIME DEFAULT GETDATE(),
    ServerName NVARCHAR(128),
    InstanceName NVARCHAR(128),
    ConfigName NVARCHAR(128),
    ConfigValue INT,
    PRIMARY KEY (CollectionDate, ServerName, ConfigName)
);

INSERT INTO dbo.SQLMemoryConfigHistory
    (ServerName, InstanceName, ConfigName, ConfigValue)
SELECT 
    SERVERPROPERTY('ServerName'),
    SERVERPROPERTY('InstanceName'),
    c.name,
    c.value_in_use
FROM 
    sys.configurations c
WHERE 
    c.name IN ('min server memory (MB)', 'max server memory (MB)');