The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 62): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `idReportTurnover` is indexed, the index won’t be used as it’s wrapped with the function `CHARINDEX`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Subqueries (query line: 23): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
3. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
4. Mixed Order By Directions Prevents Index Use (query line: 71): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.

Optimal indexes for this query:

CREATE INDEX providers_idx_idproviderexter_idprovider ON providers (idProviderExternal,idProvider);

The optimized query:

SELECT
        t.GroupName AS ProviderName,
        t.GroupName AS Groupname,
        t.idPriceGroup,
        sum(t.fixed_value) AS fixed_value,
        avg(isnull(t.percent_value,
        0)) AS percent_value,
        isnull(sum(amount),
        0) AS amount,
        sum(CASE 
            WHEN Include2SpecTotal = 1 THEN isnull(amount,
            0) 
            ELSE 0 END) AS AmountSpecial,
isnull(sum(bonus_total),
0) AS bonus_total,
isnull(sum(bonus_landlord),
0) AS bonus_landlord,
sum(CASE 
    WHEN Include2SpecTotal = 1 THEN isnull(bonus_landlord,
    0) 
    ELSE 0 END) AS bonus_landlordSpecial 
FROM
(SELECT
    ISNULL(dbo.fn_GetProviders_str(p.idLocalize,
    'EN'),
    'Test Service') AS ProviderName,
    idSupplier,
    r.idProvider,
    alias,
    isnull(amount,
    0) amount,
    isnull(bonus_total,
    0) bonus_total,
    isnull(bonus_landlord,
    0) bonus_landlord,
    (SELECT
        idPriceGroup 
    FROM
        dbo.fn_GetPricelistGroupForProvider(r.idSupplier,
        r.alias)) AS idPriceGroup,
    (SELECT
        [name] 
    FROM
        dbo.fn_GetPricelistGroupForProvider(r.idSupplier,
        r.alias)) AS GroupName,
    (SELECT
        Include2SpecTotal 
    FROM
        dbo.fn_GetPricelistGroupForProvider(r.idSupplier,
        r.alias)) AS Include2SpecTotal,
    isnull(fixed_value,
    0) fixed_value,
    isnull(percent_value,
    0) percent_value 
FROM
    ReportTurnoverItems r 
LEFT JOIN
    providers p 
        ON r.idSupplier = p.idProviderExternal 
        AND r.idProvider = p.idProvider 
WHERE
    CHARINDEX(',' + CAST(idReportTurnover AS varchar) + ',', +',' + @idReportTurnover + ',') > 0
) t 
GROUP BY
t.GroupName,
t.idPriceGroup,
t.Include2SpecTotal 
ORDER BY
avg(isnull(t.percent_value,
0)) DESC,
t.GroupName,
t.idPriceGroup