Actual plan: https://www.brentozar.com/pastetheplan/?id=SJviG_y1h

I'm looking for advice on where to start with tuning the above stored procedure. We need it to complete in as near to 1 second as possible but currently taking up to 40 seconds in production. The SP seems to be fluctuating based on server workload so need it to run as efficiently as possible. Under no load in UAT against a copy of the same db it runs in 5 seconds.

The warnings from Brent Ozar's sp_blitzcache are: Parallel, Plan Warnings, Parameter Sniffing, Implicit Conversions, Function Join, Trivial Plans, Unused Memory Grant, Plan created last 4hrs, Many Rows Table Spool, non-SARGables

This is running on the primary replica of an Always On Availability Group DB (2 node setup with synchronous commit and read only enabled on secondary). We cannot create any additional indexes.

SQL version is: Microsoft SQL Server 2017 (RTM-CU29) (KB5010786) - 14.0.3436.1 (X64) Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Other configurations of note:

  • SQL Max memory is set at 92gb
  • Server has 8 CPUs (all on 1 numa node)
  • CTFP is set at 50
  • MAXDOP set at 4
  • Primary DB has RCSI enabled

1 Answer 1


The Potential Problem

At a quick glance at the execution plan and what the code is doing, it looks like one of the main issues is your JOIN clause to [maxims_prod].[dbo].core_patient_c_identifi:

LEFT JOIN [maxims_prod].[dbo].core_patient_c_identifi alt 
  ON cp.id = alt.id 
  AND alt.lkp_c_ty = CASE @SourceSystem WHEN 'Rio' THEN 6942 
             WHEN 'Trakcare' THEN 6999

Specifically the CASE statement, it overcomplicates the predicate, which appears to result in multiple index scans against a larger (+1 million rows) table.

One Possible Fix

One potential way to fix that, is by branching the code on the @SourceSystem variable, since this is a stored procedure anyway. Prior to your main query that joins in [maxims_prod].[dbo].core_patient_c_identifi, you can create the following branch to materialize the data to a temp table:

-- Don't use SELECT *, list out the columns explicitly instead. I'm only doing it because I don't have your table schema to reference
DROP TABLE IF EXISTS #core_patient_c_identifi;
INTO #core_patient_c_identifi
FROM [maxims_prod].[dbo].core_patient_c_identifi;

IF (@SourceSystem = `Rio`)
    INSERT INTO #core_patient_c_identifi
    SELECT *
    FROM [maxims_prod].[dbo].core_patient_c_identifi
    WHERE alt.lkp_c_ty = 6942
ELSE IF (@SourceSystem = `Trakcare`)
    INSERT INTO #core_patient_c_identifi
    SELECT *
    FROM [maxims_prod].[dbo].core_patient_c_identifi
    WHERE alt.lkp_c_ty = 6999

Then your JOIN clause in your main query just needs to be:

LEFT JOIN #core_patient_c_identifi alt
    ON cp.id = alt.id

Another Dangerous Predicate

At the very end of your query you have the following predicate:

WHERE AlternativeLocalPtId = '' -- return all rows if no 3a match is found
    OR AlternativeLocalPtId = Matched_LocalPtId

This is potentially problematic for two reasons. The first being ORs can also jam up predicates from being optimally seeked on. Although this one is not overly complicated, so is probably of less concern.

The bigger issue is the AlternativeLocalPtId column is an alias in you previous subquery for an inline correlated subquery with an XML parsing expression. This makes the aforementioned predicate more complex than the words I just used to describe it:

         CASE WHEN Match_type LIKE '%3a%' 
             THEN Matched_LocalPtId END [text()]
     WHERE MATCHTYPE.MessageControlID  = MATCHTYPE2.MessageControlID    
     ORDER BY Match_type
     FOR XML PATH (''),TYPE).value('.','NVARCHAR(200)')
) AlternativeLocalPtId

Possible Fix

Materialization is likely your friend again here. You can extract only the pertinent parts of the query (might be the whole main query itself) to build the expression for AlternativeLocalPtId, and insert it into a temp table. Then filter on the temp table by the materialized column AlternativeLocalPtId.

I tried to write out a code example of that solution but the query is too verbose for me to peel back and modify on here (I write my answers from my phone). But you should get the idea at this point.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.