Partitioned Tables v. Partitioned Views–Why are they even still around?

Question: Partitioned tables were the new shiny feature in SQL Server 2005 – why are partitioned views even still available? Are there any benefits that they provide?

Answer: This is actually a question I get at almost every event at which I speak. It’s a common question and it’s actually VERY complex to fully describe. I suspect it might take me more than one post to tackle all of the issues but I’ll start with the basics here.

Related: Quick Data Synchronization Using Table Partitioning and SSIS

First and foremost, I’ll start by saying that partitioning is CRITICAL for VLT. What is VLT? It’s about as descriptive as VLDB and it means very large table. (Yes, I just made it up as a new TLA (three-letter acronym) that I’m planning to start using more. However, I’m also going to quantify it a bit more.) Most people speak of VLDBs (very large databases) and they define that as databases that are 100s of gigabytes (many would say that a database that’s 1TB or larger is a VLDB). For me, and in my experience, *many* customers run into problems long before their databases reach 1 TB; their problems tend to start when they have even just one table that starts to get well into double-digit gigabytes.

Think about it, a single table that’s 60 GB presents a variety of problems. And, to highlight where the problems occur – think about these questions in the context of your larger tables:

  • Is all of the data recent? How old is the oldest data in your 5 largest tables?
  • How much of that data changing? Not including the new data coming in – what percentage of the older data needs to be modified?
  • How often are you accessing the older data?
  • How long are your maintenance processes against it?
  • Are you replicating it?
  • Do you have enough memory to fit the table (and all of its indexes) in cache?
  • Do you really need to have indexes on ALL of that data? Or, does your data have different access patterns (which might warrant different indexing strategies)?

For many, these questions start to pose many concerns at table sizes in the mid to upper double-digit gigabytes. The reason why that’s when people notice them more? It’s all about resources. And, the most critical one here is memory. No, SQL Server is not required to put your entire table into memory. However, if you don’t have appropriate indexing strategies then you might require the entire table in memory. If that’s the case, you might be wasting one of the most important resources you have.

So, how can you solve it. Yes, indexing is a BIG part of this. However, even if your indexing strategies aren’t perfect – partitioning can also help. However, partitioning is more of a concept (NOT tied directly to either feature: partitioned tables or partitioned views) but instead a concept of breaking something down into smaller chunks. This is almost always a good thing. Breaking something down into smaller chunks often offers more options for maintenance and management and this can in turn offer better performance. Partitioning is not really directly tied to performance but indirectly it can be HUGELY beneficial. So, for today, I’ll start by saying YES to the question that partitioned views still have benefits; they should not be discounted only because they’re an older feature. Both partitioning strategies provide different benefits for partitioning. To architect the RIGHT solution takes understanding ALL of their pros/cons (ut oh, I hear “it depends” in the distance…sorry!). As an architect, you need to evaluate both PVs and PTs and possibly use them together for the most gains.

I’ll tackle more on this discussion in my next post. In fact, I suspect it’s going to take at least 3 or 4 posts to get through the entire discussion! Feel free to email me PV v. PT questions now so I can be sure to cover your specific concerns. Thanks!

Discuss this Blog Entry 2

on Feb 8, 2012
Few good reasons to still have partitioned views: 1) Backwards compatibility. 2) Not everyone has enterprise edition. Partitioned views works also on standard edition 3) Partition views can work on different databases and different servers. Partitioned tables can work on single database only. 4) Sometimes we need different indexes on the recent data and historical data. We can do it with partitioned views, but we cant do it with partitioned tables. Adi
on Aug 27, 2014

Is Table Partition used to improve the load performance and the query performance?

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×