2

I have a SQL 2016 table that looks as follows:

enter image description here

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:

enter image description here

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.

  • Add SET STATISTICS IO output to your answer. That should tell us how many rowgroups were skipped (if any) – Joe Obbish Feb 20 '17 at 14:24
  • @JoeObbish - I have done so and added it to my post. – Randy Minder Feb 20 '17 at 14:32
  • You're returning 21K rows to Management Studio? I'd check to see how much of the waits are ASYNC_NETWORK_IO, which is likely the time SQL Server waited for SSMS to render that grid... – Aaron Bertrand Feb 20 '17 at 15:02
4

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:

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.

  • You are assuming CSIs (and big tables) are only for aggregation of data. That's not what SQL 2016 is all about. SQL 2016 is about combing analytical operations together with OLTP operations in a single database environment (containing big data), which is what I have to plan for. I'm just trying to get a feel for when CSIs excel and when they don't and need to be supplemented with non-columnstore indexes. – Randy Minder Feb 20 '17 at 14:42
  • 1
    @RandyMinder You can safely assume that columnstore indexes will not excel at returning 21,000 raw rows of SELECT * to SSMS. :-) – Aaron Bertrand Feb 20 '17 at 15:03
  • @AaronBertrand - I generated live query statistics while executing the query in SSMS. SSMS isn't the issue. The actual scan operation took 97% of the time. – Randy Minder Feb 20 '17 at 15:10
  • @Randy Ok, if you don't think returning 21K rows to SSMS is an issue, there's still the fact that you're trying to read 1.1 billion rows to seek 21K but there's no way to perform a "seek"... – Aaron Bertrand Feb 20 '17 at 15:20
  • @AaronBertrand - Again, I'm just trying to figure out what CSIs are good at (and when) and when I need to suppliment them with non-CSI indexes. This is a learning exercise for me. – Randy Minder Feb 20 '17 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.