SQL Server：存储过程变得非常缓慢，原始的SQL查询仍然非常快

Question

我们正在努力解决一个奇怪的问题：当执行原始SQL非常快时，存储过程变得极其缓慢。

我们有：

- SQL Server 2008 R2 Express Edition SP1 10.50.2500.0，上面有几个数据库。 - 一个数据库（大小约为747Mb）。 - 一个存储过程，它接受不同的参数并在数据库中的多个表中进行选择。

代码：

ALTER Procedure [dbo].[spGetMovieShortDataList](
   @MediaID int = null,
   @Rfa nvarchar(8) = null,
   @LicenseWindow nvarchar(8) = null,
   @OwnerID uniqueidentifier = null,
   @LicenseType nvarchar(max) = null,
   @PriceGroupID uniqueidentifier = null,
   @Format nvarchar(max) = null,
   @GenreID uniqueidentifier = null,
   @Title nvarchar(max) = null,
   @Actor nvarchar(max) = null,
   @ProductionCountryID uniqueidentifier = null,
   @DontReturnMoviesWithNoLicense bit = 0,
   @DontReturnNotReadyMovies bit = 0,
   @take int = 10,
   @skip int = 0,
   @order nvarchar(max) = null,
   @asc bit = 1)
as 
begin
  declare @SQLString nvarchar(max);
  declare @ascending nvarchar(5);

  declare @ParmDefinition nvarchar(max);
  set @ParmDefinition = '@MediaID int,

  declare @now DateTime;
  declare @Rfa nvarchar(8),
          @LicenseWindow nvarchar(8),
          @OwnerID uniqueidentifier,
          @LicenseType nvarchar(max),
          @PriceGroupID uniqueidentifier,
          @Format nvarchar(max),
          @GenreID uniqueidentifier,
          @Title nvarchar(max),
          @Actor nvarchar(max),
          @ProductionCountryID uniqueidentifier,
          @DontReturnMoviesWithNoLicense bit = 0,
          @DontReturnNotReadyMovies bit = 0,
          @take int,
          @skip int,
          @now DateTime';

   set @ascending = case when @asc = 1 then 'ASC' else 'DESC' end  
   set @now = GetDate();
   set @SQLString = 'SELECT distinct m.ID, m.EpisodNo, m.MediaID, p.Dubbed, pf.Format, t.OriginalTitle into #temp
                FROM Media m
                inner join Asset a1 on m.ID=a1.ID
                inner join Asset a2 on a1.ParentID=a2.ID
                inner join Asset a3 on a2.ParentID=a3.ID
                inner join Title t on t.ID = a3.ID
                inner join Product p on a2.ID = p.ID
                left join AssetReady ar on ar.AssetID = a1.ID
                left join License l on l.ProductID=p.ID
                left join ProductFormat pf on pf.ID = p.Format ' 
                + CASE WHEN @PriceGroupID IS NOT NULL THEN 
                    'left join LicenseToPriceGroup lpg on lpg.LicenseID = l.ID ' ELSE '' END
                + CASE WHEN @Title IS NOT NULL THEN 
                    'left join LanguageAsset la on la.AssetID = m.ID ' ELSE '' END
                + CASE WHEN @LicenseType IS NOT NULL THEN 
                    'left join LicenseType lt on lt.ID=l.LicenseTypeID ' ELSE '' END
                + CASE WHEN @Actor IS NOT NULL THEN 
                    'left join Cast c on c.AssetID = a1.ID ' ELSE '' END
                + CASE WHEN @GenreID IS NOT NULL THEN 
                    'left join ListToCountryToAsset lca on lca.AssetID=a1.ID ' ELSE '' END
                + CASE WHEN @ProductionCountryID IS NOT NULL THEN 
                    'left join ProductionCountryToAsset pca on pca.AssetID=t.ID ' ELSE '' END
                +
                'where (
                1 = case  
                    when @Rfa = ''All'' then 1
                    when @Rfa = ''Ready'' then ar.Rfa
                    when @Rfa = ''NotReady'' and (l.TbaWindowStart is null OR l.TbaWindowStart = 0) and ar.Rfa = 0 and ar.SkipRfa = 0 then 1
                    when @Rfa = ''Skipped'' and ar.SkipRfa = 1 then 1
                end) '
                + 
                CASE WHEN @LicenseWindow IS NOT NULL THEN
                'AND 
                1 = (case 
                    when (@LicenseWindow = 1 And (l.WindowEnd < @now and l.TbaWindowEnd = 0)) then 1
                    when (@LicenseWindow = 2 And (l.TbaWindowStart = 0 and l.WindowStart < @now and (l.TbaWindowEnd = 1 or l.WindowEnd > @now))) then 1
                    when (@LicenseWindow = 4 And ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now))) then 1
                    when (@LicenseWindow = 3 And ((l.WindowEnd < @now and l.TbaWindowEnd = 0) or (l.TbaWindowStart = 0 and l.WindowStart < @now and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then 1
                    when (@LicenseWindow = 5 And ((l.WindowEnd < @now and l.TbaWindowEnd = 0) or ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then 1
                    when (@LicenseWindow = 6 And ((l.TbaWindowStart = 0 and l.WindowStart < @now and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)) or ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then 1
                    when ((@LicenseWindow = 7 Or @LicenseWindow = 0) And ((l.WindowEnd < @now and l.TbaWindowEnd = 0) or (l.TbaWindowStart = 0 and l.WindowStart < @now and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)) or ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then 1 
                end) ' ELSE '' END
                + CASE WHEN @OwnerID IS NOT NULL THEN 
                    'AND (l.OwnerID = @OwnerID) ' ELSE '' END
                + CASE WHEN @MediaID IS NOT NULL THEN 
                    'AND (m.MediaID = @MediaID) ' ELSE '' END
                + CASE WHEN @LicenseType IS NOT NULL THEN 
                    'AND (lt.Name = @LicenseType) ' ELSE '' END
                + CASE WHEN @PriceGroupID IS NOT NULL THEN 
                    'AND (lpg.PriceGroupID = @PriceGroupID) ' ELSE '' END
                + CASE WHEN @Format IS NOT NULL THEN 
                    'AND (pf.Format = @Format) ' ELSE '' END
                + CASE WHEN @GenreID IS NOT NULL THEN 
                    'AND (lca.ListID = @GenreID) ' ELSE '' END
                + CASE WHEN @DontReturnMoviesWithNoLicense = 1 THEN 
                    'AND (l.ID is not null) ' ELSE '' END
                + CASE WHEN @Title IS NOT NULL THEN 
                    'AND (t.OriginalTitle like N''%' + @Title + '%'' OR la.LocalTitle like N''%' + @Title + '%'') ' ELSE '' END
                + CASE WHEN @Actor IS NOT NULL THEN 
                    'AND (rtrim(ltrim(replace(c.FirstName + '' '' + c.MiddleName + '' '' + c.LastName, ''  '', '' ''))) like ''%'' + rtrim(ltrim(replace(@Actor,''  '','' ''))) + ''%'') ' ELSE '' END
                + CASE WHEN @DontReturnNotReadyMovies = 1 THEN 
                    'AND ((ar.ID is not null) AND (ar.Ready = 1) AND (ar.CountryID = l.CountryID))' ELSE '' END
                + CASE WHEN @ProductionCountryID IS NOT NULL THEN 
                    'AND (pca.ProductionCountryID = @ProductionCountryID)' ELSE '' END
                    +               
                ' 
                select #temp.* ,ROW_NUMBER() over (order by ';
                if @order = 'Title' 
                begin
                    set @SQLString = @SQLString + 'OriginalTitle';
                end
                else if @order = 'MediaID' 
                begin
                    set @SQLString = @SQLString + 'MediaID';
                end
                else
                begin
                    set @SQLString = @SQLString + 'ID';
                end

                set @SQLString = @SQLString + ' ' + @ascending + '
                ) rn
                into #numbered
                from #temp

                declare @count int;
                select @count = MAX(#numbered.rn) from #numbered

                while (@skip >= @count )
                begin
                    set @skip = @skip - @take;
                end

                select ID, MediaID, EpisodNo, Dubbed, Format, OriginalTitle, @count TotalCount from #numbered
                where rn between @skip and @skip + @take

                drop table #temp    
                drop table #numbered';

                execute sp_executesql @SQLString,@ParmDefinition, @MediaID, @Rfa, @LicenseWindow, @OwnerID, @LicenseType, @PriceGroupID, @Format, @GenreID, 
                    @Title, @Actor, @ProductionCountryID, @DontReturnMoviesWithNoLicense,@DontReturnNotReadyMovies, @take, @skip, @now
            end

存储过程表现良好且快速（通常执行时间为1-2秒）。

调用示例。

DBCC FREEPROCCACHE

EXEC    value = [dbo].[spGetMovieShortDataList]
        @LicenseWindow =N'1',
        @Rfa = N'NotReady',     
        @DontReturnMoviesWithNoLicense = False,
        @DontReturnNotReadyMovies = True,
        @take = 20,
        @skip = 0,
        @asc = False,
        @order = N'ID'

基本上，在执行存储过程期间，执行了3个SQL查询，第一个Select Into查询占用了99%的时间。

这个查询是：

declare @now DateTime;
set @now = GetDate();

SELECT DISTINCT 
   m.ID, m.EpisodNo, m.MediaID, p.Dubbed, pf.Format, t.OriginalTitle
FROM Media m
INNER JOIN Asset a1 ON m.ID = a1.ID
INNER JOIN Asset a2 ON a1.ParentID = a2.ID
INNER JOIN Asset a3 ON a2.ParentID = a3.ID
INNER JOIN Title t ON t.ID = a3.ID
INNER JOIN Product p ON a2.ID = p.ID
LEFT JOIN AssetReady ar ON ar.AssetID = a1.ID
LEFT JOIN License l on l.ProductID = p.ID
LEFT JOIN ProductFormat pf on pf.ID = p.Format 
WHERE
   ((l.TbaWindowStart is null OR l.TbaWindowStart = 0) 
    and ar.Rfa = 0 and ar.SkipRfa = 0)
   And (l.WindowEnd < @now and l.TbaWindowEnd = 0 )
   AND ((ar.ID is not null) AND (ar.Ready = 1) AND (ar.CountryID = l.CountryID)) 

这个存储过程在数据库大量数据更新后（许多表和行受到更新的影响，但是数据库大小几乎没有变化，现在为752），变得极其缓慢。现在需要20至90秒才能完成。

如果我从存储过程中获取原始SQL查询，则可以在1-2秒内执行。

我们已经尝试过：

1. 使用参数创建存储过程 SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON

2. 重新创建带有参数with recompile的存储过程 3. 在清除生产缓存DBCC FREEPROCCACHE之后执行存储过程 4. 将一部分where子句移到join部分 5. 重新索引表 6. 使用类似于UPDATE STATISTICS Media WITH FULLSCAN的语句更新查询表格的统计信息

然而，存储过程的执行仍然需要超过30秒。

但是，如果运行由SP生成的SQL查询，则可以在不到2秒的时间内执行。

我已经比较了存储过程和原始SQL的执行计划 - 它们非常不同。在执行原始SQL时，优化器使用合并连接，但是当我们执行存储过程时，它使用哈希匹配（内部连接），就像没有索引一样。

• 原始SQL的执行计划 - 快速
• 存储过程的执行计划 - 慢速

如果有人知道可能是什么问题，请帮忙解决。提前感谢！

- Dmitry

---

执行计划的链接无法使用。 - Dan Ling

阅读您的问题标题并查看过程定义后，最大的嫌疑对象是“参数嗅探”。尝试使用“WITH RECOMPILE”选项执行该过程。 - M.Ali

听起来像是参数嗅探问题，但 OP 说他已经尝试过 'WITH RECOMPILE'。只是好奇，当你执行原始的 SQL 时，你是用日期参数还是硬编码的值？ - Dan Ling

大家好！ 我刚刚检查了链接-它们对我来说是有效的。当您单击链接时，需要单击另一个链接。从页面：

要下载文件，请单击下面的链接： ExecutionPlan_RAW_SQL_FAST.sqlplan

是的，正如我所说，我尝试使用重新编译存储过程-但没有帮助:( 日期在执行期间分配：set @now = GetDate(); - Dmitry

Answer 1

尝试使用提示语OPTIMIZE FOR UNKNOWN。如果有效，这可能比每次强制重新编译更好。问题在于，最有效的查询计划取决于提供的实际日期参数值。编译SP时，SQL Server必须猜测将提供哪些实际值，并且在此处可能会做出错误的猜测。 OPTIMIZE FOR UNKNOWN就是为解决这个问题而存在的。

在查询末尾添加以下内容：

OPTION (OPTIMIZE FOR (@now UNKNOWN))

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

- Dan Ling

---

非常感谢！你救了我们的命 :). 我在存储过程的第一个查询后面添加了 OPTION (OPTIMIZE FOR (@now UNKNOWN, @LicenseWindow UNKNOWN))，它变得相当快。 - Dmitry

然而我有一个问题：你知道为什么以前SP能够工作，现在我需要使用这个提示吗？ - Dmitry

这取决于表中的基础数据和提供的日期参数值。要弄清楚这一点，您需要分析数据更改的性质。所有问题都围绕着 l.WindowEnd < @now 这个条件展开 - 在数据更改之前，默认生成的执行计划运行良好，但在数据更改后，显然不太好用了。 - Dan Ling

仍然作为一个出色的工具运行。从7秒降至1秒。有用且易于使用。 - Nicoara

Answer 2

OPTIMIZE FOR (@parameter1 UNKNOWN, @parameter2 UNKNOWN,...)

这对我来说非常有效。我遇到了完全相同的问题。我没有使用任何其他提示，甚至没有使用：WITH (RECOMPILE)

- Interfacer

Answer 3

你应该考虑以下选项：

1. 在使用的所有表中设置索引
2. 使用本地参数/变量
3. 在使用本地参数的每个查询末尾添加"Option (recompile)"

- Abdulkarim Suleiman

Answer 4

由于您正在使用 sp_executesql ，重新编译存储过程或清除存储过程的缓存计划将无济于事，通过 sp_executesql 执行的查询计划与存储过程的缓存计划是分别被缓存的。

您需要将查询提示 WITH (RECOMPILE) 添加到要执行的 SQL 中，或在执行之前清除该特定 SQL 的缓存：

DECLARE @PlanHandle VARBINARY(64);

SELECT  @PlanHandle = cp.Plan_Handle
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   st.text LIKE '%' + @SQLString;

DBCC FREEPROCCACHE (@PlanHandle); -- CLEAR THE CACHE FOR THIS QUERY

EXECUTE sp_executesql @SQLString,@ParmDefinition, @MediaID, @Rfa, @LicenseWindow, @OwnerID, @LicenseType, @PriceGroupID, @Format, @GenreID, 
    @Title, @Actor, @ProductionCountryID, @DontReturnMoviesWithNoLicense,@DontReturnNotReadyMovies, @take, @skip, @now;

当然，如果您执行 DBCC FREEPROCCACHE 时没有传递任何参数并清除了整个缓存，则此内容无关紧要。

- GarethD

---

感谢您的帮助。我已经使用了建议，给优化器提供了“OPTIMIZE FOR UNKNOWN”的提示，这在我的情况下解决了问题。 - Dmitry