Last week, I gave you a 1-week, $1 deadline, and 777 of you took me up on it. That's awesome! I love having fun with stuff like this.
Let's do it again! You've got 1 week to learn the Fundamentals of Columnstore for $1, and sales close Saturday. Here's the abstract:
You're lazy and overconfident.
You've been reading this blog for a while, and you've been telling yourself, "Yeah, I'm pretty good at this database thing. I've got years of experience under my belt. I know the fundamentals."
Your report queries are too slow.
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 old data.
Folks sometimes ask me, "When a table has more indexes, and SQL Server has more decisions to make, does that slow down execution plan generation?"
Well, maybe, but the table design choice that really screws you on compilation time is partitioning. If you choose to partition your tables, even tiny simple queries can cause dramatically higher CPU times. Even worse, as the famous philosopher once said, "Mo partitions, mo problems."
You manage growing SQL Server databases with shrinking nightly maintenance windows. You just don't have enough time left each night to do the necessary backups, corruption checking, index maintenance, and data jobs that your users and apps want to run. Cloud storage isn't helping the problem, either.
Stop playing Tetris with your job schedules and step back for a second: are we doing the right things, at the right times, with the right SQL Server configuration?
How's that for a niche topic? You're probably never going to know this, but since I had to figure it out the hard way, I'm writing this down so I don't forget it: queries that get trivial optimization may not get partition elimination. This post is going to be a little on the long side…
If I take the Users table from any Stack Overflow database, put an index on Reputation, and write a query to find the top 100 users sorted by reputation, descending:
[crayon-6a61d5a5dab36757305222/]
It doesn't matter whether the index is sorted ascending or descending. SQL Server goes to the end of the index and starts scanning backwards:
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…
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 old data.
I know. You, dear reader, saw that title and you came in here because you're furious. You want foreign key relationships configured in all of your tables to prevent bad data from getting in.
But you gotta make sure to index them, too.
Most of the time, conventional clustered and non-clustered indexes work just fine – but not all the time. When you really need to push performance, hand-crafted special index types can give you an amazing boost. Join Microsoft Certified Master, Brent Ozar, to learn the right use cases for filtered indexes, indexed views, computed columns, table partitioning and more.
Howdy folks. It's time for SQLBits!
This year, I'm teaching a one-day Mastering Index Tuning workshop from 9AM-5PM London time, and you can join in online. Pricing is £300 for the one-day workshop, or £800 for a full conference pass. You can register here.
Remember Clippy, the Microsoft Office assistant from the late 1990s? He would pop up at the slightest provocation and offer to help you do something - usually completely unrelated to the task you were trying to accomplish. Sadly, the Office team told Clippy that he didn't make the stack rankings cut, so he relocated over…
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.
Indexes slow down data changes.
SQL Server doesn't have the concept of asynchronous indexes - copies of the data that it could update later when it's bored. It has to keep every nonclustered index up to date, every single time you change data, like a delete/update/insert.
This morning, Greg Gonzalez (who I respect) posted about visualizing the tipping point with Plan Explorer (a product I respect), and he wrote: The tipping point is the threshold at which a query plan will "tip" from seeking a non-covering nonclustered index to scanning the clustered index or heap. The basic formula is: A clustered…
If you update a row without actually changing its contents, does it still hurt?
Paul White wrote in detail about the impact of non-updating updates, proving that SQL Server works hard to avoid doing extra work where it can. That's a great post, and you should read it.
If your query doesn't have an ORDER BY clause,
you can't reliably predict the order of your results over time.
Sure, it's going to look predictable at first, but down the road, as things change - the indexes, the table, the server's configuration, the size of your data - you can end up with some ugly surprises.
SQL Server 2017 & newer have a new DMV, sys.index_resumable_operations, that show you the percent_completion for index creations and rebuilds. It works, but...only if the data isn't changing. But of course your data is changing - that's the whole point of doing these operations as resumable. If they weren't changing, we could just let the operations finish.
One of the things I love about SQL Server is that during query plan compilation, it takes a moment to consider whether an index would help the query you're running. Regular blog readers will know that I make a lot of jokes about the quality of these recommendations - they're often incredibly bad - but even bad suggestions can be useful if you examine 'em more closely.
