1. 内存江湖里的"贫富差距"

在SQL Server的世界里，内存就像程序员手中的咖啡——总是不够分。当某个查询豪横地占据大量内存时，其他查询就像早高峰挤地铁的上班族，只能在资源争夺中苦苦挣扎。这种现象在OLTP和OLAP混合场景中尤为明显，比如电商系统的大促期间，报表查询可能把实时交易订单挤得寸步难行。

2. 现场还原：内存分配的"马太效应"

（技术栈：SQL Server 2019，兼容级别150）

-- 创建测试环境
CREATE DATABASE MemoryDemo;
GO
USE MemoryDemo;
GO

-- 创建两个业务表（订单表与日志表）
CREATE TABLE dbo.Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    ProductCode CHAR(10),
    INDEX IX_Product (ProductCode)
) WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.AuditLog (
    LogID BIGINT IDENTITY PRIMARY KEY,
    OperationType VARCHAR(20),
    Detail XML,
    CreatedTime DATETIME DEFAULT GETDATE()
);
GO

-- 模拟内存分配冲突（注意内存授予提示）
-- 大查询：全表扫描+排序
SELECT * 
FROM AuditLog 
ORDER BY CreatedTime DESC
OPTION (MAX_GRANT_PERCENT = 70); -- 强行索取70%内存配额

-- 同时运行的OLTP查询
SELECT ProductCode, COUNT(*) 
FROM Orders 
WHERE OrderDate > '2023-01-01'
GROUP BY ProductCode;

此时内存分配如同"劫贫济富"，审计日志查询抢走大部分内存后，订单统计查询可能被迫使用tempdb做哈希匹配，执行时间可能从200ms飙升到20秒。

3. 内存仲裁者的运行机制

SQL Server的内存分配遵循"饥饿游戏"规则：

  • 每个查询提交内存预算申请
  • 内存仲裁器根据可用资源分配
  • 当总需求超过实际内存时触发资源争夺

关键内存组件对比：

-- 查看内存分配情况
SELECT 
    type = CASE 
        WHEN memory_node_id = 64 THEN 'CLR'
        ELSE CAST(memory_node_id AS VARCHAR)
    END,
    pages_kb / 1024 AS MB
FROM sys.dm_os_memory_nodes
WHERE memory_node_id != 64;

4. 典型场景中的内存争夺战

###4.1 排序操作 vs 哈希匹配 当SORT操作获得大量内存时，哈希匹配可能被迫使用磁盘：

-- 内存充足的哈希匹配
SELECT o.ProductCode, COUNT(a.LogID)
FROM Orders o
JOIN AuditLog a ON o.OrderID = a.LogID % 1000
GROUP BY o.ProductCode
OPTION (HASH GROUP); -- 显式指定哈希聚合

-- 内存不足时自动降级为磁盘操作
-- 可通过跟踪标志8649观察内存授予情况

4.2 列存储索引的"内存黑洞"

列存储索引扫描可能吞噬大量内存：

-- 创建列存储索引
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Orders 
ON dbo.Orders;

-- 列存储查询的内存占用
SELECT AVG(OrderID), MAX(OrderDate)
FROM Orders
OPTION (QUERYTRACEON 649); -- 查看内存分配细节

5. 技术方案的博弈论

5.1 资源调控器（Resource Governor）

-- 创建分类函数
CREATE FUNCTION dbo.Classifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    IF APP_NAME() LIKE '%Report%'
        RETURN 'Reporting';
    RETURN 'default';
END;
GO

-- 创建资源池和工作负载组
CREATE RESOURCE POOL ReportPool WITH(
    MAX_MEMORY_PERCENT = 30,
    MIN_MEMORY_PERCENT = 10
);

CREATE WORKLOAD GROUP ReportGroup 
USING ReportPool;

ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION = dbo.Classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

5.2 索引优化的降维打击

-- 改进后的覆盖索引
CREATE INDEX IX_Orders_Covering 
ON dbo.Orders (OrderDate)
INCLUDE (ProductCode)
WITH (DATA_COMPRESSION = ROW);

-- 优化后的查询
SELECT ProductCode, COUNT_BIG(*) 
FROM Orders WITH (INDEX(IX_Orders_Covering))
WHERE OrderDate > '2023-01-01'
GROUP BY ProductCode;

6. 性能优化的双刃剑

优势：

  • 资源隔离保障关键业务
  • 减少tempdb的磁盘争用
  • 提高内存使用效率

潜在风险： ➤ 过度配置导致整体内存浪费 ➤ 工作负载分类错误引发新问题 ➤ 维护复杂度的指数级增长

7. 来自生产环境的生存指南

① 内存监控三板斧：

-- 实时内存压力
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Memory Manager%';

-- 等待统计中的内存信号
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'RESOURCE%';

-- 查询级别内存使用
SELECT * FROM sys.dm_exec_query_memory_grants;

② 动态调整策略示例：

$mem = Get-CimInstance Win32_PhysicalMemory | 
       Measure-Object -Property Capacity -Sum
$sqlMem = [math]::Round($mem.Sum/1GB * 0.8)
Invoke-Sqlcmd "EXEC sp_configure 'max server memory', $sqlMem;"

8. 技术演进与未来展望

随着智能查询处理（IQP）技术的成熟，SQL Server 2022已引入更多内存调控机制：

  • 内存分配预测算法
  • 基于机器学习的内存分配
  • 细粒度的内存回收策略

9. 总结反思：

内存分配本质是资源分配的哲学问题。DBA需要像基金经理管理资产组合那样，在性能、稳定性、成本之间寻找黄金分割点。记住：最优解永远是动态平衡的结果。