Sometimes I really hate my job. Forever now, FOREVER, it's been a standard thing where I can say, "When you're measuring storage performance during index and query tuning, you should always use logical reads, not physical reads, because logical reads are repeatable, and physical reads aren't. Physical reads can change based on what's in cache,…
Pinal Dave recently ignited a storm of controversy when he quizzed readers about which one of these would be faster on AdventureWorks2019:
[crayon-6a7445d84a1db929648351/]
I laughed so hard when I saw the storm of responses on Twitter. People sure do get passionate about this kind of thing. If you ever wanna witness patience and generosity in action, look at Pinal's responses to this tweet.
There's a widespread misconception that SQL Server's columnstore indexes are like an index on every column.
I debunk that myth in the first 30 minutes of my Fundamentals of Columnstore class, where I explain that a better way to think of them is that your table is broken up into groups of rows (1M rows or less per group), and in each group, there's an index on every column.
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 Fundamentals of Columnstore class:
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…
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.
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 each method 3 ways:
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…
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 closer look.
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?
Alone Together
When I think about SQL Server features, I often picture a high school cafeteria.
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 time. A long time from now. When I've raised an army, in accordance with ancient prophecy.
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…
