How can I Tune this Query?

Joao 2014-09-18 21:10:47

— busca patrimonios da área
DECLARE @Data DateTime = getdate()
, @codigoReduzidoOrganograma INT = 0
, @codigoEspacoFisico INT = 1000000
, @codigoFuncionario INT = 0
, @codigoEmpresaFuncionario INT = 0

  SELECT E.CD_Mdl as codigoModelo, F.CD_CntCnb as contaContabil, K.CD_RgsEntRcr AS numeroREM, K.DT_RgsEntRcr AS dataREM, K.NR_UndGst AS 
         numeroUnidadeGestoraREM, ISNULL(K.DT_AnoEmpOrc, YEAR(K.DT_EmpOrc)) AS anoEmpenhoREM, ISNULL(C.NR_EmpOrc, K.NR_EmpOrc) AS numeroEmpenhoREM, ISNULL(C.NR_PrcAdm, K.NR_PrcAdm) AS 
         numeroProcessoAdministrativoREM, A.DT_MvmPtrJrd AS dataMovimentacao, A.NR_MvmPtrJrd AS numeroMovimentacao, C.NR_PtrJrd AS numeroPatrimonio, J.CD_Rcr AS codigoMaterial, J.CD_NtrRcr as naturezaMaterial,
         EA.CD_RdzOrn AS codigoReduzidoOrganograma, EA.CD_HstAto AS ato, A.CD_EspFscDst AS codigoEspacoFisico, ISNULL(C.DT_RgsEntRcr, K.DT_RgsEntRcr) AS 
         dataAquisicao, TF.CD_EmpSGP as codigoEmpresaFuncionario, TF.CD_Fnc as codigoFuncionario, TF.NM_Fnc AS nomeFuncionario, GETDATE() AS dataBusca, C.CD_StcPtrJrd AS situacaoPatrimonio,
         A.ID_TpoMvmPtrJrdOrg as tipoMovimentacaoOrigem, A.ID_TpoMvmPtrJrdDst as tipoMovimentacaoDestino
  FROM   SGM.T_MvmPtrJrd A
    INNER JOIN SGM.T_ItmMvmSgm B ON  (A.NR_MvmPtrJrd = B.NR_MvmPtrJrd AND A.DT_MvmPtrJrd = B.DT_MvmPtrJrd)
    INNER JOIN SGM.T_PtrJrd C ON  (B.NR_PtrJrd = C.NR_PtrJrd)
    INNER JOIN SGM.T_CmpPtrJrd D ON  (C.NR_PtrJrd = D.NR_PtrJrd AND D.CD_Itm = 1)
    INNER JOIN SGM.T_Rcr J ON  (J.CD_Rcr = D.CD_Rcr AND J.CD_NtrRcr = 2)
    LEFT JOIN SGM.V_MdlRcr E ON  (D.CD_Rcr = E.CD_Rcr AND E.DT_Ano = YEAR(@Data))
    LEFT JOIN SGM.T_MdlSgm F ON  (E.CD_Mdl = F.CD_Mdl AND F.DT_AnoMdl = E.DT_Ano AND F.CD_NtrRcr = 2)
    LEFT JOIN sgm.T_RgsEntRcr K ON  (C.CD_RgsEntRcr = K.CD_RgsEntRcr AND C.DT_RgsEntRcr = K.DT_RgsEntRcr AND C.CD_NtrRgsEntRcr = K.CD_NtrRgsEntRcr)
    LEFT JOIN sgm.T_EstAdm EA ON  (A.CD_RdzOrnDst = EA.CD_RdzOrn AND A.CD_HstAto = EA.CD_HstAto)
    LEFT JOIN sgp.T_Fnc TF ON (A.CD_EmpSGPDst = TF.CD_EmpSGP AND A.CD_FncDst = TF.CD_Fnc)
  WHERE A.DT_MvmPtrJrd <= @Data -- data da ultima movimentacao 
        AND (A.CD_RdzOrnDst = @codigoReduzidoOrganograma or @codigoReduzidoOrganograma = 0) -- area
        AND (A.CD_EspFscDst = @codigoEspacoFisico or @codigoEspacoFisico = 0) -- espaco fisico
        AND (A.CD_FncDst = @codigoFuncionario or @codigoFuncionario = 0) -- movimentado para funcionario
        AND (A.CD_EmpSGPDst = @codigoEmpresaFuncionario or @codigoEmpresaFuncionario = 0) -- empresa do funcionario
        AND (A.NR_MvmPtrJrd in (Select MAX(Y.NR_MvmPtrJrd) FROM sgm.T_ItmMvmSgm Y where Y.DT_MvmPtrJrd <= @Data and Y.NR_PtrJrd = C.NR_PtrJrd)) -- ultima movimentacao até a data parametrizada
        AND ((C.CD_StcPtrJrd <> 2) -- somente ativos
             OR (C.CD_StcPtrJrd = 2 -- ou baixados depois da data parametrizada
                 AND C.NR_PtrJrd IN (SELECT C.NR_PtrJrd
                                     FROM   SGM.T_PtrJrd C
                                       INNER JOIN SGM.T_CmpPtrJrd D ON  (D.NR_PtrJrd = C.NR_PtrJrd)
                                       INNER JOIN sgm.T_ItmBxa G ON  (G.NR_PtrJrd = D.NR_PtrJrd AND G.CD_Rcr = D.CD_Rcr AND G.ID_EstBxa = 0 AND G.CD_Itm = D.CD_Itm)
                                       INNER JOIN sgm.T_BxaPtrJrd H ON  (H.CD_BxaPtrJrd = G.CD_BxaPtrJrd AND H.DT_BxaPtrJrd = G.DT_BxaptrJrd AND H.CD_StcBxa = 1)
                                     WHERE H.DT_BxaPtrJrd > @Data)))
SQLkiwi 2014-09-19 11:33:31
What problem are you experiencing? A post-execution ("actual") plan run directly from Plan Explorer would be much more useful to us than the estimated plan.
Aaron Bertrand 2014-09-20 16:23:58
Did you consider the two missing indexes that are recommended here? Also you should update statistics on just about every table involved – cardinality is way off all over the place. I also notice the compiled version of the plan doesn't have any parameter values, so some of the estimates may be off due to parameter sniffing – you might consider adding OPTION (RECOMPILE) to the query (though it is a pretty complex plan and compilation is unlikely to be cheap).
Joao 2014-09-19 17:36:05
Here is the actual plan.

I need to speed up this query. The programmer said that he need it to run at 1/8 times that before.

.[link text]1