Warnings NoJoinPredicate

3 votes
1 answer
227 views
sql-server sql-server-2016 execution-plan plan-cache
                          This morning, our SQL Server 2016 started using high CPU percentage(90%) when normally is about 30%.

Reviewing cache plan with sp_blitzcache, I saw a new plan for an old query in the top of CPU time.

Full query plan is here .

This plan had a warning about no join predicate.

    (@TokenCentro nvarchar(36),@Tarjeta nvarchar(max) ,@TarjetaAux nvarchar(max) )
    SELECT   Socios.id, Socios.id AS IdSocio, Centros.id AS IDCentro, Centros.nombre,
             ISNULL(NombreWEB, Centros.Nombre) AS NombreWeb, Socios.Token, Foto, ISNULL(Socios.IdIdioma, 1) AS IdIdioma,
             AplicacionPropia, ISNULL(Socios.IdConf_Regional, 1) AS IdUnidad, Socios_Asignar_Centro.AplicacionWEB,
             CASE WHEN Socios.Email = '' THEN 1
                  ELSE CASE WHEN Socios.Email IS NULL THEN 1
                            ELSE 0
                       END
             END AS ConEmail, Centros.HorarioVisible, Centros.PerfilApp, TactilEntrenoSocio, Centros.ColorKiosco,
             Conf_Regional.PrimerDia, ISNULL(Centros.IdGrupoApp, 0) AS IdGrupoApp, Centros.requiere_LOPD,
             ISNULL(Centros.LOPD, '') AS LOPD, Socios.aceptada_lopd, Centros.IntegracionDieta, Empresas.Chat, Centros.field,
             Centros.valoracionApp, Empresas.p_Premios, Centros.Reservas, ISNULL(CustomCss, '') AS CustomCss,
             Socios_Asignar_Centro.PuedeReservar, zonasHorarias.Zona, zonasHorarias.ZonaIana,
             ISNULL(Socios.TokenExten, '') AS TokenExtern, Centros.field1, Centros.tipoServicioPush,
             Socios.Nombre AS NombreSocio, Socios.Apellidos AS ApellidosSocio, Centros_Opciones.forzarLogin
    FROM     Socios
    INNER JOIN Socios_Asignar_Centro
    ON Socios_Asignar_Centro.IdSocio = Socios.Id
    INNER JOIN Conf_Regional
    ON Conf_Regional.Id = Socios.IdConf_Regional
    INNER JOIN Centros
    ON Centros.Id = Socios_Asignar_Centro.IdCentro
       AND Centros.FechaBaja IS NULL
    INNER JOIN Empresas
    ON Empresas.Id = Centros.IdEmpresa
    INNER JOIN zonasHorarias
    ON Socios.idzonahoraria = zonasHorarias.id
    INNER JOIN Centros_Opciones
    ON Centros_Opciones.IdCentro = Centros.Id
    INNER JOIN Centros_Tactiles
    ON Centros_Tactiles.IdCentro = Centros.Id
    WHERE    Socios.tarjeta IS NOT NULL
             AND Socios.tarjeta  ''
             AND Socios.tarjeta  '0'
             AND (
                     Socios.Tarjeta = @Tarjeta
                     OR Socios.Tarjeta = @TarjetaExtend )
             AND Centros_Tactiles.Token = @Token
             AND (
                     Socios_Asignar_Centro.FechaBaja IS NULL
                     OR Socios_Asignar_Centro.FechaBaja > CONVERT(DATE, GETUTCDATE()))
    ORDER BY IdCentro, IdSocio DESC


After changing the length of the parameter from nvarchar(max) to nvarchar(50), the new plan  was normal again. However, I can not see where the problem is. 

Any ideas?

 


                        
Asked by Serafín (45 rep)
May 12, 2017, 02:57 PM
Last activity: Dec 22, 2024, 08:30 AM
Warnings NoJoinPredicate

Related Questions
