TRENDING

TECHNOLOGY NEWS FROM AROUND THE WORLD

→
SQL Server 2012 Columnstore: Reading the scan cost of analytics
Software Engineering · 2012
Software Engineering2012

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

Yazılımcının GünlüğüPublished: Nov 18, 20263 min read
Reading progress0%

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.

Database access flowConceptual sketch · summary of the article's technical flow

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.

Be the first reader ✎ Write a comment

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?

Robin Bennett

Reading “SQL Server 2012 Columnstore: Reading the scan cost of analytics” raised this question for me: migration and rollback plans should be explained.

Sample editor reply

Editor note: Forward-looking claims in an announcement are not the same as established performance results. Further evidence may change the assessment.

Cameron Hayes

It is important to distinguish what the source verifies from projections about the future.

Sidney Wood

The next version's measurements should be compared with the initial announcement.

✎ Write a comment

Your first and last name will appear publicly alongside your comment.