What’s Faster: IN or OR? Columnstore Edition

Pinal Dave recently ignited a storm of controversy when he quizzed readers about which one of these would be faster on AdventureWorks2019: SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID IN (1, 2, 3); SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 1 OR CustomerID = 2 OR CustomerID = 3; 123456789 SELECT *FROM Sales.SalesOrderHeaderWHERE CustomerID IN (1,…
Read More
Fundamentals of Columnstore

Lock Escalation Sucks on Columnstore Indexes.

Columnstore Indexes
1 Comment
If you’ve got a regular rowstore table and you need to modify thousands of rows, you can use the fast ordered delete technique to delete rows in batches without hitting the lock escalation threshold. That’s great for rowstore indexes, but…columnstore indexes are different. To demo this technique, I’m going to use the setup from my…
Read More
Brent Ozar reading

New System Stored Procedures in SQL Server 2017

Columnstore Indexes, SQL Server 2017
Got a few new stored procs for columnstore indexes: sp_add_columnstore_column_dictionary – input params for @table_id, @column_id sp_is_columnstore_column_dictionary_enabled – as above, plus output param for @is_enabled bit sp_remove_columnstore_column_dictionary – input params for @table_id, @column_id And a couple for snapshot views (which appear to be related to the new snapshot messages in sys.messages) sp_refresh_single_snapshot_view – input param for @view_name…
Read More

Why Columnstore Indexes May Still Do Key Lookups

I was a bit surprised that key lookups were a possibility with ColumnStore indexes, since “keys” aren’t really their strong point, but since we’re now able to have both clustered ColumnStore indexes alongside row store nonclustered indexes AND nonclustered ColumnStore indexes on tables with row store clustered indexes, this kind of stuff should get a…
Read More

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

Yazoo Over on his blog, fellow Query Plan aficionado Joe Obbish has a Great Post, Brent® about query patterns that qualify for Rowgroup Elimination. This is really important to performance! It allows scans to skip over stuff it doesn’t need, like skipping over the dialog in, uh… movies with really good fight scenes. Car chases?…
Read More

How to Add Nonclustered Indexes to Clustered Columnstore Indexes

SQL Server 2012 introduced nonclustered columnstore indexes, but I never saw them used in the wild simply because once created, they made the underlying table read-only. Not a lot of folks like read-only tables. (Bad news, by the way – that limitation hasn’t disappeared in 2014.) SQL Server 2014 brings clustered columnstore indexes, and they’re…
Read More