SQL Server 2016 ColumnStore Clustered Index - Query plan showing a scan, performance not great I have a SQL 2016 table that looks as follows:



I have a columnstore clustered index on the table and no other indexes. I'm executing the following query:

Select *
From dbo.PanelWorkflow
Where ReadTime Between '4/1/2016' And '4/5/2016' And Lineage = 'PBG11A' And ProcessNumber = 5400


The query plan looks as follows:



This table has 1.1B rows. The query plan shows a scan and the query takes about 1 minute to execute. ~21K rows are returned. Is this what I can expect from columnstore clustered indexes? Will a scan always be done (assuming no other indexes on the table)? Is this the sort of performance I can expect (acceptable but not great)?

I set Statistics IO on and got the following output:

Table 'PanelWorkflow'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 9733431, lob physical reads 195, lob read-ahead reads 13045645.
Table 'PanelWorkflow'. Segment reads 955, segment skipped 336.


Finally, the query above is strictly a test query to get a feel for how CSIs perform and operate. This is not a production level query.
I have a columnstore clustered index on the table and no other indexes ... The query plan shows a scan


Well, pretty much that is the only option available for a CCI. With CCIs the performance gain come from column elimination, compression and segment elimination, all contributing to reduce IO and thus allow scans to perform fast. For your case I would look at two things:


check segment health: sys.column_store_row_groups. For 1.1B rows you should have ~1000 rowgroups, all closed and compressed.
check that you loaded the data in a manner that allows segment elimination. Read Understanding Segment Elimination. Given your date range filter your query should scan only 1 (one) of those 1000 segments, and skip the other 999. If this doesn't happen, it means you loaded the data in a manner that prevents segment elimination. Read Columnstore indexes - data loading guidance. 


Now, your query: 

Select *
From dbo.PanelWorkflow
Where ReadTime Between '4/1/2016' And '4/5/2016' And Lineage = 'PBG11A' And ProcessNumber = 5400


This is not what columnstores and big tables are about. You are selecting all columns that satisfy a date range and some other criteria.  What for? Analytical data is all about computing aggregates and making analysis. Don't ask for *. Compute relevant analysis on the server side. Redesign your app in a manner that does not require displaying pages upon pages of raw data.