SQL Server Query too slow missing index?

Question

I'm working with SQL Server 2012 and I have an query for the search purpose but It takes too long to execute.. (30 rows = 6sec)

I have checked up the execution plan but he doesn't suggest to put index and I don't know how i can speed up the query.

Here's my query:

SELECT  
    dbo.tbRV.ID, dbo.tbRV.IDCompagnie, 
    dbo.tbPatient.Nom + N', ' + dbo.tbPatient.Prenom AS NomPrenomPatient, 
    dbo.tbRV.DateRV, dbo.tbRV.HeureDebut, 
    dbo.tbRV.IDRessource, 
    coalesce(dbo.tbRessource.Nom ,'') + ' '  + coalesce(dbo.tbRessource.Prenom,'') AS NomPrenomRessource, 
    dbo.tbPatient.NoApp, 
    dbo.tbPatient.NomEtablissement, dbo.tbPatient.Adresse1, 
    tbAdresse.Ville, dbo.tbPatient.TelephoneDomicile, 
    dbo.tbPatient.TelephoneTravail, 
    dbo.tbPatient.TelephonePortable, 
    case 
       when DD.id is not null then DD.description 
       else dbo.tbRVObjet.IDObjet 
    end as idobjet, 
    dbo.tbPatient.Nom_SansAccent, dbo.tbPatient.Prenom_SansAccent, 
    dbo.tbPatient.Nom, dbo.tbPatient.Prenom, 
    dbo.tbRV.IDPatient, dbo.tbPatient.NoAssuranceMaladie, 
    dbo.tbPatient.DateNaissance, dbo.tbRV.Transit, dbo.tbRV.Annuler, 
    tbAdresse.Pays, tbAdresse.Province, dbo.tbPatient.Adresse2, 
    dbo.tbPatient.CodePostal,
    case 
       when AA.id is not null and AA.ID = tbrvobjet.ID then 1 else 0 end as onlyFirst
FROM  
    tbPatient 
INNER JOIN 
    tbRV ON tbPatient.ID = tbRV.IDPatient
INNER JOIN 
    tbRessource ON (tbRV.IDRessource = tbRessource.ID) AND (tbRV.IDCompagnie = tbRessource.IDCompagnie) 
INNER JOIN  
    tbRVObjet ON tbRV.ID = tbRVObjet.IDRV
OUTER APPLY
    dbo.fn_GetFirstIDRVObjet(tbrv.id) AA
OUTER APPLY (SELECT A.ID, A.Description 
             FROM tbForfaitEntete A 
             WHERE A.ID = tbRVObjet.IDForfait 
               AND ISNULL(tbRVObjet.IsForfaitEntete, 0) = 1 ) DD
OUTER APPLY (SELECT B.Description 
             FROM tbRVObjet A 
             JOIN tbForfaitEntete B ON (A.IDForfait  = B.ID AND  ISNULL(A.IsForfaitEntete, 0) = 1)
             WHERE AA.ID = A.id ) BB
OUTER APPLY (SELECT tbPaysISO.Pays, tbProvinceISO.Province, tbVilleISO.Ville 
             FROM tbPaysISO  
             JOIN tbProvinceISO ON (tbPaysISO.Langue = tbProvinceISO.Langue) 
                                AND (tbPatient.ProvinceISOChar = tbProvinceISO.ProvinceISOChar)
             JOIN tbVilleISO ON (tbProvinceISO.Langue = tbVilleISO.Langue) 
                             AND (tbPatient.IdVille  = tbVilleISO.IDVille)
             WHERE tbPaysISO.Langue = tbpatient.CodeLangue 
               AND tbPaysISO.PaysISOChar3 = tbpatient.PaysISOChar3 ) tbAdresse

and here's the execution plan in XML:

EDIT:

Here's my query plan URL: https://www.brentozar.com/pastetheplan/?id=HkVYeasNW

Here's my search query :

Select
    ID, IDPatient, NomPrenomPatient, DateNaissance, NoAssuranceMaladie, 
    NomEtablissement, Adresse1, Adresse2, NoApp, ville, province, pays, 
    CodePostal, TelephoneDomicile, TelephoneTravail, TelephonePortable,
    DateRV, HeureDebut, IDObjet, IDRessource, NomPrenomRessource, 
    Annuler, Transit 
From 
    rqAfficheRechercheRV 
Where 
    IDCompagnie = 1 
    And (isnull(NomPrenomRessource, '') + isnull(NoApp, '') + isnull(NomEtablissement, '') + isnull(Adresse1, '') + isnull(Ville, '') + isnull(TelephoneDomicile, '') + isnull(TelephoneTravail, '') + isnull(TelephonePortable, '') + isnull(IDObjet, '') + isnull(Nom_SansAccent, '') + isnull(Prenom_SansAccent, '') + isnull(Nom, '') + isnull(Prenom, '') + isnull(IDPatient, '') + isnull(NoAssuranceMaladie, '') + isnull(convert(varchar, DateNaissance), '') + isnull(Pays, '') + isnull(Province, '') + isnull(Adresse2, '') + isnull(CodePostal, '')) like '%881-1360%'  
    And isnull(onlyFirst,0) = 1 
Order by 
    Nom, Prenom, DateRV DESC, HeureDebut ASC

rqAfficheRechercheRV is the view of the query above

Answer 1

It is really very time consuming to read so long query plan.

you have to mention number of record involve.

i) Clustered Index Scan -It tell that column is CI but index is not being utilize

a) Because condition are not SARGable like

ISNULL(tbRVObjet.IsForfaitEntete, 0) = 1 
instead write `(tbRVObjet.IsForfaitEntete = 1 )`

Similarly in other places.Even if concern column is not index then also change it to this .

b) Also when there is high cardianility estimate then index is not being use.

ii) I have little doubt in this outer Apply BB query .ARe you sure you will again use tbRVObjet A inside this outer apply or you can directly join from above main tbRVObjet

iii ) Hope all the column use in join condition are int,bit,date etc.If there are varchar column that means there is problem in DB design also.

iv) when this thing really belong to view then why not concatenate it inside view itself,

And (isnull(NomPrenomRessource, '') + isnull(NoApp, '') + isnull(NomEtablissement, '') + isnull(Adresse1, '') + isnull(Ville, '') + isnull(TelephoneDomicile, '') + isnull(TelephoneTravail, '') + isnull(TelephonePortable, '') + isnull(IDObjet, '') + isnull(Nom_SansAccent, '') + isnull(Prenom_SansAccent, '') + isnull(Nom, '') + isnull(Prenom, '') + isnull(IDPatient, '') + isnull(NoAssuranceMaladie, '') + isnull(convert(varchar, DateNaissance), '') + isnull(Pays, '') + isnull(Province, '') + isnull(Adresse2, '') + isnull(CodePostal, '')) like '%881-1360%' 

I have doubts here.

v) What happen when you comment out order by,can't you do without it ?

vi) if you can put code of dbo.fn_GetFirstIDRVObjet also inside outer apply .

vii)Recheck join condition inside outer apply tbAdresse .Are you sure there is no extra join condition.I have doubt about their relationship between these table which is inside this outer apply.

viii) It is vety clear that onlyFirst is either 0 or 1 so no point in writing And isnull(onlyFirst,0) = 1 ,simply write And(onlyFirst = 1

ix) Check the diff in parenthesis here with your own

((tbRV.IDRessource = tbRessource.ID) AND (tbRV.IDCompagnie = tbRessource.IDCompagnie))

and here

((tbPaysISO.Langue = tbProvinceISO.Langue) 
                                AND (tbPatient.ProvinceISOChar = tbProvinceISO.ProvinceISOChar))
             JOIN tbVilleISO ON ((tbProvinceISO.Langue = tbVilleISO.Langue) 
                             AND (tbPatient.IdVille  = tbVilleISO.IDVille))
             WHERE tbPaysISO.Langue = tbpatient.CodeLangue 
               AND tbPaysISO.PaysISOChar3 = tbpatient.PaysISOChar3

x) You are missing dbo prefix in most of the places.Also use With (Nolock)

I think you can comment out all outer apply and their column.Run the query check the Plan,fix the index fine tune the query then start uncommneting outer apply one by one and repeat the process.

Answer 2

Make sure you have indexes on the JOIN ON fields, foreign keys, and on fields in the WHERE clause. You can create a covering index by putting fields in the SELECT in the INCLUDE (leaf level) of the index. Look at existing indexes to make sure you are not creating overlapping indexes. Try adding the following query hints to make sure your system is not CPU bottle-necked: OPTION (MAXDOP 1, RECOMPILE), that will force a serial plan. Sometimes this will give you new index recommendations as well.