SQL Server-聚焦什么时候用OPTION(COMPILE)呢？

2021-12-06 00:03:44

上一篇我们探讨了在静态语句中使用
WHERE Column=@Param OR @Param IS NULL的问题，有对OPTION(COMPILE)的评论，那这节我们来探讨OPTION(COMPILE)的问题。

探讨OPTION(COMPILE)问题

在SQL SERVER中任何时候第一次调用存储过程时，此时存储过程将会被SQL SERVER优化且查询计划在内存中会被缓存。由于查询计划缓存，当运行相同的存储过程时，它都将使用相同的查询计划，从而无需每次运行时对同一存储过程进行优化和编译。因此，如果我们需要每天运行相同的存储过程若干次，那么可以节省大量的时间和硬件资源。

如果每次运行的存储过程中的在WHERE子句中具有相同的参数，则重复使用存储过程的相同查询计划是有意义的。但是，如果运行相同的存储过程，但是参数的值会改变呢？发生什么取决于参数的典型性。如果存储过程的参数的值从执行到执行相似，那么缓存的查询计划将正常工作，查询将按照执行最佳来。但是，如果参数不是典型的，那么被重用的缓存查询计划可能不是最优的，导致查询运行更慢，因为它使用的查询计划并不是真正为所使用的参数设计的。下面我们借助AdventureWorks2012实例数据库来用实例讲解上述所描述的情况。

DECLARE
    @AddressLine1 NVARCHAR(60) = NULL,
    @AddressLine2 NVARCHAR(60) = NULL,
    @City NVARCHAR(30) = NULL,
    @PostalCode NVARCHAR(15) = NULL,
    @StateProvinceID INT = NULL 

SET @City = 'Bothell'
SET @PostalCode = '98011'
SET @StateProvinceID = 79

DECLARE @SQL NVARCHAR(MAX),@ColumnName VARCHAR(4000),@ParamDefinition NVARCHAR(500)

SET @ColumnName = 'a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid'

SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Address AS a WHERE 1 = 1'

IF (@AddressLine1 IS NOT NULL)
    SET @SQL = @SQL + ' AND a.AddressLine1 LIKE ''%'' + @AddressLine1 + ''%'''

IF (@AddressLine2 IS NOT NULL)
    SET @SQL = @SQL + ' AND a.AddressLine2 LIKE ''%'' + @AddressLine2 + ''%'''

IF (@City IS NOT NULL)
    SET @SQL = @SQL + ' AND a.City LIKE ''%'' + @City + ''%'''

IF (@PostalCode IS NOT NULL)
    SET @SQL = @SQL + ' AND a.PostalCode LIKE ''%'' + @PostalCode + ''%'''

IF (@StateProvinceID IS NOT NULL)
    SET @SQL = @SQL + ' AND a.StateProvinceID = @StateProvinceID' 

SET @ParamDefinition = N'@AddressLine1 NVARCHAR(60),
                         @AddressLine2 NVARCHAR(60),
                         @City NVARCHAR(30),
                         @PostalCode NVARCHAR(15),
                         @StateProvinceID INT'

EXECUTE sp_executesql @SQL,@ParamDefinition,
                           @AddressLine1  = @AddressLine1,
                           @AddressLine2 = @AddressLine2,
                           @City = @City,
                           @PostalCode = @PostalCode,
                           @StateProvinceID = @StateProvinceID

GO

我们运行上述查询1次，看到查询结果如下和计划缓存次数如下：

此时我们将外部变量StateProvinceID类型修改为SMALLINT，然后再来运行查询和缓存计划，此时会出现查询计划使用次数是为2，还是出现两条次数都为1呢？

此时我们再来将动态SQL中内部变量StateProvinceID类型修改为SMALLINT，此时会出现查询计划使用次数是为3，还是出现两条，次数分别为2和1呢？

由上可知，如果我们修改外部变量参数类型不会影响查询计划缓存即会达到重用目的，若修改动态SQL内部变量参数类型则不会重用查询计划缓存。

大多数情况下，我们可能不需要担心上述问题。但是，在某些情况下，假设从查询的执行到执行的参数变化很大，则会引起问题。

如果我们确定存储过程通常运行正常，但有时运行缓慢，则很可能会看到上述问题。在这种情况下，我们可以做的是改变存储过程，并添加WITH RECOMPILE选项。

添加此选项后，存储过程将始终重新编译自身，并在每次运行时创建一个新的查询计划。当然这会消除查询计划重用的好处，但确保了每次运行查询时都使用正确的查询计划。如果存储过程中有多个查询，那么它将重新编译存储过程中的所有查询，即使那些不受非典型参数影响的查询也是如此。

讲完OPTION(COMPILE)，接下来我们讲讲如何创建高性能的存储过程。有些童鞋可能会创建如下存储过程。

CREATE PROC [dbo].[HighPerformanceExample]
(
    @AddressLine1 NVARCHAR(60) = NULL,
    @AddressLine2 NVARCHAR(60) = NULL,
    @City NVARCHAR(30) = NULL,
    @PostalCode NVARCHAR(15) = NULL,
    @StateProvinceID SMALLINT = NULL 
)
AS 
SET NOCOUNT ON

SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid 
FROM Person.Address AS a
WHERE (a.AddressLine1 = @AddressLine1 OR @AddressLine1 IS NULL) AND
      (a.AddressLine2 = @AddressLine2 OR @AddressLine2 IS NULL) AND
      (a.City = @City OR @City IS NULL) AND
      (a.PostalCode = @PostalCode OR @PostalCode IS NULL) AND
      (a.StateProvinceID = @StateProvinceID OR @StateProvinceID IS NULL)

--或者
SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid 
FROM Person.Address AS a
WHERE a.AddressLine1 = COALESCE(@AddressLine1, a.AddressLine1) AND
      a.AddressLine2 = COALESCE(@AddressLine2, a.AddressLine2) AND
      a.City = COALESCE(@City, a.City) AND
      a.PostalCode = COALESCE(@PostalCode, a.PostalCode) AND
      a.StateProvinceID = COALESCE(@StateProvinceID, a.StateProvinceID) 

--或者
SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid 
FROM Person.Address AS a
WHERE a.AddressLine1 = CASE WHEN @AddressLine1 IS NULL THEN a.AddressLine1 ELSE @AddressLine1 END
 AND  a.AddressLine2 = CASE WHEN @AddressLine2 IS NULL THEN a.AddressLine1 ELSE @AddressLine2 END
 AND  a.City = CASE WHEN @City IS NULL THEN a.City ELSE @City END
 AND  a.PostalCode = CASE WHEN @PostalCode IS NULL THEN a.PostalCode ELSE @PostalCode END
 AND  a.StateProvinceID = CASE WHEN @StateProvinceID IS NULL THEN a.StateProvinceID ELSE @StateProvinceID END 


GO

SET NOCOUNT OFF

上述无论怎样执行都将表现的非常糟糕。因为SQL SERVER不能将其很好地进行优化，如果这是由不同的参数组合产生，那么我们可能会得到一个绝对糟糕的计划。不难理解，当执行一个存储过程，并且还没有生成一个查询缓存计划。所以，管理员可能会更新统计信息或强制重新编译（或者，甚至重新启动SQL Server）来尝试解决此问题，但这些都不是最佳解决方案。OPTION(COMPILE)重新编译是个好东西，但是我们是不是像如下简单加上重新编译就可以了呢。

SELECT ...
FROM ...
WHERE ...
OPTION (RECOMPILE);

如果我们要使用重新编译，那么我们是否需要考虑以下两个问题呢？

如果我们知道一个特定的语句总是返回相同数量的行并使用相同的计划（并且我们已测试过并知道这一点），那么我们会正常创建存储过程并让计划得到缓存。

如果我们知道一个特定的语句从执行到执行是不一样的，最佳查询计划也会有所不同（我们也应该从执行多个测试样本中知道这一点），然后我们会如正常一样创建存储过程，然后使用OPTION(RECOMPILE)以确保语句的计划不会被存储过程缓存或保存。在每次执行时，存储过程将获得不同的参数，如此一来语句将在每次执行时得到一个新的计划。

上述已经描述的很明朗了，使用或者不使用重新编译的前提不过是需不需要查询计划缓存还是重新生成一个查询计划，但是我们怎么知道到底是否需要查询计划缓存呢？这就要看对创建的存储过程是否稳定了，如果稳定我们就从缓存中去取，否则使用重新编译查询。归根结底一句话概述：重新编译不稳定（可变）的计划，但为稳定（不可变）的计划，我们把它们放在缓存中重用。

为了实现这点，我们需要分析所查询的存储过程，例如在每个企业下有对应的用户，我们想象一下所呈现的UI界面，首先是所有用户，查询条件则是企业下拉框，然后是用户名或者员工工号等。当没有任何筛选条件时则走查询计划缓存，若选择企业，或者还选择了员工相关筛选条件则重新编译。类似如下存储过程。

CREATE PROC [dbo].[HighPerformanceExample]
(
    @AddressLine1 NVARCHAR(60) = NULL,
    @AddressLine2 NVARCHAR(60) = NULL,
    @City NVARCHAR(30) = NULL,
    @PostalCode NVARCHAR(15) = NULL,
    @StateProvinceID SMALLINT = NULL 
)
AS 
SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX),@ColumnName VARCHAR(4000),@ParamDefinition NVARCHAR(500),@Recompile  BIT = 1;

SET @ColumnName = 'a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid'

SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Address AS a WHERE 1 = 1'

IF (@StateProvinceID IS NOT NULL)
    SET @SQL = @SQL + ' AND a.StateProvinceID = @StateProvinceID' 

IF (@AddressLine1 IS NOT NULL)
    SET @SQL = @SQL + ' AND a.AddressLine1 LIKE @AddressLine1'

IF (@AddressLine2 IS NOT NULL)
    SET @SQL = @SQL + ' AND a.AddressLine2 LIKE @AddressLine2'

IF (@City IS NOT NULL)
    SET @SQL = @SQL + ' AND a.City LIKE @City'

IF (@PostalCode IS NOT NULL)
    SET @SQL = @SQL + ' AND a.PostalCode LIKE @PostalCode'


IF (@StateProvinceID IS NOT NULL)
    SET @Recompile = 0

IF (PATINDEX('%[%_?]%',@AddressLine1) >= 4
        OR PATINDEX('%[%_?]%', @AddressLine2) = 0)
    AND (PATINDEX('%[%_?]%', @City) >= 4
        OR PATINDEX('%[%_?]%', @PostalCode) = 0)
    SET @Recompile = 0

IF @Recompile = 1
BEGIN
    SET @SQL = @SQL + N' OPTION(RECOMPILE)';
END;

SET @ParamDefinition = N'@AddressLine1 NVARCHAR(60),
                         @AddressLine2 NVARCHAR(60),
                         @City NVARCHAR(30),
                         @PostalCode NVARCHAR(15),
                         @StateProvinceID SMALLINT'

EXECUTE sp_executesql @SQL,@ParamDefinition,
                           @AddressLine1  = @AddressLine1,
                           @AddressLine2 = @AddressLine2,
                           @City = @City,
                           @PostalCode = @PostalCode,
                           @StateProvinceID = @StateProvinceID

GO

SET NOCOUNT OFF

本节我们讲解了如何在存储过程中使用OPTION(COMPILE)，并且使得存储过程性能达到最佳，我想这是根据实际场景分析存储过程相对来说首选和最佳的方法，以至于我们不必每次都重新编译。从而给我们长期更好的可扩展性。

码农公寓

探讨OPTION(COMPILE)问题

相关文章