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.
Best Answer
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:
sys.column_store_row_groups. For 1.1B rows you should have ~1000 rowgroups, all closed and compressed.Now, your query:
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.