[Video] Office Hours: Long Answers Edition
Today's questions from https://pollgab.com/room/brento require a little bit longer answers:
https://youtu.be/W-HKNMh8JQE
Table Partitioning
Partitioned tables and indexes, elimination, boundaries, and tradeoffs.
26 associated posts22 primary posts
Office Hours: “Read This” Edition
Sometimes when people post questions at https://pollgab.com/room/brento, the answer is just a link, nothing more, nothing less. It doesn't make sense for me to rehash something that's been covered really well online, and I want to link 'em to the best resource possible. Petr: Huge slowdown of basic queries when using a partitioned table (Partition_Key…
[Video] Office Hours at the Arctic Circle
Just after passing the Arctic Circle marker and celebrating with a glass of champagne, I took your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/gyyxKrv45bQ
Office Hours: Dodging Work Edition
Post your questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me cover. Today, I'm dodging work, so I went through your questions while I waited for the coffee shop to open:
https://youtu.be/xBxvb6vy97U
Partitioned Tables Cause Longer Plan Compilation Times.
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."
Autoparameterized Trivial Queries May Not Get Partition Elimination
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…
T-SQL & Development
How to Create a Table with a Partitioned Clustered Columnstore Index
If you need to create a table and you want it to be partitioned right from the start, AND you want it to have a clustered columnstore index, here's a creation T-SQL example:
[crayon-6a6ef4f346b48054469357/]
The partition scheme name goes on the outside of the parenthesis, the index name goes on the inside, and the order of the whole thing is a little counter-intuitive.
Production DBA
DBA Training Plan 17: Should You Partition Your Tables?
In the last episode, I talked about knowing when it's time to scale out: identifying when our data is getting to be so large that we have to split it across multiple servers, and I explained why that is so challenging. But what about table partitioning - SQL Server's ability to break up a single table into smaller ones on the same server?
Production DBA
Database Design Choices To Consider When You’re Worried About Scale
Tipping the Kilter
I'm intentionally avoiding physical/hardware choices in this post, like how much hardware to buy, and scaling out vs. scaling up.
Index Tuning Week: Fixing Nonaligned Indexes On Partitioned Tables
Unquam Oblite
This post will not change your life, but it will help me remember something.
When you decide to partition a table to take advantage of data management features, because IT IS NOT A PERFORMANCE FEATURE, or you have an existing partitioned table that takes advantage of data management features, because IT IS NOT A PERFORMANCE FEATURE, you may have or end up with nonclustered indexes that aren't aligned with the partitioning scheme.
Performance Tuning
Implied Predicate and Partition Elimination
>implying
Way back when, I posted about turning the Votes table in the Stack Overflow database into a Partitioned View.
While working on related demos recently, I came across something kind of cool. It works for both partitioned tables and views, assuming you've done some things right.
Performance Tuning
Partitioned Views, Aggregates, and Cool Query Plans
The Max for the Minimum
Paul White (obviously of course as always) has a Great Post, Brent® about Aggregates on partitioned tables
Well, I'm not that smart or good looking, so I'll have to settle for a So-So Post about this.
Performance Tuning
Query Plans: Trivial Optimization vs Simple Parameterization
Facemaking
You know when you think you know something, and the obviousness of it makes you all the more confident that you know it?
That's usually the first sign that there's a giant gotcha waiting for you.
[Video] Office Hours 2016/09/28 (With Transcriptions)
This week, Brent, Richie, Tara, and Erik, discuss non-clustered indexes, dynamic SQL, replication, setups for servers dedicated to reporting services and warehouse databases, cross-cluster migrations, employment opportunities in the current market, and much, much more!
Here’s the video on YouTube:
Performance Tuning
Partitioned Views: A How-To Guide
This is not about table partitioning If you want to learn about that, there's a whole great list of links here, and the Best Blogger Alive has a tremendous post on why table partitioning won't make your queries any faster over here. With that out of the way, let's talk about partitioned views, and then…
Performance Tuning
Temporal Tables, Partitioning, and ColumnStore Indexes
This post is mostly a thought experiment The thought was something along the lines of: I have a table I want to keep temporal history of. I don't plan on keeping a lot of data in that table, but the history table can accumulate quite a bit of data. I want to partition the history…
Performance Tuning
Why is This Partitioned Query Slower?
It's tempting to think that table partitioning will improve query performance. After all, it's an Enterprise Edition feature-- it must have a lot of magic, right? Table partitioning does have magic for the right situations. It shines when you want to add a large amount of data to a table or remove a large amount…
How to Use Partitioning to Make kCura Relativity Faster
kCura Relativity is an e-discovery program used by law firms to find evidence quickly. I've blogged about performance tuning Relativity, and today I'm going to go a little deeper to explain why DBAs have to be aware of Relativity database contents.
In Relativity, every workspace (case) lives in its own SQL Server database. That one database houses:
Performance Tuning
Filtered Indexes vs. Table Partitioning
It was a dark and stormy… Oh, wrong story. It was actually a warm, sunny afternoon in Charlotte, NC. I was presenting “Index Methods You’re Not Using” at PASS Summit. In this talk, I discussed how indexed views, filtered indexes, and compressed indexes can improve your query performance by reducing I/O.
From stage right, an intrepid audience member raised his hand and asked, “Can you think of an example of when you would use filtered indexes instead of partitioning?”
Best Practices + Table Partitioning: Merging Boundary Points
One of the many best practices for SQL Server's table partitioning feature is to create "extra" empty partitions around your data. This is explained in SQL Server Books Online in the page on altering a Partition Function: Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before…
