LinkedIn
YouTube
GitHub

Columnstore Indexes

  1. Home
  3. Indexing
  5. Columnstore Indexes

New System Stored Procedures in SQL Server 2017

Brent Ozar
Columnstore Indexes, SQL Server 2017
No Comments
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

Key Lookups and ColumnStore Indexes

Erik Darling
Columnstore Indexes, Execution Plans, Indexing, SQL Server
9 Comments
Beavis v. Butthead I was a bit surprised that this was 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…
Read More

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

Erik Darling
Columnstore Indexes, Development, Execution Plans, Indexing, SQL Server, SQL Server 2017
5 Comments
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

ColumnStore Indexes And Recursive CTEs

Erik Darling
Columnstore Indexes, Indexing, SQL Server
, , ,
7 Comments
Alone Together When I think about SQL Server features, I often picture a high school cafeteria. AGs are making fun of Mirroring, index rebuilds are walking around taking everyone’s lunch, dta is making a glue sandwich, and no one knows who Service Broker is even though they’ve been going to school together since Kindergarten. At…
Read More

How to Add Nonclustered Indexes to Clustered Columnstore Indexes

Brent Ozar
Bad Idea Jeans, Columnstore Indexes, Indexing, SQL Server
, ,
15 Comments
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
Menu