How To Optimize a Query That Aggregates Then Duplicates a Large Number of Records in a Single Table

Question

In the following post, J.D. brings up that I have a poorly performing query.

I am running this query on SQL Server 2019 Standard Edition (query plan was generated in Development Edition)

Let's take a look at it here:

INSERT INTO [dbo].[tbl_Planning_Operational_Data_Exploded] (
    [ScenarioID]
    ,[CompanyID]
    ,[OperationalAccountID]
    ,[CurrencyID]
    ,[CustomerID]
    ,[ItemID]
    ,[CalendarDate]
    ,[Amt]
    ,[PlanningOperationalDataActualTransactionAttributeValueExplodedID]
    )
SELECT ats.[ScenarioID]
    ,pode.[CompanyID]
    ,pode.[OperationalAccountID]
    ,pode.[CurrencyID]
    ,pode.[CustomerID]
    ,pode.[ItemID]
    ,pode.[CalendarDate]
    ,SUM(pode.[Amt]) AS Amt
    ,'00000000-0000-0000-0000-000000000000' AS [PlanningOperationalDataActualTransactionAttributeValueExplodedID]
FROM #ActualThroughScenarios ats WITH (NOLOCK) --Mini 100 records
INNER JOIN [dbo].[tbl_Core_Scenarios] cs WITH (NOLOCK) ON cs.ScenarioID = ats.ScenarioID --Mini 100 records
    AND cs.ScenarioTypeID IN (
        2
        ,3
        )
INNER JOIN [dbo].[tbl_Core_Scenarios] csActuals WITH (NOLOCK) ON csActuals.FiscalYear = cs.FiscalYear --Mini 100 records
    AND csActuals.ScenarioTypeID = 1
INNER JOIN [dbo].[tbl_Planning_Operational_Data_Exploded] pode ON pode.ScenarioID = csActuals.ScenarioID -- Huge up to 300 million records
INNER JOIN [dbo].[tbl_Core_Fiscal_Date] cfd WITH (NOLOCK) ON pode.CalendarDate = cfd.CalendarDate  --Mini 1000 records
WHERE cfd.FiscalPeriod <= cs.ActualsThrough
    AND cs.ActualsThrough > 0
GROUP BY ats.[ScenarioID]
    ,pode.[CompanyID]
    ,pode.[OperationalAccountID]
    ,pode.[CurrencyID]
    ,pode.[CustomerID]
    ,pode.[ItemID]
    ,pode.[CalendarDate]

Query Plan Generated: https://www.brentozar.com/pastetheplan/?id=Sk69AQ-As

Basically this query is quite simple, I have a very large table "Exploded", I need to take sections of it, group them and modify the "ScenarioID", then re-insert them into the same table.

I can optimize or shift indexing strategy for all of the small tables, but adding indexes to the "Exploded" table is very costly in other parts (not shown there are many) of the database, I would prefer not to add any additional indexes to that table.

As mentioned in the linked post above this query can run can run quite slowly as it generates a very large Hash Match, I think this is the group by doing that, but I need the group by, that and the summation are keys parts to what I'm doing here:

Which spills to TempDB and is heavily affected by the speed of TempDB. Is there any way I could improve the query above given the constraints I have on the related tables?

Answer 1

The goal here is to get the data you want staged in a temp table, and then dump that into the permanent table.

Part of the problem is that you're inserting into a table while also referencing it in the query you're pulling data from. When processing many rows, a fully parallel plan is often more ideal than doing everything on a single thread. It's possible you're not getting one here due to the placement of parallel exchanges, etc.

Even though the cardinality estimates are dismally off through much of the plan, it has an estimated subtree cost that should be high enough to qualify for a parallel plan, you don't get one. We don't get an explicit reason for the serial plan, so we can assume the decision is cost-based.

I don't think you need to change indexes at all, but you should try some things. Here's what I would try, with comments below.

CREATE TABLE
    dbo.cs
(
    id bigint NOT NULL,
    INDEX c CLUSTERED COLUMNSTORE
);

SELECT
    ats.ScenarioID,
    pode.CompanyID,
    pode.OperationalAccountID,
    pode.CurrencyID,
    pode.CustomerID,
    pode.ItemID,
    pode.CalendarDate,
    Amt = SUM(pode.Amt),
    PlanningOperationalDataActualTransactionAttributeValueExplodedID = '00000000-0000-0000-0000-000000000000'
INTO #tbl_Planning_Operational_Data_Exploded
FROM #ActualThroughScenarios AS ats WITH (NOLOCK)
INNER JOIN dbo.tbl_Core_Scenarios AS cs WITH (NOLOCK)
  ON  cs.ScenarioID = ats.ScenarioID
  AND cs.ScenarioTypeID IN (2, 3)
INNER JOIN dbo.tbl_Core_Scenarios AS csActuals WITH (NOLOCK)
  ON csActuals.FiscalYear = cs.FiscalYear
  AND csActuals.ScenarioTypeID = 1
INNER JOIN dbo.tbl_Planning_Operational_Data_Exploded AS pode
  ON pode.ScenarioID = csActuals.ScenarioID
INNER JOIN dbo.tbl_Core_Fiscal_Date AS cfd WITH (NOLOCK)
  ON pode.CalendarDate = cfd.CalendarDate
LEFT JOIN dbo.cs ON 1 = 0 /*Attempt batch mode execution*/
WHERE cfd.FiscalPeriod <= cs.ActualsThrough
AND   cs.ActualsThrough > 0
GROUP BY
    ats.ScenarioID,
    pode.CompanyID,
    pode.OperationalAccountID,
    pode.CurrencyID,
    pode.CustomerID,
    pode.ItemID,
    pode.CalendarDate
OPTION
(
    USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'), /*See if cardinality estimated improve*/
    QUERYTRACEON 8649, /*Go for a parallel execution plan*/
    LOOP JOIN, /*Avoid  merge joins in parallel execution plans*/
    HASH JOIN, /*See above*/
    MIN_GRANT_PERCENT = 100 /*Get the full grant available, adjust if necessary*/
);

1. If you're on Enterprise/Developer Edition, I would attempt to get batch mode execution involved, since it tends to work better for large aggregations. It is terribly hobbled in Standard Edition with a limit of two threads in a parallel plan, though. If that's what you're using, there's a chance that limitation will be counterproductive.

2. See if the legacy Cardinality Estimator improves row estimates between joins. This may be enough to not need other hints, but you'll have to test that locally.

3. Force a parallel execution plan via undocumented trace flag 8649. You may not need this with the query dumping into a #temp table first, but it's here for completeness. If you don't have permissions to tweak trace flags, you can replace this hint with USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE').

4. Use join hints to avoid getting a Merge Join in a parallel execution plan, because parallel Merge Joins were an unforgivable mistake.

5. Request the full memory grant available on this server for the query. You can reduce this to a lower percentage if the maximum grant far outsizes the needs of the final query plan. I don't know what those will be with the other prescribed changes, but it certainly looks to be higher than the spills in the current query plan.

As a final note, it's unclear why you have trace flags 1117 and 1118 enabled on SQL Server 2019, since their behavior became the default starting with SQL Server 2016.