Columnstore Indexes

Want to use columnstore indexes? Take the ColumnScore test.

October 1, 2020

When columnstore indexes first came out in SQL Server 2012, they didn’t get a lot of adoption. Adding a columnstore index made your entire table read-only. I often talk about how indexing is a tradeoff between fast reads and slow writes, but not a lot of folks can make their writes quite that slow. Thankfully, Microsoft…

Announcing a New Class: Fundamentals of Columnstore

September 29, 2020

Brent Ozar

Columnstore Indexes

Your report queries are too slow. Will columnstore indexes help? You’ve tried throwing some hardware at it: your production SQL Server has 12 CPU cores or more, 128GB RAM, and SQL Server 2016 or newer. It’s still not enough to handle your growing data. It’s already up over 250GB, and they’re not letting you purge…

When a Columnstore Index Makes Your Query Fail

June 10, 2020

Brent Ozar

Columnstore Indexes

If you wanna count the number of rows in a table, I’ve usually said, “It doesn’t matter what you put inside the COUNT() – it can be COUNT(*), COUNT(Id), COUNT(1), or COUNT(‘Chocula’), or even COUNT(1/0) – it all works the same.” And that was true right up until I added a columnstore index. Start with…

How to Make SELECT COUNT(*) Queries Crazy Fast

December 26, 2019

Brent Ozar

Columnstore Indexes, Execution Plans

When you run a SELECT COUNT(*), the speed of the results depends a lot on the structure & settings of the database. Let’s do an exploration of the Votes table in the Stack Overflow database, specifically the 2018-06 ~300GB version where the Votes table has 150,784,380 rows taking up ~5.3GB of space. I’m going to measure…

Research Paper Week: Query Execution in Column-Oriented Database Systems

August 20, 2019

Brent Ozar

Columnstore Indexes

This week, I’m sharing some of my favorite papers that I’ve read. Sometimes they’re about future technologies that haven’t shipped yet – and may never ship! Sometimes, like this one, they’re not from Microsoft at all, but from someone else in the industry who’s explaining a problem that we face in SQL Server. SQL Server…

New System Stored Procedures in SQL Server 2017

October 2, 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…

Key Lookups and ColumnStore Indexes

September 22, 2017

Erik Darling

Columnstore Indexes, Execution Plans, Indexing, SQL Server

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…

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

August 23, 2017

Erik Darling

Columnstore Indexes, Development, Execution Plans, Indexing, Parameter Sniffing, SQL Server, SQL Server 2017

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?…

ColumnStore Indexes And Recursive CTEs

July 27, 2017

Erik Darling

Columnstore Indexes, Indexing, SQL Server

dba, development, performance tuning, sql server

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…

Clustered Index key columns in Nonclustered Indexes

August 12, 2015

Erik Darling

Columnstore Indexes, Indexing, SQL Server

dba, development

Clustered indexes are fundamental And I’m not just saying that because Kendra is my spiritual adviser! They are not ~a copy~ of the table, they are the table, ordered by the column(s) you choose as the key. It could be one. It could be a few. It could be a GUID! But that’s for another…

How to Add Nonclustered Indexes to Clustered Columnstore Indexes

March 25, 2014

Brent Ozar

Bad Idea Jeans, Columnstore Indexes, Indexing, SQL Server

clustered columnstore, columnstore, sql 2014

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…