SqlServer SQL语句或存储过程运行慢 使用 WITH RECOMP ILE 或 OPTION (RECOMPILE)（重新编译）

如果您的存储过程包含参数可以重新申明变量把参数接收下，可能解决你过程执行慢的原因。如果未能解决，请参考以下文章内容：

WITH RECOMPILE 子句可以在以下地方使用：

一种是当你创建一个过程时，例如：

CREATE PROCEDURE MySP WITH RECOMPILE AS
这指示 SQL Server 在每次调用时重新编译整个过程，并且从不缓存该计划。

另一种情况是当你调用一个过程时：

EXEC MySP WITH RECOMPILE
这告诉 SQL Server 重新编译此调用的过程，绕过缓存中的内容。我不知道新计划是否存储在缓存中，或者是否被丢弃。

这两个都是至少从 SQL Server 4.x 开始就存在于产品中的旧功能。

从SQL 2005开始，有一个更好的选择，那就是语句级提示OPTION(RECOMPILE)：

SELECT ...
FROM   tbl
WHERE Id = @id OR @id NULL
OPTION (RECOMPILE)
        这告诉 SQL Server 每次执行此语句时都重新编译它。这比 WITH RECOMPILE 更强大，原因有二。一是如果这是过程中唯一一个每次都需要重新编译的语句，则通过不重新编译其余部分可以节省资源。但真正强大的是 @id 可以作为常量处理，因此当您有 @id 时可以进行索引查找，而当您没有 @id 时可以进行表扫描。使用 WITH RECOMPILE 无法获得这种效果，因为编译发生在过程启动时，并且 @id 的运行时值未知。

        因此，WITH RECOMPILE 和 CREATE PROCEDURE 实际上是一个已停用的功能，您很少（如果有的话）有理由使用它。我认为 EXEC WITH RECOMPILE 从未被广泛使用过。

选项（重新编译）：
首先，让我们创建一个包含关键字OPTION（RECOMPILE）的存储过程。

CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT)
AS
SELECT *
FROM WideWorldImporters.Sales.Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE)

现在在 SQL Server Management Studio (SSMS) 中为查询窗口启用执行计划。

接下来，让我们使用两个不同的参数运行以下两个存储过程。

EXEC GetCustomerOrders 1060
EXEC GetCustomerOrders 90
GO

        当我们看到查询的执行计划时，您会注意到，这次执行计划对行有一个正确的估计，因此查询正在消耗优化特定执行计划所需的资源。

        当查询需要更多资源时，它会占用更多资源，当需要更少资源时，它会使用适当的资源。这是一个很大的调整，SQL Server 每次执行存储过程时都会进行调整。 

参考：SQL SERVER - Parameter Sniffing and OPTION (RECOMPILE) - SQL Authority with Pinal Dave

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

使用 Transact-SQLUsing Transact-SQL：

1、连接到数据库引擎。

2、从标准栏中选择“新建查询”。

3、将以下示例复制并粘贴到查询窗口中，然后选择“执行”。此示例创建过程定义。

USE AdventureWorks2022;  
GO  
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL   
    DROP PROCEDURE dbo.uspProductByVendor;  
GO  
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'  
WITH RECOMPILE  
AS  
    SET NOCOUNT ON;  
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'  
    FROM Purchasing.Vendor AS v   
    JOIN Purchasing.ProductVendor AS pv   
      ON v.BusinessEntityID = pv.BusinessEntityID   
    JOIN Production.Product AS p   
      ON pv.ProductID = p.ProductID  
    WHERE v.Name LIKE @Name;  
    
使用 WITH RECOMPILE 选项重新编译存储过程
选择“新建查询”，然后将以下代码示例复制并粘贴到查询窗口中，然后选择“执行”。这将执行该过程并重新编译该过程的查询计划。

USE AdventureWorks2022;  
GO  
EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;  
GO

使用 sp_recompile 重新编译存储过程
选择“新建查询”，然后将以下示例复制并粘贴到查询窗口中，然后选择“执行”。这不会执行该过程，但它会标记要重新编译的过程，以便在下次执行该过程时更新其查询计划。

USE AdventureWorks2022;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO 

参考：Recompile a Stored Procedure - SQL Server | Microsoft Learn 

OPTION（RECOMPILE）总是更快；为什么？

        有时使用OPTION(RECOMPILE)是有意义的。根据我的经验，唯一可行的选择是使用动态 SQL。在探索这是否适合您的情况之前，我建议您重建统计信息。这可以通过运行以下命令来完成：

EXEC sp_updatestats
然后重新创建执行计划。这将确保在创建执行计划时将使用最新信息。

OPTION(RECOMPILE)每次执行查询时都会重新构建执行计划。我从未听说过这样的描述，但creates a new lookup strategy也许我们只是对同一件事使用了不同的术语。

        创建存储过程时（我怀疑您正在从 .NET 调用临时 SQL，但如果您使用参数化查询，那么这最终将成为存储过程调用）SQL Server 尝试根据数据库中的数据和传入的参数确定此查询的最有效执行计划（参数嗅探），然后缓存该计划。这意味着，如果您在数据库中有 10 条记录的情况下创建查询，然后在有 100,000,000 条记录时执行该查询，则缓存的执行计划可能不再是最有效的。

        总结一下 - 我看不出有任何理由OPTION(RECOMPILE)会在这里带来好处。我怀疑你只需要更新你的统计数据和执行计划。重建统计数据可能是 DBA 工作的一个重要部分，具体取决于你的情况。如果在更新统计数据后仍然遇到问题，我建议发布两个执行计划。

        OPTION (RECOMPILE)为什么更快，我想说，每次执行查询时重新编译执行计划的最佳选择是非常不寻常的。

参考：sql - OPTION (RECOMPILE) is Always Faster; Why? - Stack Overflow