Quiz on partitioned tables and columnstore indexes

Q1. What should “Segment” really say in Statistics IO output?

  •  Delta Store
  •  Rowgroup
  •  B+ Tree
  •  Partition
Rowgroup

Q2. Can you get both rowgroup elimination and partition elimination if you have a partitioned columnstore index?

  •  If you have a partitioned columnstore index, you can’t get either
  •  Yes, you can get both
  •  No, if you get partition elimination you can’t get rowgroup elimination
  •  No, if you get rowgroup elimination you can’t get partition elimination
Yes, you can get both

Q3. When you see “Actual Partition Count” of 0 on a columnstore index, how should you interpret this?

  •  No data access needed to be done
  •  None of the rows were in the delta store
  •  The table or index is empty
  •  Partition elimination wasn’t done, but possibly rowgroup elimination was done
Partition elimination wasn’t done, but possibly rowgroup elimination was done

Q4. Why couldn’t we get partition elimination on the FirstNameId column in the demo in “More fun with rowgroup elimination and predicate pushdown”?

  •  We were too busy giggling at the word “elimination”
  •  The base table and nonclustered columnstore index were both partitioned on FakeBirthDateStamp
  •  The column is an INT, and you can’t get partition elimination on INT data type columns
  •  Our partitioned indexes were not “aligned”
The base table and nonclustered columnstore index were both partitioned on FakeBirthDateStamp

Q5. What did it mean when we saw “Actual Number of Rows” was 0 on our Columnstore Index Scan for the query that was doing a COUNT(*) in the demo in “More fun with rowgroup elimination and predicate pushdown”?

  •  The final count was zero rows
  •  No rows needed to be counted by the hash match aggregate
  •  No segments were able to be eliminated
  •  Rowgroup elimination occurred even though partition elimination couldn’t occur
No rows needed to be counted by the hash match aggregate

Q6. What is one issue with string data type columns in columnstore indexes as of SQL Server 2016?

  •  You can’t use these columns as a partitioning key
  •  You can’t have these columns in a columnstore index
  •  You can’t get rowgroup elimination on these columns
  •  There are too many cats playing with the string
You can’t get rowgroup elimination on these columns

Q7. As of SQL Server 2016 SP1, batch mode can only be used by SQL Server when your query…

  •  … has a hint directing the query to use batch mode
  •  … has a columnstore index operator in the query execution plan
  •  … uses a linked server
  •  … references a table with a columnstore index in the TSQL
… references a table with a columnstore index in the TSQL

Q8. How many values can be processed in a “batch” when batch mode is in use?

  •  Up to 1 million, but it may be less
  •  Exactly 900 in each batch
  •  Up to 900, but it may be less
  •  Exactly one million in each batch
Up to 900, but it may be less

Q9. What does Query Time Stats indicate on an operator in row mode?

  • Values for just that node
  • Cumulative values for the node and its children
Cumulative values for the node and its children

Q10. What does Query Time Stats indicate on an operator in batch mode?

  •  Values for just that node
  •  Cumulative values for the node and its children
Values for just that node
Back to: Execution Plans: Partitioned Tables & Columnstore Indexes (1 hour 30 minutes) > Quiz and Course Highlights

Share a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu