SQL Server Performance – Slow SELECT with Many LEFT JOINs

I would like some advice on how I could optimize the performance of a query that is stored in a procedure.

Previously, it took 20 minutes and now we can get it in 13 minutes. But it's still a long time.

I've checked the indexes several times, updated the statistics too and got the 13 minutes running.

I also tried to replace the logic of LEFT JOIN with OUTER APPLY but performance deteriorated.

Strangely, when I removed the [ShowToUser] = 1 filter in the WHERE clause, in SSMS the query was very fast (all records have a value of 1), so I commented this line of code and I removed the indexes with this column, as I thought it unnecessary. But when processing via application, the performance was even worse!

SELECT COUNT([Id]) AS [Events],
    [ClientId],
    [PersonId],
    [FullName],
    [SuperiorId],
    [Manager],
    [ClientPhoneNumberId],
    [PhoneNumber],
    [Departament],
    [CostCenter],
    [CostCenterCode],
    [TelecomUserInitialDate],
    [TelecomUserEndDate],
    [TelecomUserId],
    [PhoneCompanyId],
    [PhoneCompanyName],
    (SUM([UserCost]) / COUNT(DISTINCT [ReferenceMonth])) AS [AverageCostMonth],
    MAX([ReferenceMonth]) AS [ReferenceMonth],
    CAST(SUM(CASE 
                WHEN [ReferenceMonth] = @referenceMonthEnd
                    THEN [UserCost]
                ELSE 0
                END) AS DECIMAL(18, 4)) AS [CostSumLastReference],
    Meta,
    ServicesValue,
    RegisteredArea,
    CASE 
        WHEN ServicesValue > 0
            THEN (
                    ServicesValue - CAST(SUM(CASE 
                                WHEN [ReferenceMonth] = @referenceMonthEnd
                                    THEN [UserCost]
                                ELSE 0
                                END) AS DECIMAL(18, 4))
                    ) * 100 / ServicesValue
        ELSE 0
        END AS ServicesPercent,
    CASE 
        WHEN Meta > 0
            THEN (
                    Meta - CAST(SUM(CASE 
                                WHEN [ReferenceMonth] = @referenceMonthEnd
                                    THEN [UserCost]
                                ELSE 0
                                END) AS DECIMAL(18, 4))
                    ) * 100 / Meta
        ELSE 0
        END AS MetaPercent
FROM (
    SELECT DISTINCT [t].[Id],
        [c].[Id] AS [ClientId],
        [p2].[Id] AS [PersonId],
        [p2].[FullName],
        [p2].[SuperiorId],
        [mg].[Fullname] AS [Manager],
        [cn].[Id] AS [ClientPhoneNumberId],
        [n].[Number] AS [PhoneNumber],
        [dp].[Name] AS [Departament],
        [tcc].[Name] AS [CostCenter],
        [tcc].[Code] AS [CostCenterCode],
        [tu].[InitialDate] AS [TelecomUserInitialDate],
        [tu].[EndDate] AS [TelecomUserEndDate],
        [a].[ReferenceMonth] AS [ReferenceMonth],
        [tu].[Id] AS [TelecomUserId],
        tu.goal AS Meta,
        tu.ServicesValue,
        tu.RegisteredArea,
        CASE 
            WHEN @phoneCompanyId IS NOT NULL
                THEN [cn].[PhoneCompanyId]
            ELSE NULL
            END AS [PhoneCompanyId],
        [cp].[TradingName] [PhoneCompanyName],
        CAST([t].[UserCost] AS DECIMAL(18, 4)) AS [UserCost]
    FROM [telecom].[CallDetailRecord] AS [t]
    LEFT JOIN [telecom].[TelecomBill] AS [a]
        ON [a].[Id] = [t].[TelecomBillId]
    LEFT JOIN [telecom].[TelecomBillAccount] AS [tba]
        ON [tba].[Id] = [a].[TelecomBillAccountId]
    LEFT JOIN [telecom].[TelecomBillAccountContract] AS [tbacon]
        ON [tbacon].[TelecomBillAccountId] = [tba].[Id]
    LEFT JOIN [doc].[PersonContract] AS [pcontr]
        ON [pcontr].[Id] = [tbacon].[ContractId]
    LEFT JOIN [people].[Person] AS [p]
        ON [p].[Id] = [pcontr].[ContractorId]
    LEFT JOIN [customer].[Client] AS [c]
        ON [c].[PersonId] = [p].[Id]
    LEFT JOIN [telecom].[PhoneNumber] AS [n]
        ON [n].[Id] = [t].[PhoneNumberId]
    LEFT JOIN [telecom].[ClientPhoneNumber] AS [cn]
        ON [cn].[PhoneNumberId] = [n].[Id]
            AND [cn].[ClientId] = [c].[Id]
    LEFT JOIN [telecom].[TelecomUser] AS [tu]
        ON [tu].[ClientPhoneNumberId] = [cn].[Id]
    LEFT JOIN [people].[Person] AS [cp]
        ON [cp].Id = [cn].[PhoneCompanyId]
    LEFT JOIN [people].[Person] AS [p2]
        ON [p2].[Id] = [tu].[PersonId]
    LEFT JOIN [people].[Person] AS [mg]
        ON [mg].[Id] = [p2].[SuperiorId]
    LEFT JOIN [people].[Departament] AS [dp]
        ON [dp].[Id] = [cn].[DepartamentId]
    LEFT JOIN [accounting].[CostCenter] AS [cc]
        ON [cc].[Id] = [dp].[CostCenterId]
    LEFT JOIN [telecom].[TelecomUserCostCenter] AS [tuc]
        ON [tuc].[TelecomUserId] = [tu].[Id]
    LEFT JOIN [accounting].[CostCenter] AS [tcc]
        ON [tcc].[Id] = [tuc].[CostCenterId]
    LEFT JOIN [telecom].[PhoneNumber] AS [n1]
        ON [n1].[Id] = [t].[CalledPhoneNumberId]
    WHERE (
            [t].[CallDate] BETWEEN [tu].[InitialDate]
                AND (
                        CASE 
                            WHEN [tu].[EndDate] IS NULL
                                THEN getdate()
                            ELSE [tu].[EndDate]
                            END
                        )
            OR [t].[CallDate] IS NULL
            )
        AND [ShowToUser] = 1
        AND [c].[Id] = @clientId
        AND [p2].[SuperiorId] = @superiorId
        AND [cn].[PhoneCompanyId] = ISNULL(@phoneCompanyId, [cn].[PhoneCompanyId])
        AND (
            ISNULL([cn].[DepartamentId], 0) = ISNULL(@departamentId, 0)
            OR [cn].[DepartamentId] = ISNULL(@departamentId, [cn].[DepartamentId])
            )
        AND [a].[ReferenceMonth] BETWEEN @referenceMonthInitial
            AND @referenceMonthEnd
    ) AS tb
GROUP BY [ClientId],
    [PersonId],
    [FullName],
    [Manager],
    [ClientPhoneNumberId],
    [PhoneNumber],
    [Departament],
    [CostCenter],
    [CostCenterCode],
    [TelecomUserInitialDate],
    [TelecomUserEndDate],
    [SuperiorId],
    [TelecomUserId],
    [PhoneCompanyId],
    [PhoneCompanyName],
    Meta,
    ServicesValue,
    RegisteredArea