Query optimization question

Question

I'm attempting to improve this query. When I run this as user it runs in ~1 minute but during the ETL load it can take up to 40 minutes. There shouldn't be any locking issues because these tables aren't referenced anywhere else.

Based on the query plan, which direction I should go?

I can improve my indexing. It uses hash matches and merge joins might improve this with indexing.

Do I need an approach that isn't a series of LEFT JOINs?

Column and table names have been obfuscated:

https://www.brentozar.com/pastetheplan/?id=rJc7-rHV6

Answer 1

Here's some rough accounting of where the time was spent in your execution plan:

• About 5 seconds to join CohortStagingTemp, #Cohort_5, and #Cohort_4 together with batch mode hash joins
• About 42 seconds to join #Cohort_1
• About 15 seconds to join in the remaining 3 tables
• About 16 seconds for the parallel insert

So there's a clear bottleneck in the query: the join to #Cohort_1. SQL Server is estimating a 12 KB row size from that table, so I'm guessing that you have some LOB columns in that table. There are some restrictions on LOB data for batch mode hash joins. That's probably why you see some batch mode hash joins and then the plan switches to row mode with the expensive merge join along with other row mode hash joins.

In general, I would stick with one approach: either get a query plan with row mode merge joins or get a plan with mostly batch mode hash joins. Your tables all have the same row count and join on the same column so merge joins might do just fine here, but the query may need to run at MAXDOP 1 for best performance. I recommend getting actual query plans for the following test cases:

1. Try the query as is but comment out the join to #Cohort_1
2. Try to get a better hash join plan by adding an OPTION (HASH JOIN, LOOP JOIN) hint and rewriting the query to have the #Cohort_1 join at the end
3. Add clustered indexes to all tables on the PersonID column and add an OPTION (MERGE JOIN) hint to the query

You also expressed concern that the query can take up to 40 minutes during ETL which is much longer than what you observed with your testing. The most basic way to troubleshoot this is to enable Query Store and to look for differences between what query store logs and what you saw in your own testing. Are the query plans different? Are the wait stats different? Does one query use more CPU than the other? All of that information is available on SQL Server 2019. You don't have to guess as to whether or not there was blocking. Query Store will tell you this.

In a comment, you said the following:

our admins won't allow us to turn on query store d/t overhead which is disappointing

I find this to be an offensive statement. DBAs don't work to serve the database. They should work to serve the end users. The end users include you, the developer. If they want to take the position that they can't enable Query Store due to overhead (which is almost certainly wrong), then they need to provide you the information that you need in some other way. You may need to escalate this problem to management or try to find some kind of compromise. For example, if the ETL workload only runs at night maybe Query Store can be enabled just during the night. But I guarantee you that if the DBAs had a problem that they were responsible for, they would enable the necessary diagnostics instead of making excuses about overhead.

Answer 2

Look at the actual plan for when the query runs long, and the wait stats will tell you why.

         <WaitStats>
          <Wait WaitType="CXPACKET" WaitTimeMs="290132" WaitCount="49920" />
          <Wait WaitType="HTMEMO" WaitTimeMs="3417" WaitCount="12" />
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="2579" WaitCount="563763" />
          <Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="2282" WaitCount="321487" />
          <Wait WaitType="SOS_PHYS_PAGE_CACHE" WaitTimeMs="1486" WaitCount="7410" />
          <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="155" WaitCount="1976" />
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="72" WaitCount="96" />
          <Wait WaitType="LATCH_EX" WaitTimeMs="66" WaitCount="2807" />
          <Wait WaitType="LATCH_SH" WaitTimeMs="43" WaitCount="7" />
          <Wait WaitType="HTBUILD" WaitTimeMs="8" WaitCount="12" />
        </WaitStats>
        <QueryTimeStats CpuTime="195151" ElapsedTime="79659" />

Or turn on Query Store and look at the wait stats there.

From a design POV, having multiple separate cohort staging tables instead of one wider one is odd. And using columnstore on temp tables is odd. They are expensive to build, and it's expensive to reconstruct and output all the rows for a columnstore. It's cheaper to do that with a heap or clustered index.

Answer 3

Based on the query plan alone, do you have suggestions regarding which direction I should go? My intuition tells me I can improve my indexing. I've noticed it uses hash matches and my understanding is that merge joins might improve this which means fiddling with indexing. Also curious if I need to take my blinders off and consider an approach that isn't a series of LEFT JOINs.

For a query plan like this, with columnstore indexes as the source for some of the data acquisition operators, I'd go in the opposite direction.

1. Add columnstore indexes to any tables that don't currently have them
2. Use OPTION(HASH JOIN) at the end of the query
3. If 8 or more cores are available in the server, add MAXDOP 8 to the hint as well

Merge joins don't support batch mode -- and there is at least one in your plan -- where hash joins do.

This whole thing would likely be better off operating entirely in batch mode rather than having some operations in row mode, and switching back and forth at various points in the plan.

The purpose of the increased DOP is to spread the per-thread row distribution so more threads have fewer rows to process concurrently, which will likely also improve the wall clock time at the cost of additional CPU time.

Answer 4

Apart from what other said.

i) Notice the warning sign in query plan and eliminate the conversion warning.Why convert Personid to varchar?similarly notice the other conversion.Data type should be uniform.

ii) Though query appear very simple,but table design appear to be flawed,due to which optimizer getting overwhelmed. On and avg. each temp contain 33L data .Also many of them are large data type.

iii)Try another method,Insert all personid and other columns from Staging.CohortStagingTemp AS A first.Then Update Project.BaseTable_STG with each of the temp table using inner join one be one.I am sure this will be faster