SQL Server 2012 Columnstore: Reading the scan cost of analytics
Understand what column-oriented storage changes for sales analytics, the limits of the early SQL Server release and how to compare plans.
Archive retrospective · Topic year 2012
Analytics scans have different needs from point lookups
A sales report can read only a few columns across a large part of a table. It may aggregate quantity and amount by date and product group. SQL Server 2012 introduced nonclustered columnstore support, opening column-oriented scans and compression for that work. A point lookup for one order has a different access pattern. Separate the queries that scan broad segments from those that seek a few records before changing indexes.
Keep 2012 behavior separate from modern releases
Early columnstore versions restricted ordinary updates on indexed tables. Later SQL Server capabilities do not apply retroactively. In a period data warehouse, a team might separate read and load windows or plan index maintenance around data processing. A historical comparison should explain the balance between query speed, maintenance time and the update constraints that applied to the deployed version.
Compare plans with equivalent data and questions
Use the same input, filters and calculation when comparing plans. Choose parameters for quiet and busy periods. Record logical reads, CPU time, total elapsed time and execution operators. A faster isolated run does not settle system-wide behavior. Include loading, maintenance and any overlapping work. Match the resulting amounts before drawing a performance conclusion. Plan shape matters, but it is only one part of the observed outcome.
Pair storage decisions with a correct data model
Document whether facts describe order headers or line items. Faster scans magnify the consequences of calculating at the wrong grain. Test fact rows that do not match product or date dimensions. Then investigate how the same query executes on a modern release. A useful retrospective records data size, filter selectivity and maintenance constraints as well as response time. That makes the technical improvement meaningful for the business.
Define the data contract first
SQL changes need explicit keys, uniqueness rules, null semantics, time zones and transaction boundaries. Test retries and concurrent workers, not only a single successful request. Count source and destination records before migrations, retain checkpoints and keep a rollback path. Strong data contracts prevent duplicated or missing records when jobs are repeated.
Measure execution plans under representative load
An index is not a universal speed improvement. Inspect query plans, rows read, statistics freshness and write overhead together. A query made faster for one dashboard may slow down frequent ingestion. Compare latency percentiles and system resource use under realistic data distribution before calling the optimization successful.
Comments
No comments yet. Be the first to contribute.
Discussion question from the editor Would you first compare logical reads or elapsed time for these analytic scans?
Reading “SQL Server 2012 Columnstore: Reading the scan cost of analytics” raised this question for me: migration and rollback plans should be explained.
Editor note: Forward-looking claims in an announcement are not the same as established performance results. Further evidence may change the assessment.
It is important to distinguish what the source verifies from projections about the future.
The next version's measurements should be compared with the initial announcement.
✎ Write a comment