Sql-server – How to make this execution plan more efficient

execution-plansql serversql-server-2017type conversion

I have worked out all the implicit conversions, but I still see mentions of it in the plan. I have attached the plan, and any recommendation will help.

select cardholder_index, sum(value) as [RxCost] 
into #rxCosts 
from RiskPredictionStatistics with (nolock) where model_name = 'prescription_cost_12_months'
 and model_set_name = 'rx_updated' and run_id in (select value from #runIds) 
 and exists (select 1 from StringContainsHelper with (nolock) where IntValue = cardholder_index and ReferenceId = @stringContainsHelperRefId)
group by cardholder_index

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

Best Answer

It looks like when you populate the #runIds table -- and I'm just taking a wild guess here -- you're using a string splitter function that outputs the values as NVARCHAR(MAX).

You could try converting the values there to get rid of the implicit conversion warnings.

Another possible improvement would be to alter the NonClustereIndex-Cardholder index on RiskProductionStatistics to have model_set_name as a key column, and model_name, run_id, value as included columns. This would address the Key Lookup.

You may also want to check the datatype of model_name. It appears in a Filter operator, and my fear is that it's a MAX datatype, which may prevent the predicate from being pushed down.

Since this is an estimated plan, and you haven't included any metrics about the query, it's hard to say how much improvement these changes will have.

Related Solutions

Sql-server – Deadlock — Way to make this better

I would try to understand why your query leads to a deadlock. It would be useful to look at deadlock graph info. Try enabling trace flags (1204, 1222) to get more details from the SQL Server log.
Since you are using the row level locking and get deadlocks I suspect that PrintBatchId might have a large number of records associated. So SQL Server has to escalate the lock to page/table level. And as a result, you get a deadlock with an update on different PrintBatchId. (BTW. Do you have an index on PrintBatchId? W/o proper index row level locking won't work). Try to make query filter smaller.

If you can't make your filter more precise I would suggest to catch deadlocks and retry update attempt. Here is a sample code:

DECLARE @Retries INT = 5
    , @ErrorMessage nvarchar(max)
    , @ErrorLine INT    
    , @ErrorSeverity INT
    , @ErrorNumber INT 

WHILE (@Retries > 0)
BEGIN             
    BEGIN TRY
        --UPDATE Command.... 
        SELECT @retries = 0
    END TRY
    BEGIN CATCH
        SELECT 
              @ErrorMessage = ERROR_MESSAGE()
            , @ErrorLine    = ERROR_LINE()
            , @ErrorSeverity= ERROR_SEVERITY()
            , @ErrorNumber  = ERROR_NUMBER();

        IF (@ErrorNumber = 1205 AND @Retries > 0) -- If Deadlock, then retry 
            SELECT @ErrorMessage = 'Execution Failed due to a deadlock. Retrying... ';
        ELSE
        IF (@ErrorNumber = 1205 AND @Retries <= 0) -- If Deadlock, and max number of retries reached. Fail.
            SELECT @ErrorMessage = 'Execution Failed due to a deadlock after max number of retries ... ' + CHAR(13) + ' Original Error: Msg. ' + CAST(@ErrorNumber AS VARCHAR(MAX)) + ', ' + @ErrorMessage;
        ELSE
            SELECT @ErrorMessage = 'Execution Failed: Msg ' + CAST(@ErrorNumber AS VARCHAR(MAX)) + ', ' + @ErrorMessage;

        SELECT @Retries-=1;     

    END CATCH;
END

Sql-server – Ideas how to make this query faster

The last two left joins are not used and should be deleted

Have index on all ON conditions

I would try materialize in a #temp with a PK

SELECT IDPolozka 
FROM SA_NapZisk_Popis2Zmeny 
WHERE Zpracovano <> 1 OR Zpracovano IS NULL

SELECT TabPohybyZbozi.ID
      ,TabPohybyZbozi.NazevSozNa2
      ,TabZakazka.Nazev
      ,TabDokladyZbozi.DatPorizeni
      ,TabPohybyZbozi.IDDoklad
FROM TabPohybyZbozi 
INNER JOIN TabDokladyZbozi 
        ON TabDokladyZbozi.ID          = TabPohybyZbozi.IDDoklad
       AND TabDokladyZbozi.DruhPohybuZbo IN (13,14,18,19)
       AND TabDokladyZbozi.DatPorizeni >= DATEADD(DAY,-365,GETDATE()) 
       AND TabDokladyZbozi.DatPorizeni <= GETDATE()
LEFT  JOIN TabZakazka 
        ON TabZakazka.CisloZakazky     = TabPohybyZbozi.CisloZakazky
LEFT  JOIN TabPohybyZbozi_Ext 
        ON TabPohybyZbozi_Ext.ID       = TabPohybyZbozi.ID 
       AND (    TabPohybyZbozi.NazevSozNa2 <> TabZakazka.Nazev 
            OR (TabPohybyZbozi.NazevSozNa2 = N'' AND TabZakazka.Nazev IS NULL))
       AND TabPohybyZbozi.NazevSozNa2 <> N'' 
       AND TabPohybyZbozi.NazevSozNa2 <> N'0'
LEFT  JOIN TabDruhDokZbo 
        ON TabDruhDokZbo.DruhPohybuZbo = TabDokladyZbozi.DruhPohybuZbo 
       AND TabDruhDokZbo.RadaDokladu   = TabDokladyZbozi.RadaDokladu
LEFT  JOIN TabDruhDokZbo_Ext 
        ON TabDruhDokZbo.ID            = TabDruhDokZbo_Ext.ID
       AND (   TabDruhDokZbo_EXT._SA_ZalohovaFa = 0 
            OR TabDruhDokZbo_EXT._SA_ZalohovaFa IS NULL)
WHERE NOT EXISTS (SELECT * 
                  FROM SA_NapZisk_Popis2Zmeny 
                  WHERE (Zpracovano <> 1 OR Zpracovano IS NULL) 
                  AND IDPolozka = TabPohybyZbozi.ID
                 )

Best Answer

Related Solutions

Sql-server – Deadlock — Way to make this better

Sql-server – Ideas how to make this query faster

Related Question