SQL Server: el procedimiento almacenado se vuelve muy lento, la consulta SQL sin procesar aún es muy rápida

Question

Jun 03, 2014, 03:15 PM

performance stored-procedures sql-server sql

SQL Server: el procedimiento almacenado se vuelve muy lento, la consulta SQL sin procesar aún es muy rápida

Estamos luchando con un problema extraño: un procedimiento almacenado se vuelve extremadamente lento cuando el SQL sin procesar se ejecuta con bastante rapidez.

Tenemos

SQL Server 2008 R2 Express Edition SP1 10.50.2500.0 con varias bases de datos en él.una base de datos (su tamaño es de alrededor de 747Mb)un procedimiento almacenado que toma diferentes parámetros y selecciona entre múltiples tablas de la base de datos.

Código:

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

El procedimiento almacenado funcionaba bastante bien y rápido (su ejecución usualmente tomó 1-2 segundos).

Ejemplo de llamada

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'

Básicamente durante la ejecución del procedimiento almacenado, las 3 consultas SQL ejecutadas, la primeraSelect Into la consulta lleva el 99% del tiempo.

Esta consulta es

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))

Este procedimiento almacenado, después de una actualización masiva de datos en la base de datos (muchas tablas y filas se vieron afectadas por la actualización, sin embargo, el tamaño de la base de datos casi no cambió, ahora es 752) funciona extremadamente lento. Ahora toma de 20 a 90 segundos.

Si tomo una consulta SQL sin procesar del procedimiento almacenado, se ejecuta dentro de 1-2 segundos.

Hemos intentado:

el procedimiento almacenado se crea con parámetros

SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON

recrear el procedimiento almacenado con parámetrowith recompile

ejecutar el procedimiento almacenado después de purgar la cachéDBCC FREEPROCCACHEmover parte de las cláusulas where a la parte de unióntablas reindexactualizar estadísticas para las tablas de la consulta usando declaraciones comoUPDATE STATISTICS Media WITH FULLSCAN

Sin embargo, la ejecución del procedimiento almacenado todavía es >> 30 segundos.

Pero si ejecuto la consulta SQL que genera el SP, se ejecuta durante menos de 2 segundos.

Comparé los planes de ejecución para SP y para el SQL sin formato, son bastante diferentes. Durante la ejecución de RAW SQL, el optimizador usa Merge Joins, pero cuando ejecutamos SP, usa Hash Match (Inner Join), como si no hubiera índices.

Plan de ejecución para RAW SQl - Rápido Plan de ejecución para SP: lento

Si alguien sabe qué podría ser, por favor ayuda. ¡Gracias por adelantado!