Even a query returning one total can aggregate in different parts of its plan. Aggregate pushdown lets qualifying columnstore scans perform useful aggregation locally. Inspect the scan's actual properties, then compare an expression that requires different work above the scan.
Find Aggregate Pushdown Inside the Scan
Columnstore batch-mode execution can push eligible aggregate work into the scan. Instead of passing every qualifying raw value to later aggregation, the scan can supply locally aggregated results. The final plan can still contain an aggregate to combine partial results, so its presence does not disprove pushdown.
I inspect Actual Number of Locally Aggregated Rows on the Columnstore Index Scan. That property provides evidence about what happened during the execution. The SQL text alone does not guarantee that every SUM or COUNT uses the optimization.
Eligibility depends on aggregate form, data types, execution mode, and other plan conditions. Start with straightforward numeric aggregates before introducing expressions. Keep the actual plan and source definition together. A single output row is tidy, but it says nothing about whether the engine carried every input row through several operators before producing it. The accounting needs to happen somewhere.
Build a Compressed Numeric Example
The setup uses a dedicated table in a disposable database. It generates synthetic integers and a small region domain, then builds a clustered columnstore index. The chosen demonstration population and values are input settings, not measured production counts or storage claims.
The numeric values keep the sample SUM within the demonstrated type's range. For a real table, review aggregate return types and overflow risk. Changing a column type or adding a conversion can also change optimization eligibility, so preserve those details in the test.
I enable actual plans before running the aggregate, then retain STATISTICS IO output. A query that returns one value still needs its scan examined. Check actual execution mode, relevant predicates, rows read, and the locally aggregated property. A tiny table or an unsuitable physical state can produce a different strategy, which is a result to explain rather than a screenshot to replace.
CREATE TABLE dbo.AggregateColumnstoreDemo(ID int,Amount int,Region varchar(10));
WITH Numbers AS
(
SELECT TOP(100000) ROW_NUMBER() OVER(ORDER BY a.object_id,b.object_id) AS n
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT dbo.AggregateColumnstoreDemo
SELECT CONVERT(int,n),CONVERT(int,n%100),CASE WHEN n%2=0 THEN 'West' ELSE 'East' END
FROM Numbers;
CREATE CLUSTERED COLUMNSTORE INDEX CCI_AggregateDemo ON dbo.AggregateColumnstoreDemo;Compare Straightforward Aggregate Functions
The next query uses SUM, COUNT_BIG, MIN, and MAX over a qualifying numeric columnstore input. Inspect each aggregate expression in the actual plan and read the scan properties. Do not assume one eligible function proves every expression in a more complex query was pushed down.
COUNT_BIG avoids COUNT's int return limit for large populations. SUM still follows its input type's return rules, so a production design must protect its numeric range appropriately. That correctness requirement comes before choosing the most convenient demonstration type.
What rows did the scan reduce locally? Read the actual property and compare the rest of the plan. A final aggregate can combine local partial values, while filters and other operations can change where reduction occurs. Capture the observations from your own execution. The article supplies the query and interpretation, not an invented number claiming that a particular amount of work disappeared on your server.
SET STATISTICS IO ON;
SELECT SUM(Amount) AS TotalAmount,COUNT_BIG(*) AS TotalRows,
MIN(Amount) AS LowestAmount,MAX(Amount) AS HighestAmount
FROM dbo.AggregateColumnstoreDemo;
SET STATISTICS IO OFF;
Test Strings and Calculated Expressions Separately
A string aggregate or a calculated numeric expression can require work that does not qualify for the same scan-local aggregation. The next statements provide separate comparisons: MAX over Region and SUM over a row expression involving Amount and ID. Read their actual plans and the local-aggregation property again.
Optimizer transformations and version-specific support can alter the exact plan. Inspect whether the value is computed above the scan and whether local aggregation remains available. Report the observed property rather than claiming every expression must disable all pushdown in every future version. In my test on SQL Server 2025, the scan for the first query reported local aggregation for every row. The scans for MAX over Region and for the SUM over an expression reported none.
Keep the comparison's purpose clear. These queries calculate different business results, so their runtimes are not a direct substitute for an equivalent-query tuning test. They reveal eligibility and execution location. Once you identify the real workload's calculation, test an equivalent alternative if one exists. Removing a required expression merely to obtain pushdown changes the result and fails the task.
SELECT MAX(Region) AS LastRegion FROM dbo.AggregateColumnstoreDemo;
SELECT SUM(Amount+(ID%7)) AS ExpressionAmount FROM dbo.AggregateColumnstoreDemo;Review Grouping and Filtering for Aggregate Pushdown
GROUP BY introduces keys and potentially many partial groups. Supported pushdown behavior depends on those keys, their types, and the plan's conditions. A low-cardinality grouped aggregate and a unique-key grouped aggregate have very different opportunities for reducing rows.
The next query groups by the sample Region column. Inspect the scan's actual properties rather than assuming grouping either always enables or always prevents local aggregation. Keep the region distribution with the evidence, since the number of groups affects the amount of reduction. With two regions in my test, the grouped scan still reported local aggregation.
Predicates also matter. Segment elimination can skip work before aggregation, while scan-level filtering can change the rows being aggregated. These optimizations complement each other but remain separate observations. A reduction in segment reads is not automatically proof of aggregate pushdown. Describe which optimization the plan actually shows and how it contributes to the whole query.
SELECT Region,SUM(Amount) AS RegionAmount,COUNT_BIG(*) AS RegionRows
FROM dbo.AggregateColumnstoreDemo
GROUP BY Region;Judge the Complete Analytical Query
Test the real aggregate with representative filters, grouping keys, and data types. Check memory grants and spills in operators above the scan as well as local reduction. Pushdown can help without being the only important source of cost.
Keep the result contract unchanged when comparing rewrites. Validate numeric precision, NULL behavior, and group membership. A faster expression with a different rounding or missing category is not an optimization of the original report.
Aggregate pushdown is useful when supported work happens closer to the compressed data and reduces downstream processing. Confirm it through actual locally aggregated rows, compare ineligible forms carefully, and retain the complete plan. The lasting decision should serve the analytical calculation, not merely produce one attractive scan property.
Related reading on this blog: ColumnStore Indexes Without Aggregation and Stream Aggregate and Hash Aggregate.
A one-row result is not proof of efficient aggregation, it is the endpoint of work you must locate in the plan.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
