SQL Server query is taking 8 minutes to complete. All indexes are good

Question

Below is the query that I am running. Execution plan is at the bottom. It takes 8 minutes to complete and shows 'Query cost related to batch : 84%' in query plan. I haves fixed some required non-cluster indexes but it saved hardly a minute in runtime.

Select distinct 
    ItemSolicitacao.IdItemSolicitacao As IdItemSolicitacao, 
    ItemSolicitacao.IdSolicitacao As IdSolicitacao, 
    ItemSolicitacao.IdItemAplicacao As IdItemAplicacao, 
    ItemSolicitacao.Mapeado As Mapeado, 
    ItemSolicitacao.IdFuncao As IdFuncao, 
    ItemSolicitacao.IdDepartamento As IdDepartamento, 
    ItemSolicitacao.IdDeptoGrupo As IdDeptoGrupo, 
    ItemSolicitacao.Removido As Removido, 
    ItemSolicitacao.AtividadeNome As AtividadeNome, 
    ItemSolicitacao.Acao As Acao, 
    Aplicacao.Nome As AplicacaoNome, 
    dbo.OwnersItemAplicacao(ItemSolicitacao.IdItemAplicacao, ItemSolicitacao.IdSolicitacao) As Owner, 
    case 
        when((select count(d.codigo) from ControleXerox..departamento as d 
              where d.deptoPai = Departamento.Codigo) != 0)  
           then 1 
           else 0 
    end as Grupo, 
    Departamento.Nome As DepartamentoNome, 
    DeptoOutro.Nome As DepartamentoGrupoNome, 
    Funcao.Nome As FuncaoNome, 
    TipoAplicacao.Nome As TipoAplicacaoNome, 
    TipoItemAplicacao.Nome As TipoItemAplicacaoNome, 
    ItemAplicacao.Nome As ItemAplicacaoNome 
From 
    ItemSolicitacao 
Inner Join 
    ItemAplicacao ON ItemSolicitacao.IdItemAplicacao = ItemAplicacao.IdItemAplicacao 
Inner Join 
    Aplicacao ON ItemAplicacao.IdAplicacao = Aplicacao.IdAplicacao 
Inner Join 
    TipoAplicacao ON TipoAplicacao.IdTipoAplicacao = Aplicacao.IdTipoAplicacao 
Inner Join 
    TipoItemAplicacao ON ItemAplicacao.IdTipoItemAplicacao = TipoItemAplicacao.IdTipoItemAplicacao 
left Join 
    ControleXerox..Departamento ON Departamento.Codigo = ItemSolicitacao.IdDepartamento 
left Join 
    ControleXerox..Departamento as DeptoOutro ON DeptoOutro.Codigo = ItemSolicitacao.IdDeptoGrupo 
left Join 
    ControleXerox..Funcao ON Funcao.Codigo = ItemSolicitacao.IdFuncao 
Inner Join 
    AprovadorItemAplicacao ON AprovadorItemAplicacao.IdItemAplicacao = ItemAplicacao.IdItemAplicacao 
Inner Join 
    Aprovador on AprovadorItemAplicacao.idAprovador = Aprovador.idAprovador 
              and AprovadorItemAplicacao.IdTipoAprovador = 1

https://www.brentozar.com/pastetheplan/?id=B1ND0biUQ

Please find the details of scalar function.

CREATE FUNCTION [dbo].[OwnersItemAplicacao] 
(
    @IdItemAplicacao INT
    ,@IdSolicitacao INT
)
RETURNS VARCHAR(2000)
AS
BEGIN
    DECLARE @saida VARCHAR(2000)

    SET @saida = ' '

    DECLARE @owner VARCHAR(200)

    DECLARE cur CURSOR
    FOR
    SELECT DISTINCT vc.Nome
    FROM controlexerox..grupoEstacao
    INNER JOIN controlexerox..view_colaboradores vc ON grupoEstacao.codEstacao = (
            SELECT max(e.codigo)
            FROM controlexerox..workflowItem wi
            INNER JOIN controlexerox..estacao e ON e.codWorkflowItem = wi.codigo
                AND wi.codigoItem = @IdSolicitacao
            INNER JOIN controlexerox..grupoEstacao ge ON ge.codEstacao = e.codigo
                AND ge.idItemAplicacao = @IdItemAplicacao
            )
        AND grupoEstacao.LoginS3Responsavel = vc.codigo
    ORDER BY vc.Nome;

    OPEN cur

    FETCH NEXT
    FROM cur
    INTO @owner

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @saida = @saida + CHAR(10) + @owner

        FETCH NEXT
        FROM cur
        INTO @owner
    END

    CLOSE cur

    DEALLOCATE cur

    RETURN substring(@saida, 3, len(@saida))
END

Answer 1

This code is 100% untested because I have nothing to work with. But something along these lines should let you completely eliminate that scalar function. Keep in mind that sql is a declarative language and works best with sets. Doing processing RBAR (row by agonizing row) is going to suck the life out of your server.

Give this code a shot and see if it gets you pointed the right way.

Select distinct 
    s.IdItemSolicitacao
    , s.IdSolicitacao
    , s.IdItemAplicacao
    , s.Mapeado
    , s.IdFuncao
    , s.IdDepartamento
    , s.IdDeptoGrupo
    , s.Removido
    , s.AtividadeNome
    , s.Acao
    , a.Nome As AplicacaoNome
    --, dbo.OwnersItemAplicacao(s.IdItemAplicacao, s.IdSolicitacao) As Owner
    , Owner = x.NameList
    , case when (select count(d.codigo) from ControleXerox..departamento as dept where dept.deptoPai = d.Codigo) != 0 then 1 else 0 end as Grupo,
    , d.Nome As DepartamentoNome
    , DeptoOutro.Nome As DepartamentoGrupoNome
    , f.Nome As FuncaoNome
    , Tipoa.Nome As TipoAplicacaoNome
    , Tipoia.Nome As TipoItemAplicacaoNome
    , ia.Nome As ItemAplicacaoNome 
From ItemSolicitacao s
Inner Join ItemAplicacao ia ON s.IdItemAplicacao = ia.IdItemAplicacao 
Inner Join Aplicacao a ON ia.IdAplicacao = a.IdAplicacao 
Inner Join TipoAplicacao Tipoa ON Tipoa.IdTipoAplicacao = a.IdTipoAplicacao 
Inner Join TipoItemAplicacao Tipoia ON ia.IdTipoItemAplicacao = Tipoia.IdTipoItemAplicacao 
left Join ControleXerox..Departamento d ON d.Codigo = s.IdDepartamento 
left Join ControleXerox..Departamento as DeptoOutro ON DeptoOutro.Codigo = s.IdDeptoGrupo 
left Join ControleXerox..Funcao f ON f.Codigo = s.IdFuncao 
Inner Join AprovadorItemAplicacao Aprovadoria ON Aprovadoria.IdItemAplicacao = ia.IdItemAplicacao 
Inner Join Aprovador ON Aprovadoria.idAprovador = Aprovador.idAprovador 
    and Aprovadoria.IdTipoAprovador = 1
outer apply
(
    select NameList = STUFF((select char(10) + vc.Nome
                FROM controlexerox..grupoEstacao ge
                INNER JOIN controlexerox..view_colaboradores vc ON ge.codEstacao = 
                        (
                            SELECT max(e.codigo)
                            FROM controlexerox..workflowItem wi
                            INNER JOIN controlexerox..estacao e ON e.codWorkflowItem = wi.codigo
                                        AND wi.codigoItem = s.IdSolicitacao
                            INNER JOIN controlexerox..grupoEstacao ge ON ge.codEstacao = e.codigo
                                    AND ge.idItemAplicacao = s.IdItemAplicacao
                        )
                    AND grupoEstacao.LoginS3Responsavel = vc.codigo
                group by vc.Nome
                ORDER BY vc.Nome
                FOR XML PATH('')), 1, 1, '')
) x