一、兼容级别是什么？为什么需要关注它？
在SQLServer的世界里，兼容级别就像是一个"时光机"的开关。它决定了数据库引擎的行为应该模仿哪个版本的SQLServer。举个例子，如果你把一个数据库的兼容级别设置为120（对应SQLServer 2014），那么查询优化器就会按照2014年的规则来处理你的查询。
为什么要这么设计呢？主要是为了向后兼容。想象一下，你有一个十年前开发的应用程序，现在要迁移到最新的SQLServer版本上。如果完全按照新版本的规则运行，可能会遇到各种兼容性问题。这时候，兼容级别就派上用场了。
-- 查看当前数据库的兼容级别
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
-- 修改数据库兼容级别为SQLServer 2019 (150)
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 150;
二、不同兼容级别下的查询优化器行为差异
查询优化器是SQLServer的大脑，它决定了如何最有效地执行你的查询。在不同的兼容级别下，这个大脑会做出不同的决策。
以SQLServer 2016(130)和SQLServer 2019(150)为例，它们处理查询的方式就有显著区别：
基数估计模型：130级别使用SQLServer 2014的基数估计模型，而150级别使用更先进的模型，能更准确地预测查询将返回多少行数据。
批处理模式内存授予反馈：150级别引入了这个新特性，可以根据查询实际使用的内存动态调整内存分配。
-- 在兼容级别130和150下观察不同的执行计划
-- 先设置为130
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 130;
GO
-- 执行一个复杂查询
SELECT o.OrderID, c.CustomerName, p.ProductName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderDate > '2020-01-01'
GROUP BY o.OrderID, c.CustomerName, p.ProductName;
-- 再设置为150
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 150;
GO
-- 执行同样的查询
-- 观察执行计划的差异
三、功能差异：哪些特性受兼容级别影响？
兼容级别不仅影响查询优化，还会影响某些功能的可用性。以下是一些关键差异：
STRING_AGG函数：这个方便的函数只在兼容级别130及以上可用。
TRUNCATE TABLE与IDENTITY列：在较低兼容级别下，TRUNCATE TABLE不能用于有外键引用的表。
内存优化表：某些内存优化表功能需要特定的最低兼容级别。
-- 演示STRING_AGG在不同兼容级别下的行为
-- 设置为140(SQLServer 2017)
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 140;
GO
-- 这个查询会成功
SELECT STRING_AGG(ProductName, ', ') AS AllProducts
FROM Products;
-- 设置为110(SQLServer 2012)
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 110;
GO
-- 同样的查询会失败
-- 错误信息会告诉你STRING_AGG在这个兼容级别不可用
四、如何选择合适的兼容级别？
选择兼容级别不是简单的"越高越好"。需要考虑以下因素：
应用程序兼容性：如果你的应用程序代码依赖于特定版本的行为，可能需要保持较低的兼容级别。
性能考量：新版本的优化器通常能生成更好的执行计划，但有时也会引入回归问题。
功能需求：如果需要使用新版本的特定功能，就必须升级兼容级别。
-- 测试不同兼容级别对性能的影响
-- 创建一个测试环境
CREATE TABLE TestTable (
ID INT IDENTITY PRIMARY KEY,
DataValue VARCHAR(100),
CreateDate DATETIME DEFAULT GETDATE()
);
-- 插入测试数据
INSERT INTO TestTable (DataValue)
SELECT TOP 10000 NEWID() FROM sys.objects a CROSS JOIN sys.objects b;
-- 在兼容级别120下测试
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 120;
GO
-- 执行并观察IO和CPU消耗
SELECT * FROM TestTable WHERE DataValue LIKE 'A%';
-- 在兼容级别150下测试
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 150;
GO
-- 执行同样的查询，比较性能差异
五、升级兼容级别的注意事项
升级兼容级别是个重要决定，需要谨慎操作：
测试环境先行：永远先在测试环境中验证。
性能基准测试：升级前后进行全面的性能测试。
回滚计划：准备好快速回滚的方案。
监控期：升级后密切监控系统一段时间。
-- 升级兼容级别的标准流程示例
-- 1. 首先检查当前级别
SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();
-- 2. 检查可能的兼容性问题
-- 使用SQLServer提供的兼容性检查工具
-- 或者运行应用程序的测试套件
-- 3. 备份数据库
BACKUP DATABASE YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName.bak';
-- 4. 执行升级
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 150;
-- 5. 验证升级后行为
-- 运行关键查询和存储过程
-- 检查应用程序功能
六、常见问题与解决方案
在实际工作中，我们经常会遇到一些与兼容级别相关的问题：
查询性能下降：升级兼容级别后，某些查询可能变慢。这时候可以使用查询存储(Query Store)来识别和修复问题。
功能不可用：如果收到"此版本的SQLServer不支持此功能"的错误，可能需要检查兼容级别。
行为差异：某些函数在不同兼容级别下返回结果可能略有不同。
-- 使用查询存储解决性能回归问题
-- 启用查询存储(如果尚未启用)
ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON;
-- 升级兼容级别前
-- 确保查询存储收集了足够的数据
-- 升级后，可以比较查询性能
SELECT qsqt.query_sql_text,
qsrs_old.avg_duration AS old_avg_duration,
qsrs_new.avg_duration AS new_avg_duration
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query qsq ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs_old ON qsp.plan_id = qsrs_old.plan_id
JOIN sys.query_store_runtime_stats qsrs_new ON qsp.plan_id = qsrs_new.plan_id
WHERE qsrs_old.runtime_stats_interval_id = (
SELECT MAX(runtime_stats_interval_id) - 1
FROM sys.query_store_runtime_stats_interval
WHERE start_time < (SELECT MAX(start_time) FROM sys.query_store_runtime_stats_interval)
)
AND qsrs_new.runtime_stats_interval_id = (
SELECT MAX(runtime_stats_interval_id)
FROM sys.query_store_runtime_stats_interval
)
AND qsrs_old.avg_duration * 1.5 < qsrs_new.avg_duration -- 性能下降超过50%
ORDER BY (qsrs_new.avg_duration - qsrs_old.avg_duration) DESC;
七、总结与最佳实践
经过上面的探讨，我们可以得出以下最佳实践：
不要长期使用过低的兼容级别：虽然保持低级别可以确保兼容性，但你会错过性能改进和新功能。
有计划地升级：制定一个升级计划，逐步提高兼容级别，而不是一次性跳到最新。
全面测试：每次升级兼容级别都要进行功能测试和性能测试。
利用查询存储：这是诊断兼容级别相关性能问题的强大工具。
文档记录：记录数据库的兼容级别以及任何与级别相关的特殊考虑。
记住，兼容级别是SQLServer提供的一个强大工具，正确使用它可以在保持应用程序稳定的同时，享受到新版本带来的性能提升和功能改进。关键是要有计划、有测试、有监控地管理这个重要的数据库属性。
评论