Recently at a consulting project while working with tabular model I was asked about how we can force SQL Server to use batch mode on Columnstore index when doing a query which is essentially just a select *.
To demonstrate this I have written two queries below which will return the same number of rows and performs basically a select * on the data.
The table contains 5.8 M rows to allow for a realistic scenario. As you can see from the screenshot below we are executing in Row mode.
SELECT year
,carrier
,airlineid
FROM [dbo].[2014]
Now let’s modify the query by adding a filter that doesn’t actually filter anything
SELECT year
,carrier
,airlineid
FROM [dbo].[2014]
WHERE month >= 1
AND month < 13
As you can see from the screenshot below we can see that now the query execution mode is batch mode and we are still fetching the same number of rows.
So the next part of the question becomes while we are executing in batch mode do we get any significant performance improvement even though we are technically fetching all rows.
After three executions I found that batch mode does give a marginal improvement of about 6 seconds. I guess better results are possible on a server with multiple cores and better disk access.
Please Consider Subscribing
