At What Point Should I Split or Partition a Very Large but Simple Table

Question

Our site has some large but simple (INT, INT, DATE) tables for stats. Each table has up to 300,000,000 rows, and gets bigger every day.

The hosting provider has suggested that we split or partition the tables, and I have seen this recommendation elsewhere on numerous occasions.

However...

I am struggling to reconcile this advice with the stated max capacity for SQL Server - a database size of 524,272 terabytes, with table rows limited only by "available storage".

Based upon those figures, the table described above could easily have centillions of rows (10 to the power of 303).

Ah ha you might say, there is a difference between CAPABILITY and PERFORMANCE.

But in virtually every question about SQL Server performance the answer is "It depends.... on table design and query design".

That is why I am asking this question. The table design couldn't be much simpler. Nor could the queries which are simple count(*) operations based on an indexed ID field.

Answer 1

There's a reason that the general advice is that it depends on the table design and the queries on it. My answer to your other post on Stack Exchange says as much. Saying "queries which are simple count(*) operations based on an indexed ID field" doesn't give much information since it says nothing of the cardinality of the set of rows under consideration. Things you can do to mitigate the (as of now perceived) problems are:

1. Partitioning. Specifically, your data seems to be logging-type data. My guess is that you want to get stats by some unit of time (e.g. "widgets per day" or "whozits by hour"). Partition by your quantum (i.e. days or hours in the previous examples) and move partitions off to read-only file groups occasionally

2. On a related note, if the data is write-once, consider pre-aggregating the data once the time period is no longer active. That is, why do I need to keep counting how many events happened on a day from three years ago if that data is never going to change? Once the day is over, count everything in that day, store it somewhere else, and never count it again. In fact, if you never have need of the detailed data (i.e you only ever do aggregations against it), consider deleting it after you count it. If you implement this idea, you can get even more clever with filtered indexes that cover only the "active" period which will make your queries faster because they will not cover the vast majority of your data

But, as my advice in the other post suggests, the only way you're going to know for sure is to load it up with a reasonable amount of data and try it out. All we can do here is say what will probably work in the general case. Without the specifics of your hardware, your data, and your queries, all we can do is guess. And, you may find that once you run the test that I'm proposing that the answer is "there's nothing to do" because it works just fine as is.

Answer 2

Before deciding how large you want the partition to be, please consider the query plan implications of partitioning. From a purely performance perspective, partitions serve as a form of coarse grained index. This can provide extra performance, but it is also a source of performance regressions, especially if the partition key does not appear in all queries. From here, I am assuming you have done this homework already (as it appears you have).

A good rule of thumb for how large a partition size you want is: About half the size of the DRAM you have on the box. The reason for this recommendation is:

1. You can rebuild the indexes on the partition without spilling to tempdb. this is MUCH faster than if you use disk access (even with SSD).
2. While you do this rebuild, you can still hold an entire partition (typically the latest) in DRAM to keep your query performance plodding along nicely.

In other words, you want to have enough DRAM to hold two partitions and the partition size you want depends on the machine you run on. Bigger machines can comfortably handle bigger partitions.

Note that this guidance also provides a minimum size for tempdb: At least the size of your largest partition (so you CAN spill the index build there if there is not enough DRAM when you rebuild an index).

You may consider smaller partition sizes than this, but if you do, this is typically intended for performance optimisation and not to support manageability of the data.

There are a ton of other tricks you can play with partitions. For example, compressing, aggregation or using Fill Factor 100 on partitions that are read only. But the basic principle still is: Try to keep each chunk of data you manage smaller than DRAM.

PS: Happy to see you do not take "it depends" as an answer, always ask for a method to get the to the answer.

Answer 3

I'm going to take a different approach and note that partitioning (in SQL Server) is primarily a data management feature with query performance being a possible secondary outcome, depending on how you manage it.¹

As noted in the linked article, the primary benefit of partitioning is that you can quickly move data by using partition switching. For example, you can archive "cooler" data to slower storage and keep your "hot" data on fast storage. At regularly-scheduled intervals, you can quickly archive data by rolling it to archive partition(s) without having to go through the process of waiting for an ETL to perform the transfer. As noted in one of the early comments to your question, though, this will take some careful thought and planning before implementing it. Also, depending on the SQL Server edition that you use (Enterprise), you can leverage data compression to compress individual partitions.

As far as performance is concerned, you can change lock escalation to AUTO (default is TABLE) like so:

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO);
GO

Additionally, you might get partition elimination but your query patterns would need to fit a very specific and repeatable pattern within your system - the partitioning key and clustering key and any unique keys become interconnected and very important. If this balance isn't treated acknowledged and designed around, you end up with performance nightmares.

With the advent of SQL Server 2014, you can also take advantage of incremental statistics which is very handy if you proactively monitor and update/create statitsics on large tables.

So, at what point should a table be partitioned? That depends on your query workload, the profile of your data, but most importantly, it depends on which of the management features of partitioning you absolutely must leverage. Partitioning isn't for query performance, it's primarily for data management and administration.

Answer 4

Table Partitioning, like several other features, is quite often (or possibly even most often?) used inappropriately. Any of the cautions I would give have been nicely stated in @swasheck's answer.

In addition, an alternative to consider is Partitioned Views. This is a way of keeping fully separate tables but linking them together via UNION ALL in a View. Each table requires a CHECK CONSTRAINT enforcing which range of data each table holds. The optimizer knows of this construct and should only access the underlying tables that are required by a query using the View (I don't recall all of the requirements to have this work as intended so please see the CREATE VIEW link at the bottom, but I have set it up before and it wasn't difficult to get it to work as expected).

There are definitely some restrictions, and a main downside is that it is less transparent as compared to Table Partitioning. However, a main benefit is that these are separate tables, and hence the statistics are completely separate, whereas with a Partitioned Table they are for the entire table (even if, starting in SQL Server 2014, you can update the statistics per partition).

If you are not going to be making use of switching partitions in and out, you should consider this option. Especially if the older data isn't changing much since the tables holding the older data don't need their indexes / statistics updated nearly as often (or possibly ever if that data never changes).

Another downside to Table Partitioning that goes unmentioned / unnoticed all too often is that starting in SQL Server 2012, you no longer get a "free" UPDATE STATISTICS WITH FULLSCAN when rebuilding partitioned indexes. You still do get this update stats with a rebuild on non-partitioned indexes, which the indexes on the tables in a Partitioned View would be :).

For more information on Partitioned Views, please check out the MSDN page for CREATE VIEW and look for the section on "Partitioned Views" under "Remarks".