SQL Server 使用 OPTION (RECOMPILE) 和查询存储的查询

设置
        我们正在使用 WideWorldImporters 数据库,您可以从 Github 下载【sql-server-samples/samples/databases/wide-world-importers at master · microsoft/sql-server-samples · GitHub】。我正在运行SQL Server 2017 的最新 CU【https://sqlserverbuilds.blogspot.com/】,但这适用于任何版本的查询存储(SQL Server 2016 及更高版本)和 Azure SQL 数据库。下面的代码将启用查询存储,将 QUERY_CAPTURE_MODE 设置为 ALL(要了解各种不同以及建议用于生产的内容,请查看我的查询存储设置帖子【https://blog.youkuaiyun.com/hefeng_aspnet/article/details/140130527】),然后清除查询存储中的所有内容。我通常不建议您清除查询存储,但我们正在恢复演示数据库,这是一个演示,所以我要确保我们从头开始。最后,我们将创建一个用于测试的存储过程,该存储过程使用 RECOMPILE 创建,然后完全释放过程缓存。请注意,我不建议向存储过程添加 RECOMPILE 选项 - 这意味着每次执行时都会重新编译整个存储过程。我也不建议在生产中释放过程缓存 - 这只是为了演示目的。
USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON;
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE CLEAR;
GO
 
DROP PROCEDURE IF EXISTS Sales.usp_GetOrderInfo 
GO
 
CREATE PROCEDURE Sales.usp_GetOrderInfo
(@OrderID INT)
WITH RECOMPILE
AS
BEGIN
SELECT
o.OrderID,
o.CustomerID,
o.OrderDate,
ol.Quantity,
ol.UnitPrice
FROM Sales.Orders o
JOIN Sales.OrderLines ol
ON o.OrderID = ol.OrderID
WHERE o.OrderID = @OrderID;
END
GO
 
DBCC FREEPROCCACHE;
GO

测试
        首先,执行一个临时查询(不属于存储过程的一部分,具有 OPTION (RECOMIPLE) 提示):

SELECT
i.InvoiceID,
i.CustomerID,
i.InvoiceDate,
il.Quantity,
il.UnitPrice
FROM Sales.Invoices i
JOIN Sales.InvoiceLines il
ON i.InvoiceID = il.InvoiceID
WHERE i.InvoiceID = 54983
OPTION (RECOMPILE);
GO 10

如果我们检查计划缓存,您会注意到没有证据表明该查询已执行:

SELECT
qs.execution_count,
st.text, 
qs.creation_time
FROM sys.dm_exec_query_stats AS [qs] 
CROSS APPLY sys.dm_exec_sql_text ([sql_handle]) [st]
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) [p]
WHERE [st].[text][/text] LIKE '%Sales.Invoices%';
GO

但如果我们查看查询存储,我们确实会看到以下查询:
SELECT
[qsq].[query_id], 
[qsp].[plan_id],
[qsq].[object_id],
[rs].[count_executions],
[rs].[last_execution_time],
[rs].[avg_duration],
[rs].[avg_logical_io_reads],
[qst].[query_sql_text],
TRY_CONVERT(XML, [qsp].[query_plan]) AS [QueryPlan_XML],
[qsp].[query_plan] 
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] 
ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [qst].[query_sql_text] LIKE '%Sales.Invoices%';
GO 

如果我们展开 query_sql_text 列(由于空间原因删除了中间的文本),你会看到文本包含OPTION (RECOMPILE)。这很酷。

现在让我们执行使用 RECOMPILE 创建的存储过程,然后检查计划缓存:

EXEC Sales.usp_GetOrderInfo 57302;
GO 10
 
SELECT
qs.execution_count,
st.text, 
qs.creation_time
FROM sys.dm_exec_query_stats AS [qs] 
CROSS APPLY sys.dm_exec_sql_text ([sql_handle]) [st]
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) [p]
WHERE [st].[text][/text] LIKE '%Sales.Orders%';
GO

当我们检查查询存储时,我们确实看到了查询: 

SELECT
[qsq].[query_id], 
[qsp].[plan_id],
[qsq].[object_id],
OBJECT_NAME([qsq].[object_id]) AS ObjectName,
[rs].[count_executions],
[rs].[last_execution_time],
[rs].[avg_duration],
[rs].[avg_logical_io_reads],
[qst].[query_sql_text],
TRY_CONVERT(XML, [qsp].[query_plan]) AS [QueryPlan_XML],
[qsp].[query_plan] /* nvarchar(max) */
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] 
ON [qsp].[plan_id] = [rs].[plan_id]
WHERE OBJECT_NAME([qsq].[object_id]) = 'usp_GetOrderInfo';
GO

概括
        无论在何处使用 OPTION(RECOMPILE)——在临时查询的语句级别还是在存储过程内的语句——以及在创建或执行期间在过程级别使用 RECOMPILE 选项时——查询文本、计划和执行统计信息仍会在查询存储中捕获。

参考:Queries with OPTION (RECOMPILE) and Query Store - Erin Stellato 

<think>好的,我现在需要回答用户关于SQL Server查询使用DECLARE声明变量还是直接写datetime更高效的问题。首先,我得理解这两种方法的区别适用场景。 首先,DECLARE用于声明变量,然后在查询使用变量,比如声明一个@StartDate的datetime变量,再在WHERE条件中使用它。而直接写datetime则是直接在查询中硬编码日期值,比如WHERE OrderDate >= '2023-01-01'。 接下来,考虑性能方面。可能变量直接值在SQL Server的执行计划中是否有差异?如果变量被使用,优化器可能无法准确预估变量的值,导致生成次优的执行计划。而直接使用字面量可以让优化器更准确地利用统计信息,选择更好的索引或执行策略。例如,使用变量时,如果变量值在每次执行时变化较大,可能影响执行计划的稳定性,而字面量则每次都是确定的,优化器可以针对具体值优化。 然后,考虑可维护性代码重复。使用变量可以避免重复书写相同的值,尤其在复杂查询中多次引用同一日期时,修改变量值即可,减少错误。而直接写值虽然直观,但如果同一日期在多处使用,修改时需要逐一修改,容易出错。 缓存执行计划方面,参数化查询使用变量)可能更有利于执行计划的复用,因为查询结构相同,只是参数值变化。而直接写值可能导致每次查询都被视为不同的语句,增加编译开销。但这里可能需要区分用户定义的变量参数化查询中的参数,用户可能混淆了这两者。 另外,用户的问题可能涉及到是否应该参数化查询以提高安全性,防止SQL注入。虽然直接写值在动态SQL中可能有风险,但如果是静态查询,可能不涉及这个问题,但使用变量(参数)通常是更好的做法。 需要验证变量直接值对索引使用的影响。例如,如果列上有索引,使用变量是否会导致索引失效,或者优化器是否仍然有效利用索引?可能需要查阅文档或测试案例。 总结可能的要点: 1. 性能:直接值可能让优化器更准确,变量可能导致参数嗅探问题。 2. 执行计划缓存:变量有助于重用执行计划,直接值可能每次生成新计划。 3. 可维护性:变量提高可维护性,减少重复。 4. 安全性:变量(参数化)防止SQL注入。 5. 索引使用:两者在正确使用时都应利用索引,但变量可能导致统计信息不准确。 可能还需要区分用户是在存储过程中使用变量,还是在即席查询中直接写值。存储过程中使用变量更常见,而即席查询可能直接用字面量。 最后,需要给出建议:在需要动态值或重用查询使用变量,而在简单查询或需要优化器准确优化时使用直接值,同时注意参数化查询的安全性。</think>在 SQL Server 中,使用 `DECLARE` 声明变量还是直接写 `datetime` 值,主要取决于具体场景需求。以下是两种方式的对比分析: --- ### 1. **使用 `DECLARE` 声明变量** ```sql DECLARE @StartDate DATETIME = '2023-01-01'; SELECT * FROM Orders WHERE OrderDate >= @StartDate; ``` #### **优点:** - **代码重用性**:变量可以在同一查询中多次使用,避免重复硬编码。 - **可维护性**:修改值时只需修改变量声明,无需改动多处逻辑。 - **参数化查询**:配合存储过程或动态 SQL 时,减少 SQL 注入风险。 - **执行计划复用**:SQL Server 可能缓存参数化查询的执行计划,提升后续执行效率。 #### **缺点:** - **参数嗅探问题**:如果变量值在多次调用中差异大,可能导致优化器选择次优执行计划。 - **统计信息受限**:优化器无法提前感知变量值,可能无法充分利用索引或统计信息。 --- ### 2. **直接写 `datetime` 值** ```sql SELECT * FROM Orders WHERE OrderDate >= '2023-01-01'; ``` #### **优点:** - **优化器精准优化**:优化器能基于具体值(如 `'2023-01-01'`)生成针对性更强的执行计划。 - **避免参数嗅探**:直接值每次明确,执行计划更稳定。 - **简单场景更直观**:适合简单查询或一次性使用。 #### **缺点:** - **代码重复**:同一值多次使用时需重复编写,增加维护成本。 - **执行计划冗余**:每次不同值可能生成新执行计划,增加编译开销。 - **安全性风险**:动态拼接 SQL 时直接写值可能引发 SQL 注入(需配合参数化解决)。 --- ### **性能对比** - **直接值**:可能因优化器精准利用统计信息而更快,但频繁变化的值会导致执行计划重复编译。 - **变量**:执行计划可能被复用,但若变量值分布不均(如从 `2023-01-01` 突然变为 `1990-01-01`),可能导致索引失效或扫描操作。 --- ### **建议场景** 1. **优先用变量(`DECLARE`)**: - 查询需多次调用(如存储过程)。 - 同一值需多处复用。 - 需要参数化查询以提高安全性。 2. **优先直接写值**: - 简单的一次性查询。 - 需要优化器精准优化(如复杂条件或索引覆盖)。 - 值固定且无需动态变化。 --- ### **补充优化技巧** - 若需强制优化器参考变量值,可添加 `OPTION (RECOMPILE)` 提示(牺牲编译时间换取精准计划)。 - 对于高频变化的参数,结合参数嗅探解决方案(如 `OPTIMIZE FOR UNKNOWN`)。 ```sql DECLARE @StartDate DATETIME = '2023-01-01'; SELECT * FROM Orders WHERE OrderDate >= @StartDate OPTION (RECOMPILE); -- 针对当前值生成新计划 ``` --- ### **总结** - **开发效率/维护性**:变量更优。 - **查询性能**:直接值可能更精准,但需权衡执行计划复用代价。 - 根据实际场景选择,必要时结合查询提示(`OPTION`)调优。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

csdn_aspnet

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值