I often joke that TempDB is like SQL Server’s public toilet: there’s all kinds of crazy stuff happening inside there. Seems like whenever Microsoft needs a holding place for something, they resort to TempDB. That’s not necessarily bad – we gotta put it somewhere – but being aware of these extra uses will come in…
It seems so simple at first: queries create temp tables, load data, and drop ’em. How hard can it be? Spectacularly tricky, as it turns out. Temp tables share some behaviors with real tables, but they also have their own tricky behavior when it comes to statistics and execution plans. We’ll start by creating a…
To be honest, I’m not a big fan of table partitioning in most scenarios. It’s a data loading & unloading feature thanks to the sliding window scenario, but… most folks just don’t load their data an entire partition at a time. However, when we need to do index maintenance for columnstore indexes, that’s exactly what we…
The small Users table isn’t a good fit for clustered columnstore because of its low size and its constant updates. We’ll pick a better table for columnstore indexes – the append-only Votes table – implement a columnstore index on it, then simulate daily load on it, and we’ll see why index maintenance is so hard…
If the quiz in the last module gave you bad news about whether clustered columnstore indexes are a good fit for your tables, take heart. Clustered columnstore isn’t the only way to make your queries go faster: there are also nonclustered columnstore indexes. In this session, we’ll look at the pros & cons of those….
Now you’re starting to see the challenge of maintaining query performance. The combination of these three facts: Columnstore indexes are like lots of partitioned column-level indexes, and Newly changed data ends up in separate rowstore indexes called delta stores that just get scanned, and they don’t have any of their own columnstore indexes, and Selects…
Now that you understand how columnstore data is stored in row groups, column segments, and delta stores – that it’s kinda partitioning on steroids – let’s see how SQL Server uses this sliced-up, diced-up data to more rapidly find the rows you’re looking for. We’ll dig into the different reasons that columnstore indexes perform more…
We started this class with a freshly built columnstore index stored in a grid of row groups and column segments. Next, let’s start changing the data by running deletes, updates, and inserts to see the new objects getting created. We’ll see how insert volume affects whether new rows go into the delta store or new…
In How to Think Like the Engine, I used pieces of paper to explain how clustered and nonclustered rowstore indexes work. Columnstore is different, though. You may have heard that columnstore is like an index on every column, but it’s much more complex than that: it’s really a partitioned index on every column. We’ll take…
