views:

512

answers:

3

Warning: this is the actual code generated from my system:

;WITH RESULTS AS (
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'CountFocusRecords' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK)
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = m.APPL_CD
AND cat.ALBASE = m.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
GROUP BY m.APPL_CD, m.ALBASE
UNION
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'CountBiminiRecords' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_STATINV] AS c WITH(NOLOCK)
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = c.APPL_CD
AND cat.ALBASE = c.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
GROUP BY c.APPL_CD, c.ALBASE
UNION
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'RecordsInFocusMissingInBimini' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK)
LEFT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)
ON m.[YEAR] = c.[YEAR]
    AND m.[MONTH] = c.[MONTH]
    AND m.[BANK_NO] = c.[BANK_NO]
    AND m.[COST_CENTER] = c.[COST_CENTER]
    AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]
    AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]
    AND m.[APPL_CD] = c.[APPL_CD]
    AND m.[ALBASE] = c.[ALBASE]
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = m.APPL_CD
AND cat.ALBASE = m.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
WHERE c.[YEAR] IS NULL
GROUP BY m.APPL_CD, m.ALBASE
UNION
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'RecordsInBiminiMissingInFocus' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK)
RIGHT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)
ON m.[YEAR] = c.[YEAR]
    AND m.[MONTH] = c.[MONTH]
    AND m.[BANK_NO] = c.[BANK_NO]
    AND m.[COST_CENTER] = c.[COST_CENTER]
    AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]
    AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]
    AND m.[APPL_CD] = c.[APPL_CD]
    AND m.[ALBASE] = c.[ALBASE]
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = c.APPL_CD
AND cat.ALBASE = c.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
WHERE m.[YEAR] IS NULL
GROUP BY c.APPL_CD, c.ALBASE
) SELECT * FROM RESULTS ORDER BY Program, APPL_CD, ALBASE, Measure

The code just sits there, no locking or blocking.

The individual components of the UNION return in a few seconds each. The code works in general for checking the output results of all the other programs in the STAT group, but just halts for this one.

Remove the CTE, no effect, sits there for 30 minutes/an hour, however long you care to wait before cancelling.

Remove the UNION, and the 4 result sets return in 11 seconds, total of 19 records accross all 4 result sets.

Run just the first two together - works fine, run just the last 2 together, also fine. First 3 together, fine, too.

I've already modified the code to output these to a #temp table, for other requirements, so I'm just going to change it to output each to the #temp table in sequence, but I have never seen SQL just stop like that with no evidence of blocking or anything.

+1  A: 

Change to UNION ALL, since you'll never have dupes (the Measure column is hard coded to be different). UNION must first sort the rows, and then find dupes and eliminate.

My real guess is it's a parallelization issue. Try adding OPTION (MAXDOP 1) at the end.

Mark Brackett
None of those had any effect. The RIGHT JOIN shouldn't have anything to do with it, right? I was kind of lazy in the code gen procedure, so instead of reversing the output, I made a RIGHT JOIN instead of a LEFT JOIN.
Cade Roux
Generally, no - a left join and right join can be subbed for each other without issue. However, this seems like a query optimizer bug, so anything influencing the query plan could be the trigger. If you have access to a test db with the same issue, fire up Profiler and take a look.
Mark Brackett
Come to think of it - I think you'd need OPTION (MAXDOP 1) at the end of each SELECT (before UNION ALL) to disable parallelism for the entire query.
Mark Brackett
OPTION (MAXDOP 1) was not allowed on each query. I'll try with the profiler tomorrow.
Cade Roux
A: 

If you can post the query execution plan in XML format, that'll help us determine what parts of the query are causing problems. In SSMS, click Query, Display Estimated Execution Plan, and when it comes up, right-click on it and save as XML.

Brent Ozar
I'm using 2k5, it doesn't seem to offer the save as XML option.
Cade Roux
A: 

I've moved on to regression testing 200808, but the fundamental query is the same, with a different batchrunid and different known good table.

<?xml version="1.0"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3239.00">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="&#13;&#10;;WITH RESULTS AS (&#13;&#10;SELECT 1251 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'CountFocusRecords' AS Measure, COUNT(*) AS Value&#13;&#10;FROM [MISWork].[SX_FOCUS_NATIVE_200808] AS m WITH(NOLOCK)&#13;&#10;INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)&#13;&#10;ON cat.APPL_CD = m.APPL_CD&#13;&#10;AND cat.ALBASE = m.ALBASE&#13;&#10;AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'&#13;&#10;GROUP BY m.APPL_CD, m.ALBASE&#13;&#10;UNION&#13;&#10;SELECT 1251 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'CountBiminiRecords' AS Measure, COUNT(*) AS Value&#13;&#10;FROM [MISWork].[SX_STATINV] AS c WITH(NOLOCK)&#13;&#10;INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)&#13;&#10;ON cat.APPL_CD = c.APPL_CD&#13;&#10;AND cat.ALBASE = c.ALBASE&#13;&#10;AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'&#13;&#10;GROUP BY c.APPL_CD, c.ALBASE&#13;&#10;UNION&#13;&#10;SELECT 1251 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'RecordsInFocusMissingInBimini' AS Measure, COUNT(*) AS Value&#13;&#10;FROM [MISWork].[SX_FOCUS_NATIVE_200808] AS m WITH(NOLOCK)&#13;&#10;LEFT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)&#13;&#10;ON m.[YEAR] = c.[YEAR]&#13;&#10;    AND m.[MONTH] = c.[MONTH]&#13;&#10;    AND m.[BANK_NO] = c.[BANK_NO]&#13;&#10;    AND m.[COST_CENTER] = c.[COST_CENTER]&#13;&#10;    AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]&#13;&#10;    AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]&#13;&#10;    AND m.[APPL_CD] = c.[APPL_CD]&#13;&#10;    AND m.[ALBASE] = c.[ALBASE]&#13;&#10;INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)&#13;&#10;ON cat.APPL_CD = m.APPL_CD&#13;&#10;AND cat.ALBASE = m.ALBASE&#13;&#10;AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'&#13;&#10;WHERE c.[YEAR] IS NULL&#13;&#10;GROUP BY m.APPL_CD, m.ALBASE&#13;&#10;UNION&#13;&#10;SELECT 1251 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'RecordsInBiminiMissingInFocus' AS Measure, COUNT(*) AS Value&#13;&#10;FROM [MISWork].[SX_FOCUS_NATIVE_200808] AS m WITH(NOLOCK)&#13;&#10;RIGHT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)&#13;&#10;ON m.[YEAR] = c.[YEAR]&#13;&#10;    AND m.[MONTH] = c.[MONTH]&#13;&#10;    AND m.[BANK_NO] = c.[BANK_NO]&#13;&#10;    AND m.[COST_CENTER] = c.[COST_CENTER]&#13;&#10;    AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]&#13;&#10;    AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]&#13;&#10;    AND m.[APPL_CD] = c.[APPL_CD]&#13;&#10;    AND m.[ALBASE] = c.[ALBASE]&#13;&#10;INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)&#13;&#10;ON cat.APPL_CD = c.APPL_CD&#13;&#10;AND cat.ALBASE = c.ALBASE&#13;&#10;AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'&#13;&#10;WHERE m.[YEAR] IS NULL&#13;&#10;GROUP BY c.APPL_CD, c.ALBASE&#13;&#10;) SELECT * FROM RESULTS ORDER BY Program, APPL_CD, ALBASE, Measure&#13;&#10;&#13;&#10;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="1209.5" StatementEstRows="13965.1" StatementOptmLevel="FULL">
          <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false"/>
          <QueryPlan CachedPlanSize="504" CompileTime="1244" CompileCPU="1099" CompileMemory="5016">
            <MissingIndexes>
              <MissingIndexGroup Impact="29.2539">
                <MissingIndex Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[APPL_CD]" ColumnId="7"/>
                    <Column Name="[ALBASE]" ColumnId="8"/>
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[YEAR]" ColumnId="1"/>
                    <Column Name="[MONTH]" ColumnId="2"/>
                    <Column Name="[BANK_NO]" ColumnId="3"/>
                    <Column Name="[COST_CENTER]" ColumnId="4"/>
                    <Column Name="[GLACCOUNT_NO]" ColumnId="5"/>
                    <Column Name="[CUSTACCOUNT]" ColumnId="6"/>
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
              <MissingIndexGroup Impact="29.6796">
                <MissingIndex Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[APPL_CD]" ColumnId="7"/>
                    <Column Name="[ALBASE]" ColumnId="8"/>
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <RelOp NodeId="0" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="13965.1" EstimateIO="0" EstimateCPU="0.121489" AvgRowSize="45" EstimatedTotalSubtreeCost="1209.5" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Column="Union1039"/>
                <ColumnReference Column="Union1040"/>
                <ColumnReference Column="Union1041"/>
                <ColumnReference Column="Union1042"/>
                <ColumnReference Column="Union1043"/>
                <ColumnReference Column="Union1044"/>
              </OutputList>
              <Parallelism>
                <OrderBy>
                  <OrderByColumn Ascending="1">
                    <ColumnReference Column="Union1041"/>
                  </OrderByColumn>
                  <OrderByColumn Ascending="1">
                    <ColumnReference Column="Union1042"/>
                  </OrderByColumn>
                  <OrderByColumn Ascending="1">
                    <ColumnReference Column="Union1043"/>
                  </OrderByColumn>
                </OrderBy>
                <RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="13965.1" EstimateIO="0.00281532" EstimateCPU="0.220682" AvgRowSize="45" EstimatedTotalSubtreeCost="1209.37" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Column="Union1039"/>
                    <ColumnReference Column="Union1040"/>
                    <ColumnReference Column="Union1041"/>
                    <ColumnReference Column="Union1042"/>
                    <ColumnReference Column="Union1043"/>
                    <ColumnReference Column="Union1044"/>
                  </OutputList>
                  <MemoryFractions Input="0.0191727" Output="1"/>
                  <Sort Distinct="0">
                    <OrderBy>
                      <OrderByColumn Ascending="1">
                        <ColumnReference Column="Union1041"/>
                      </OrderByColumn>
                      <OrderByColumn Ascending="1">
                        <ColumnReference Column="Union1042"/>
                      </OrderByColumn>
                      <OrderByColumn Ascending="1">
                        <ColumnReference Column="Union1043"/>
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp NodeId="2" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="13965.1" EstimateIO="0" EstimateCPU="0.000349132" AvgRowSize="45" EstimatedTotalSubtreeCost="1209.15" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Column="Union1039"/>
                        <ColumnReference Column="Union1040"/>
                        <ColumnReference Column="Union1041"/>
                        <ColumnReference Column="Union1042"/>
                        <ColumnReference Column="Union1043"/>
                        <ColumnReference Column="Union1044"/>
                      </OutputList>
                      <Concat>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Union1039"/>
                            <ColumnReference Column="Expr1006"/>
                            <ColumnReference Column="Expr1014"/>
                            <ColumnReference Column="Expr1025"/>
                            <ColumnReference Column="Expr1036"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1040"/>
                            <ColumnReference Column="Expr1007"/>
                            <ColumnReference Column="Expr1015"/>
                            <ColumnReference Column="Expr1026"/>
                            <ColumnReference Column="Expr1037"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1041"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_STATINV]" Alias="[c]" Column="APPL_CD"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_STATINV]" Alias="[c]" Column="APPL_CD"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1042"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_STATINV]" Alias="[c]" Column="ALBASE"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_STATINV]" Alias="[c]" Column="ALBASE"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1043"/>
                            <ColumnReference Column="Expr1008"/>
                            <ColumnReference Column="Expr1016"/>
                            <ColumnReference Column="Expr1027"/>
                            <ColumnReference Column="Expr1038"/>
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Union1044"/>
                            <ColumnReference Column="Expr1005"/>
                            <ColumnReference Column="Expr1013"/>
                            <ColumnReference Column="Expr1024"/>
                            <ColumnReference Column="Expr1035"/>
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="7140" EstimateIO="0" EstimateCPU="0.0001785" AvgRowSize="42" EstimatedTotalSubtreeCost="362.728" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                            <ColumnReference Column="Expr1005"/>
                            <ColumnReference Column="Expr1006"/>
                            <ColumnReference Column="Expr1007"/>
                            <ColumnReference Column="Expr1008"/>
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1006"/>
                                <ScalarOperator ScalarString="(1251)">
                                  <Const ConstValue="(1251)"/>
                                </ScalarOperator>
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="Expr1007"/>
                                <ScalarOperator ScalarString="'STATINV'">
                                  <Const ConstValue="'STATINV'"/>
                                </ScalarOperator>
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Column="Expr1008"/>
                                <ScalarOperator ScalarString="'CountFocusRecords'">
                                  <Const ConstValue="'CountFocusRecords'"/>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="7140" EstimateIO="0" EstimateCPU="0.0001785" AvgRowSize="23" EstimatedTotalSubtreeCost="362.728" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                              <OutputList>
                                <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                <ColumnReference Column="Expr1005"/>
                              </OutputList>
                              <ComputeScalar>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1005"/>
                                    <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[globalagg1083],0)">
                                      <Convert DataType="int" Style="0" Implicit="1">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Column="globalagg1083"/>
                                          </Identifier>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <RelOp NodeId="7" PhysicalOp="Hash Match" LogicalOp="Aggregate" EstimateRows="7140" EstimateIO="0" EstimateCPU="0.114864" AvgRowSize="27" EstimatedTotalSubtreeCost="362.728" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                                  <OutputList>
                                    <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                    <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                    <ColumnReference Column="globalagg1083"/>
                                  </OutputList>
                                  <MemoryFractions Input="0.5" Output="0.980827"/>
                                  <Hash>
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Column="globalagg1083"/>
                                        <ScalarOperator ScalarString="SUM([partialagg1082])">
                                          <Aggregate Distinct="0" AggType="SUM">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Column="partialagg1082"/>
                                              </Identifier>
                                            </ScalarOperator>
                                          </Aggregate>
                                        </ScalarOperator>
                                      </DefinedValue>
                                    </DefinedValues>
                                    <HashKeysBuild>
                                      <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                      <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                    </HashKeysBuild>
                                    <BuildResidual>
                                      <ScalarOperator ScalarString="[DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[APPL_CD] as [m].[APPL_CD] = [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[APPL_CD] as [m].[APPL_CD] AND [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[ALBASE] as [m].[ALBASE] = [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[ALBASE] as [m].[ALBASE]">
                                        <Logical Operation="AND">
                                          <ScalarOperator>
                                            <Compare CompareOp="IS">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Compare CompareOp="IS">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </Logical>
                                      </ScalarOperator>
                                    </BuildResidual>
                                    <RelOp NodeId="8" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="28560" EstimateIO="0" EstimateCPU="0.0614707" AvgRowSize="27" EstimatedTotalSubtreeCost="362.613" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                                      <OutputList>
                                        <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                        <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                        <ColumnReference Column="partialagg1082"/>
                                      </OutputList>
                                      <Parallelism PartitioningType="Hash">
                                        <PartitionColumns>
                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                        </PartitionColumns>
                                        <RelOp NodeId="9" PhysicalOp="Hash Match" LogicalOp="Partial Aggregate" EstimateRows="28560" EstimateIO="0" EstimateCPU="1.7277" AvgRowSize="27" EstimatedTotalSubtreeCost="362.551" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                                          <OutputList>
                                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                            <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                            <ColumnReference Column="partialagg1082"/>
                                          </OutputList>
                                          <MemoryFractions Input="0" Output="0"/>
                                          <Hash>
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Column="partialagg1082"/>
                                                <ScalarOperator ScalarString="COUNT(*)">
                                                  <Aggregate Distinct="0" AggType="COUNT*"/>
                                                </ScalarOperator>
                                              </DefinedValue>
                                            </DefinedValues>
                                            <HashKeysBuild>
                                              <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                              <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                            </HashKeysBuild>
                                            <BuildResidual>
                                              <ScalarOperator ScalarString="[DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[APPL_CD] as [m].[APPL_CD] = [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[APPL_CD] as [m].[APPL_CD] AND [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[ALBASE] as [m].[ALBASE] = [DUASFIN].[MISWork].[SX_FOCUS_NATIVE_200808].[ALBASE] as [m].[ALBASE]">
                                                <Logical Operation="AND">
                                                  <ScalarOperator>
                                                    <Compare CompareOp="IS">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </Compare>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Compare CompareOp="IS">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </Compare>
                                                  </ScalarOperator>
                                                </Logical>
                                              </ScalarOperator>
                                            </BuildResidual>
                                            <RelOp NodeId="10" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="879583" EstimateIO="0" EstimateCPU="62.0602" AvgRowSize="19" EstimatedTotalSubtreeCost="360.824" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
                                              <OutputList>
                                                <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                              </OutputList>
                                              <MemoryFractions Input="1" Output="0.5"/>
                                              <Hash>
                                                <DefinedValues/>
                                                <HashKeysBuild>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISProcess]" Table="[SXProcessCatalog]" Alias="[cat]" Column="APPL_CD"/>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISProcess]" Table="[SXProcessCatalog]" Alias="[cat]" Column="ALBASE"/>
                                                </HashKeysBuild>
                                                <HashKeysProbe>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="APPL_CD"/>
                                                  <ColumnReference Database="[DUASFIN]" Schema="[MISWork]" Table="[SX_FOCUS_NATIVE_200808]" Alias="[m]" Column="ALBASE"/>
                                                </HashKeysProbe>
                                                <ProbeResidual>
Cade Roux