1. 引言：为什么需要查询重写
作为一名常年与SQLServer打交道的DBA，我经常遇到这样的场景：某些关键业务查询性能不佳，但修改原始SQL又很困难——可能是因为应用代码难以改动，或者查询来自第三方系统。这时候，查询重写技术就成了我们的救命稻草。
SQLServer本身提供了多种查询优化机制，但有时我们需要更细粒度的控制。这就是今天要介绍的基于SQLServerAudit实现的自定义查询重写插件技术。它允许我们在不修改原始查询的情况下，在服务器端对查询进行"偷梁换柱"，实现性能优化。
2. SQLServerAudit基础回顾
在深入查询重写之前，我们先快速回顾一下SQLServerAudit的基本概念。SQLServerAudit是SQLServer提供的一个强大的审计功能，可以跟踪和记录服务器或数据库级别的各种事件。
-- 创建服务器审计示例
CREATE SERVER AUDIT MyQueryAudit
TO FILE (FILEPATH = 'C:\Audits\')
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO
-- 创建数据库审计规范，捕获SELECT语句
CREATE DATABASE AUDIT SPECIFICATION MyQueryAuditSpec
FOR SERVER AUDIT MyQueryAudit
ADD (SELECT ON DATABASE::AdventureWorks BY public);
GO
-- 启用审计
ALTER SERVER AUDIT MyQueryAudit WITH (STATE = ON);
ALTER DATABASE AUDIT SPECIFICATION MyQueryAuditSpec WITH (STATE = ON);
GO
这段代码创建了一个审计，它会记录所有在AdventureWorks数据库上执行的SELECT语句。审计日志会被写入指定路径的文件中。
3. 查询重写插件架构设计
我们的查询重写插件将基于SQLServerAudit构建，整体架构如下：
- 使用SQLServerAudit捕获特定查询
- 通过扩展事件或触发器拦截查询
- 应用自定义重写规则
- 执行优化后的查询
- 返回结果给客户端
-- 创建扩展事件会话捕获SQL语句
CREATE EVENT SESSION [QueryCapture] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sql_text],'%FROM LargeTable%')))
ADD TARGET package0.event_file(SET filename=N'C:\XEvents\QueryCapture')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS);
GO
-- 启动扩展事件会话
ALTER EVENT SESSION [QueryCapture] ON SERVER STATE = START;
GO
这个扩展事件会话会捕获所有包含"FROM LargeTable"的SQL语句，为后续的重写提供基础。
4. 实现查询重写的三种方法
4.1 基于触发器的查询重写
-- 创建DDL触发器拦截特定查询
CREATE TRIGGER tr_QueryRewrite
ON ALL SERVER
FOR LOGON
AS
BEGIN
-- 检查是否是我们要拦截的应用程序
IF ORIGINAL_LOGIN() = 'ProblemAppUser'
BEGIN
-- 获取当前执行的SQL文本（简化示例，实际需要更复杂的逻辑）
DECLARE @sqltext NVARCHAR(MAX) = (SELECT text FROM sys.dm_exec_requests WHERE session_id = @@SPID);
-- 检查是否需要重写
IF @sqltext LIKE '%SELECT * FROM LargeTable WHERE Status = 1%'
BEGIN
-- 重写查询：添加索引提示和只选择必要列
SET @sqltext = REPLACE(@sqltext,
'SELECT * FROM LargeTable WHERE Status = 1',
'SELECT ID, Name FROM LargeTable WITH (INDEX(IX_Status)) WHERE Status = 1');
-- 执行重写后的查询
EXEC sp_executesql @sqltext;
-- 阻止原始查询执行
ROLLBACK;
END
END
END;
GO
这种方法适用于特定登录的查询重写，但要注意触发器对性能的影响。
4.2 基于计划指南的查询重写
-- 创建计划指南重写查询
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
-- 获取原始SQL的哈希值（示例SQL）
SELECT @stmt = N'SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID';
SELECT @params = N'@CustomerID int';
-- 创建计划指南，强制使用特定的执行计划
EXEC sp_create_plan_guide
@name = N'ForceOrderIndex',
@stmt = @stmt,
@type = N'SQL',
@module_or_batch = NULL,
@params = @params,
@hints = N'OPTION (TABLE HINT(Orders, FORCESEEK(Orders(IX_CustomerID))))';
GO
计划指南是SQLServer内置的查询重写机制，不需要额外组件，但灵活性相对较低。
4.3 基于CLR存储过程的查询重写
-- 注册包含查询重写逻辑的CLR程序集
CREATE ASSEMBLY QueryRewriterAssembly
FROM 'C:\MyAssemblies\QueryRewriter.dll'
WITH PERMISSION_SET = UNSAFE; -- 需要高级权限
GO
-- 创建CLR存储过程处理查询重写
CREATE PROCEDURE sp_QueryRewriter(@originalSQL NVARCHAR(MAX))
AS EXTERNAL NAME QueryRewriterAssembly.StoredProcedures.QueryRewriter;
GO
-- 使用示例
EXEC sp_QueryRewriter N'SELECT * FROM Products WHERE Price > 100';
GO
CLR方法提供了最大的灵活性，可以用C#等语言实现复杂的重写逻辑，但部署和维护成本较高。
5. 完整示例：优化报表查询
让我们看一个完整的示例，优化一个常见的报表查询场景。
-- 原始低效查询（经常在生产环境运行的报表查询）
SELECT
c.CustomerName,
o.OrderDate,
oi.ProductID,
p.ProductName,
oi.Quantity,
oi.UnitPrice
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
JOIN Products p ON oi.ProductID = p.ProductID
WHERE o.OrderDate BETWEEN '2022-01-01' AND '2022-12-31'
ORDER BY c.CustomerName, o.OrderDate;
-- 使用SQLServerAudit捕获这个查询后，我们可以创建以下重写逻辑
DECLARE @originalSQL NVARCHAR(MAX) = '上面那个原始查询';
DECLARE @rewrittenSQL NVARCHAR(MAX);
-- 检查是否需要重写
IF @originalSQL LIKE '%FROM Customers c%JOIN Orders o ON c.CustomerID = o.CustomerID%'
BEGIN
-- 重写策略：
-- 1. 使用WITH (NOLOCK)减少阻塞
-- 2. 只查询必要年份的数据
-- 3. 添加查询提示优化连接顺序
SET @rewrittenSQL = REPLACE(@originalSQL,
'FROM Customers c',
'FROM Customers c WITH (NOLOCK)');
SET @rewrittenSQL = REPLACE(@rewrittenSQL,
'JOIN Orders o ON c.CustomerID = o.CustomerID',
'JOIN Orders o WITH (NOLOCK) ON c.CustomerID = o.CustomerID');
SET @rewrittenSQL = REPLACE(@rewrittenSQL,
'JOIN OrderItems oi ON o.OrderID = oi.OrderID',
'JOIN OrderItems oi WITH (NOLOCK) ON o.OrderID = oi.OrderID');
SET @rewrittenSQL = REPLACE(@rewrittenSQL,
'JOIN Products p ON oi.ProductID = p.ProductID',
'JOIN Products p WITH (NOLOCK) ON oi.ProductID = p.ProductID');
-- 添加OPTION强制连接顺序
SET @rewrittenSQL = @rewrittenSQL +
' OPTION (FORCE ORDER)';
-- 执行重写后的查询
EXEC sp_executesql @rewrittenSQL;
END
ELSE
BEGIN
-- 执行原始查询
EXEC sp_executesql @originalSQL;
END
这个重写示例展示了如何在不修改应用代码的情况下，通过服务器端的重写逻辑显著提升查询性能。
6. 关联技术：查询存储与智能查询处理
SQLServer 2016引入的查询存储(Query Store)功能可以与我们的重写插件配合使用：
-- 启用查询存储
ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;
GO
-- 配置查询存储
ALTER DATABASE AdventureWorks SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 60,
MAX_STORAGE_SIZE_MB = 1024,
QUERY_CAPTURE_MODE = AUTO,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200
);
GO
-- 使用查询存储数据识别需要重写的查询
SELECT
q.query_id,
qt.query_sql_text,
rs.avg_logical_io_reads,
rs.avg_duration,
rs.count_executions
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.avg_logical_io_reads > 1000
ORDER BY rs.avg_logical_io_reads DESC;
查询存储可以帮助我们识别性能最差的查询，这些查询正是我们重写插件的最佳候选对象。
7. 应用场景分析
查询重写插件特别适用于以下场景：
- 遗留系统优化：无法修改的旧系统中有性能问题的查询
- 第三方应用：供应商提供的应用中性能低下的查询
- 紧急修复：生产环境中突然出现的性能问题需要快速修复
- A/B测试：比较不同查询版本的性能差异
- 多租户优化：为不同租户定制不同的查询策略
8. 技术优缺点评估
优点：
- 无侵入性：不需要修改应用代码
- 灵活性高：可以根据需要实现复杂的重写逻辑
- 即时生效：修改后立即对所有新查询生效
- 可针对性优化：只为特定查询或特定用户应用重写
缺点：
- 维护成本：需要维护重写规则库
- 性能开销：审计和重写过程本身有性能开销
- 复杂性：调试重写逻辑可能比较困难
- 版本兼容性：SQLServer版本升级可能需要调整实现
9. 注意事项与最佳实践
- 测试环境验证：所有重写规则应在测试环境充分验证
- 性能监控：实施后密切监控服务器整体性能
- 逐步实施：从最关键查询开始，逐步扩展重写范围
- 文档记录：详细记录每个重写规则的目的和效果
- 回退计划：准备好快速禁用重写功能的方案
- 避免过度重写：只重写真正有问题的查询
10. 总结与展望
基于SQLServerAudit的查询重写插件为我们提供了一把解决棘手性能问题的瑞士军刀。通过本文介绍的技术，我们可以在不修改应用代码的情况下，灵活地优化各种查询性能问题。
未来，随着SQLServer智能查询处理功能的不断增强，我们可以将这些原生功能与自定义重写插件相结合，构建更加智能、自适应的查询优化系统。例如，可以结合机器学习算法自动识别和推荐查询重写策略。
无论技术如何发展，理解查询执行原理和掌握问题诊断工具始终是数据库性能优化的基础。查询重写插件只是我们工具箱中的一件利器，合理使用才能发挥最大价值。
评论