SQL Server：存储过程变得非常慢，原始SQL查询还是很快的

这是好地方

当原始的时候，我们正在为一个奇怪的问题而挣扎SQL当执行相当快时，存储过程将变得非常缓慢。
" \7 b% q- I* d# L" ]我们有
SQL Server 2008 R2 Express Edition SP1 10.50.2500.上面有多个数据库。
一个数据库(大小约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          = case                      when @Rfa = ''All'' then                 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                 when @Rfa = ''Skipped'' and ar.SkipRfa = 1 then              end)                            CASE WHEN @LicenseWindow IS NOT NULL THEN         AND           = (case                     when (@LicenseWindow = 1 And (l.WindowEnd  @now))) then                 when (@LicenseWindow = 4 And ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now))) then                 when (@LicenseWindow = 3 And ((l.WindowEnd  @now)))) then                 when (@LicenseWindow = 5 And ((l.WindowEnd  @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then                 when (@LicenseWindow = 6 And ((l.TbaWindowStart = 0 and l.WindowStart  @now)) or ((l.TbaWindowStart = 1 or l.WindowStart > @now) and (l.TbaWindowEnd = 1 or l.WindowEnd > @now)))) then                 when ((@LicenseWindow = 7 Or @LicenseWindow = 0) And ((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秒）。
0 u3 R' J; ~/ {. ]" n通话示例
' h6 V. _$ W% k5 ?6 F  B8 ^DBCC FREEPROCCACHEEXEC    value = [dbo].[spGetMovieShortDataList]        @LicenseWindow =N          @Rfa = N'NotReady  @DontReturnMoviesWithNoLicense = False,       @DontReturnNotReadyMovies = True,       @take = 20，         @skip = 0，   @asc = False,       @order = N'ID'基本上，在执行存储过程中执行了三次SQL查询，第一个Select Into查询花费了99％的时间。
该查询是
declare @now DateTime;set @now = GetDate();SELECT DISTINCT    m.ID,m.EpisodNo,m.MediaID,p.Dubbed,pf.Format,t.OriginalTitleFROM Media mINNER JOIN Asset a1 ON m.ID = a1.IDINNER JOIN Asset a2 ON a1.ParentID = a2.IDINNER JOIN Asset a3 ON a2.ParentID = a3.IDINNER JOIN Title t ON t.ID = a3.IDINNER JOIN Product p ON a2.ID = p.IDLEFT JOIN AssetReady ar ON ar.AssetID = a1.IDLEFT JOIN License l on l.ProductID = p.IDLEFT 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 大量的数据更新(许多表和行受到更新的影响，但是DB大小几乎不变，现在是752)之后，存储过程变得非常缓慢。现在需要20到90秒。
如果我从存储过程中得到原始的SQL查询-它会在1-2秒内执行。
6 ^  C! Q5 {, Q; M- u4 \7 x我们试过：
: N; `0 L1 D3 R, j6 J" u  {[ol]使用参数创建存储过程[/ol]SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON
$ S) h5 c: H. Q) k' o+ w[ol]用参数重新创建存储过程 with recompile
& ^( _, e, \/ O! a, k清除产品缓存后的存储过程DBCC FREEPROCCACHE
7 _' ]; H3 S7 v. f将where子句的一部分移动到连接部分
  U! U3 z7 N1 ~4 W. X# w重新索引表
从查询中更新类似以下句子的统计信息 UPDATE STATISTICS Media WITH FULLSCAN[/ol]然而，存储过程的执行仍然存在>> 30秒。
但是，如果我运行的话SP生成的SQL查询-它执行不到2秒。
我已经比较过了SP和原始SQL的执行计划-它们完全不同。在执行中RAW SQL的过程中-优化器正在使用合并连接，但正在执行中SP时-
$ ~; V9 B& w2 A, G哈希匹配(内部连接)用于优化器，就像没有索引一样。
RAW SQl的执行计划-快速
6 B/ f; T+ F2 B" y" L: ~SP的执行计划-慢如果有人知道会发生什么-请提供帮助。提前感谢。！
* _, G9 S2 B% X5 Q* {. [/ j' a                                                               
3 F0 V5 z2 Z0 w    解决方案:                                                               
                                                                尝试使用提示OPTIMIZE FORUNKNOWN。如果可行，可能比每次强制重新编译要好。问题是，最有效的查询计划取决于日期参数的实际值。SP时，SQL
Server必须猜测将提供什么实际值，并且在这里可能会做出错误的猜测。OPTIMIZE FOR UNKNOWN解决这个确切的问题。
查询结束时，添加
' [5 C$ L7 D( }' i% Q7 \3 JOPTION (OPTIMIZE FOR (@now UNKNOWN))