Fragmentation in columnstore indexes means deleted rows that stay inside compressed row groups, and row groups that are too small. This post builds a demo table, measures the deleted rows with the row group view, and compares REORGANIZE with REBUILD.
Locally Aggregated Rows: Why a Columnstore Scan Shows Zero
A columnstore scan shows zero actual rows when it aggregates rows itself, and the rows sit in the locally aggregated rows property. A tested demo on 1,000,000 rows shows 0 actual rows and 1,000,000 locally aggregated rows. The post also covers a scan that really is empty, and what STATISTICS IO counts.
How to Disable Batch Mode in SQL Server? – Interview Question of the Week #252
My immediate reaction was why would you disable batch mode in SQL Server when it is really is an awesome feature for performance
Row Mode vs Batch Mode: Measuring the Speed Difference
A fair test of row mode vs batch mode changes only the execution mode. This post uses a query hint to force row mode at the same compatibility level, checks that both modes return the same rows, and times five runs of each on a three million row table. It also shows a query where batch mode gains nothing.
Batch Mode on Rowstore: A Simple Example in SQL Server
Batch mode on rowstore lets SQL Server process rows in groups, even when the table has no columnstore index. This post builds a million row table, reads the execution mode of each plan operator, and flips four switches to show when you get batch mode and when you don’t. It also answers why a plan can stay in row mode.
Nonclustered Columnstore Index on a Temp Table in SQL Server
A nonclustered columnstore index works on a temp table but not on a table variable. This post loads a temp table, builds the index, and measures the same aggregate query before and after. It also shows the error a table variable raises and when the build cost pays off.
Columnstore Indexes Without Aggregation: Do They Still Help?
Columnstore indexes without aggregation still help when a query reads many rows but only a few columns. This post builds a rowstore table and a columnstore copy of one million rows and compares reads, size and execution mode. It also shows the case where a plain b-tree index wins.