Nested Partitioned Views

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

A few weeks ago I had a customer engagement, where the customer wanted to implement Nested Partitioned Views. Before we go into the details of this approach and if it could work, I want to give you an overview about the requirements, and why the customer wanted to have Nested Partitioned Views.

The main idea was that the customer had to implement their SQL Server database for both Standard and Enterprise Edition. With the Enterprise Edition the customer used a combination of Partitioned Views and Partitioned Tables, as you can see it from the following picture.

Some of you might ask, why the heck you want to implement such a scenario. The answer is easy: Partitioned Views itself have some disadvantages, and on the other hand Partitioned Tables also have some disadvantages. But when you combine then you will get the benefits from BOTH worlds. The following list describes the pros and cons of Partitioned Views in SQL Server.

  • Pros
    • Available on any Edition of SQL Server
    • Each table has its own better Statistics
    • Index Rebuilds of any table is an ONLINE operation (when done on Enterprise Edition)
    • Each table can be indexed independently
      • Operational Data
      • Historical Data
  • Cons
    • Lots of tables to administer
    • Indexes must be created on individual tables
    • Check Constraints are needed for Table Elimination
    • Gaps and overlapping values are possible

The following list describes the pros and cons of Partitioned Tables in SQL Server.

  • Pros
    • Only one table to administer
    • Gaps and overlapping values are NOT possible
    • Completely transparent
  • Cons
    • Available only on the Enterprise Edition of SQL Server
    • Table-Level Statistics
      • Less accurate on larger Partitioned Tables
      • Filtered Statistics can help here…
    • Partition Level Index Rebuilds are OFFLINE operations
      • Only the whole Partitioned Table can be rebuild ONLINE
    • Supports Partitioning only over a single column
      • Persisted Computed Columns are needed

As you can see from this list, there are a lot of disadvantages of Partitioned Tables, especially in the area of maintenance (less accurate Statistics, Partition Level Index Rebuilds). As you have seen in the previous figure, you can achieve a very powerful solution, when you combine Partitioned Views with Partitioned Tables.

Because the customer also had to support the database on the Standard Edition of SQL Server (where Partitioned Tables are not available), they just wanted to substitute the Partitioned Tables with Partitioned Views – so in the final solution you just have Partitioned Views inside Partitioned Views – just Nested Partitioned Views. The following picture illustrates this concept.

So the question was now, if this could be done with SQL Server? Of course, you can make it this way, but what’s about performance? SQL Server can use with a single Partitioned View Table Elimination when each participating table has a corresponding CHECK constraint defined. Therefore SQL Server only has to query the necessary tables – it’s almost the same concept as Partition Elimination with Partitioned Tables. But would Table Elimination work with Nested Partitioned Views? Because there is no chance to define a CHECK constraint on the inner Partitioned Views…

Before I give you the ultimate answer on that question, I want to walk through a complete scenario, how you can setup and implement Nested Partitioned Views as illustrated in the last picture. In the first step we are creating a new database, and adding 2 new file groups to it, where the data of 2007 and 2008 is stored.

In each file group we create a Partitioned View which subdivides the data into 4 separate tables for each quarter (Q1, Q2, Q3, Q4). The following listing shows how to setup the Partitioned View for the 2007 sales data.

As you can see from the listing, each individual table has a corresponding CHECK constraint, so that SQL Server can eliminate the tables which don’t have to be accessed, when we query our Partitioned View. The next listing shows how to setup the Partitioned View for the 2008 sales data.

After we have now setup the Partitioned Views for 2007 and 2008, we finally create another Partitioned View which just unions the data of the other 2 Partitioned Views together:

So now we have created our Nested Partitioned View: Partitioned Views within Partitioned Views. The final thing that we now have to check is, if SQL Server is able to do Table Elimination with our Nested Partitioned View. When we talk about Table Elimination (or Partition Elimination with Partitioned Table) we have to differentiate between 2 types of elimination:

  • Static Elimination
  • Dynamic Elimination

Static Elimination means that you provide within your query a static value. So during the compilation of the Execution Plan the Query Optimizer already knows which data we are accessing. In that case SQL Server generates an Execution Plan, which only references the relevant data. Look at the following query:

We are providing here hard coded values, so the Query Optimizer can sniff them, and generates an Execution Plan, which only accesses the Sales2007_Q3 table. So Static Elimination works without any problems with Nested Partitioned Views, as you can see it in the following Execution Plan:

Static Elimination is fine, if you are doing demos etc., but in a production database when you are calling your query with different values through parameters, SQL Server has to do Dynamic Elimination. Dynamic Elimination means that the Query Optimizer has to generate an Execution Plan, which can’t take into account during the compilation which tables you are accessing, because those tables are just determined during runtime – therefore the name Dynamic Elimination. So let’s imagine we are executing the following parameterized query:

In that case SQL Server can’t determine which data is accessed, because the referenced variables are runtime constructs, they are not available during the compilation phase of our query. The above query produces the following Execution Plan:

When you look at the first step on that plan, you might think that SQL Server has to seek into each table to get the corresponding records. The Query Optimizer estimates each Clustered Index Seek with around 12%. But that’s only the half-truth of that Execution Plan! The real power lies in the Filter Operator which comes before the Clustered Index Seek. When you look at the Tooltip Window of the Filter Operator inside SQL Server Management Studio, you can see that the operator has a so-called Startup Expression Predicate:

As you can see from the picture, the Startup Expression Predicate has the same value as the CHECK constraint on the table itself. So that Filter Operator only calls the Clustered Index Seek operator on the underlying table when the Startup Expression Predicate is evaluated to true! You can also cross-check this with the property Actual Executions on the Clustered Index Seek Operators:

In our case SQL Server only executed the Clustered Index Seek Operator on the Sales2007_Q3 table. On all other tables the Filter Operator prevented through the Startup Expression Predicate the execution of the Clustered Index Seeks! That’s a really powerful concept for Dynamic Table Elimination inside Partitioned Views.

As you can see from this example, Nested Partitioned Views are working without any problems in SQL Server, and on the other hand SQL Server uses a Startup Expression Predicate inside a Filter Operator to drive Dynamic Table Elimination when you work with Partitioned Views – it doesn’t matter here if those Views are nested or not. In that case it is really important that you check the whole Execution Plan to get a better understanding what SQL Server is actually doing for your query. When you just look on that plan (without going into the details), you might think that SQL Server has to access every table, but that’s not really true!

A big Thank You goes to Paul White (Weblog, Twitter) who helped me to understand that behavior and implementation strategy of SQL Server. You can find the whole sample of Nested Partitioned Views here for download.

Thanks for reading


Do you like this post? Why not share it with the SQL Server community?

SQL Server Quickie #3 – Allocation Units

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Today I have uploaded my 3rd SQL Server Quickie to YouTube. In this episode I’m talking about Allocation Units in SQL Server.


Thanks for reading & watching


Do you like this post? Why not share it with the SQL Server community?

I’m a man, I’m a husband, I’m a father of 2 boys, and my life just sucks.

I’m a man, I’m a husband, I’m a father of 2 boys, and my life just sucks. Don’t prejudice too fast on me, and let me tell you why life just sucks because of these reasons.

I’m a father, married man, and our first son Philip was born 2.5 years ago on a cold Thursday morning in February. It was really an awesome feeling to hold your own baby in your hands. But you have to be aware of the fact, that you are a man, a father, and not a woman, and not the mother. After Philip’s birth, my wife and Philip had to stay a few more days in the hospital, just to check that everything is fine with them. Every day the nurse told me how I have to hold my baby (e.g. you have to stabilize the head, because there are no muscles there after the birth). They are telling you this every day, because you are a man, you can’t know these tiny little facts after their first “introduction”.

We are a very open family, which means it doesn’t bother for us, who is doing what in our household. I was staying up in the night, when Philip was crying, I have changed his swaddling bands, I have stayed with him, when he couldn’t sleep during the night, so that at least my wife was able to get some sleep. That’s the great part of being a father.

I’ve also done a lot of different activities alone with Philip, because after 1.5 years my wife started to work again for some hours during the week. We were going to a near lake, feeding pigeons, and we were doing various other things. I really liked this time – did I? Just imagine you have to change his swaddling bands while you are not at home. A wife just goes to a public toilet, and there is some space where you can change his swaddling bands without any interruptions and without any questions. But as a man, as a father you don’t have this option. Your only option is to the gent’s toilet, but how you will change his swaddling bands there? There are just 2 options: directly on the floor, or directly in the urinal… not very handy, trust me… The other more interesting option is just to use a woman’s toilet. Just imagine you are standing at the changing table in the woman’s toilet, your baby is full of shi…, its summer, you wear short trousers, a lady enters the room, and the first thing she sees are your hairy legs… “Oh my god, there’s a man, there’s a man, I need help, I need help!!!!” You reaction: take your baby as fast as possible and run, run, – run for your life!

I’ve also visited with Philip every Monday for 1.5 hours a baby gaming round. I think it’s almost the same as Tubber parties, but just with babies. But there is one important difference: you are a man, a father, in a round of mothers. They are looking at you surprisingly, they are thinking how the hell he should handle that baby, when the swaddling bands are full of shi… etc. To get their acceptance there is only one option: you have to join their discussions. Easy isn’t it? They are talking about their latest lingerie shopping trips, about their latest love toys they have acquired, and how bad their last night was because of their awkwardly mans. So you will remain the outsider…

Normally you will say, why the heck you should care of these little things, you are a man, you are a father, just enjoy your life with your dears. But today my whole opinion just changed with one small sentence. But let’s start from the beginning. Since Monday this week Philip goes to the kindergarten – for 3 hours after the morning until lunch. Our second son Daniel was born exactly 4 weeks ago. For that reason, my wife and I have decided that we want to bring Philip together to the kindergarten for the first days, so that he is not in troubles when my wife or I are bringing him alone to the kindergarten. Sometimes our nights are currently a little bit challenging because of Daniel, therefore our plan was that I’m bringing him alone to the kindergarten after such nights, as long as my travel schedule permits it.

There is also a so-called familiarization phase of one month at the kindergarten, so that the kids are phased into that new world as smooth as possible. So their general rule of thumb is that only one part (mother or father) should bring the kid to the kindergarten. But we were going both, because my wife can’t just go with Philip AND Daniel, who is just 4 weeks old. His swaddling bands are ALWAYS full of shi…, and he is always hungry, and my wife is feeding him. So the only option to support my wife is that I’m coming along with her and help were I can. We have done this yesterday for the first time, and everything was fine. But today’s morning was different!

When we entered the kindergarten, the female director of the kindergarten was coming straight to my wife, and started talking to her: “Hello Mrs. Aschenbrenner. You know it’s not allowed and permitted that you and your “man” (her finger was pointing to me in the background) are coming both to the kindergarten? You also have to follow our rules. There are no exceptions to this.” I just though OMG…

Then she looked directly at me and said the following sentence directly into my eyes: “Fathers are not welcomed during the familiarization phase, because the mother is the psychological parent of the child.” Just for my German readers: “Väter sind nicht erwünscht während der Eingewöhnungsphase, weil die Mutter die Bezugsperson des Kindes ist“.

I’m a man, I’m a husband, I’m a father of 2 boys, and my life just sucks.