2

I understand that table partitioning is mostly done for data management. I understand that table maintenance becomes more difficult for large tables, since e.g. an index rebuild cannot fit in memory or because e.g. sorts scale with O(n*log(n)) and this causes extra problems with large tables.

Can someone give some examples how large a table should be, relative to the amount of memory on the server, to be a definite candidate for partitioning? Say the table is larger than the amount of RAM. Will it be bad practice not to partition the table?

I am looking for some principles I can base my analysis upon.

In the article linked to in the answer below it also mentions:

  • Long-running index maintenance jobs (or an inability to run them at all because they would take so long) reference.

So I guess my question can be formulated as: When will SQL Server start having problems with rowstore index rebuilds when they are not partitioned, given the hardware specifications?

I am not convinced that "it is not about the size of the table". When an index cannot be rebuilt then I would say partitioning is very much related to the size of the table.

0

3 Answers 3

5

There's not really a size-based formula to start partitioning a table. Unfortunately, it's a lot more complicated than that.

A tangent on performance vs data management

I would word it even more strongly than saying partitioning is "mostly done for data management." I would say that partitioning is a data management feature--full stop. Some folks will see performance benefits, but that's really a side benefit in certain scenarios related to coding & index patterns that are forced to accomplish partition alignment. Those coding & indexing patterns on their own often bring similar benefit. But that's a bit of a ranty tangent from your actual question.

The important thing is that partitioning is a data management feature, not a performance feature.

When to use partitioning?

Kendra Little wrote a great post titled How To Decide if You Should Use Partitioning and I suggest reading that. It's a few years old, but still fully accurate and relevant.

The summary is that there are certain "stories" around data management (not size) that are good drivers for adopting partitioning. Further, any size considerations will usually be related to the size of a partition rather than the size of the overall data.

A few of the "textbook" reasons are:

  • There is a specific key (such as date windows) that are used to identify different segments (partitions) of your data.
  • When doing ETL/ELT, using a separate/partition as a "landing" spot, then SWITCH the partition into the main table.
  • When purging old data, partitioning by date so that old data can be purged by truncating/dropping old partitions, rather than using DELETEs.
  • Using different compression levels on "active" data compared to archive data.
  • Query patterns vary between different segments of data (such as active vs archive, or other partitioning key).

That's just a quick list off the top of my head, but needless to say it's more about how you manage it, rather than specifically about size. If you partition by an arbitrary key that isn't related to your data management needs, you're likely to gain a bunch of complexity without realizing any benefits.

2
  • Thanks, it is a great answer, can you add something about maintenance (e.g. index rebuilds) and memory on server? My understanding is that if one has trouble rebuilding the index then one should start to think about partitioning. Even in the article linked it says "Long-running index maintenance jobs (or an inability to run them at all because they would take so long)". So if you prefer, when will SQL Server start having problems with regular rowstore index maintenance?
    – xhr489
    Jun 25, 2021 at 21:35
  • 1
    Good answer, though I believe that whether partitioning is data management or performance management is a matter of opinion. For example, you state it is data management, however the examples you gave were all performance related with the exception of compression which could be considered both. If you had given an example of backing up individual partitions, that would be data management. An interested perspective though, thank you for sharing! Jun 25, 2021 at 21:56
2

Regarding the comments, Partitioning is a feature for data management as AMtwo mentions. To say it's for performance could be confusing, as a lot of people assume that would mean Partitioning directly helps performance with querying the data via DQL and DML statements (SELECT, INSERT, UPDATE, DELETE), which is not true.

Rather, it does improve performance of DDL statements which fall under the umbrella of data management, such as index maintenance (because you can rebuild individual index partitions separately). This can result in better overall server performance by balancing the workload of your data management tasks with the normal DQL and DML queries that are running.

As far as Memory goes, when you a query a Table, it doesn't need to be loaded entirely into Memory for the data your query requests to be served. Rather, the data is stored in smaller physical units of measure called Pages (in SQL Server these are 8 KB each in size by default). Pages are what are loaded into Memory from the Disk when the data is being served. So that makes Partitioning independent, in a sense, to the amount of Memory your server has relative to the total size of a Table or an individual partition when you choose to Partition said Table.

5
  • With a sort all pages have to read into the data cache and if there is not enough memory then the sort has to spill to disk. So if it is impossible for the server to grant the memory grant required to make the sort then I would still say my answer below should hold, i.e. a large table relative to memory on server should be partitioned...But it makes sense what you write about DDL and DML...
    – xhr489
    Jun 27, 2021 at 16:19
  • You write "to say it's for performance could be confusing". I agree, but to say it is not related to the size of the table is even more confusing.
    – xhr489
    Jun 27, 2021 at 16:27
  • Even in the documentation it says: "Partitioning large tables or indexes can have the following manageability and performance benefits." (learn.microsoft.com/en-us/sql/relational-databases/partitions/…). So I don't understand all this dogmatism about "it is not about the size of the table and it is not about performance".
    – xhr489
    Jun 27, 2021 at 16:34
  • @xhr489 With a sort on what though?...if there are predicates in a SELECT query that filters the data down, which is not uncommon, then generally the data will be filtered before the sort is applied. So even then, not all data pages need to be concurrently loaded in Memory. In the opposite case, where there are no filtering predicates and the entire Table needs to be sorted, then Partitioning isn't going to improve performance any differently since all the pages need to be loaded off disk.
    – J.D.
    Jun 27, 2021 at 19:25
  • My question though, in general, how big should a table be (relative to the RAM), in order to start to cause problems. I have never had to tune maintenance tasks, so I am just curious since I might have to start also doing maintenance tasks on a small server. I agree that partitioning is not a performance tool for DML statements, but as you mention it is a performance tool for DDL statements.
    – xhr489
    Jun 27, 2021 at 20:22
-2

Well, I don't agree with the answer and my own analysis says that the size of the table is the main factor when deciding when to partition. You don't need to think about these things when the table is 1MB or 1GB.

It also depends on the hardware, a table might be large on one server, but not that big a deal on another server. I think if the table cannot fit in RAM then one should treat it as "big data" and start trowing more tools at it, e.g. partition the table. But of course, one may even think about partitioning based on other reasons e.g. the reasons mentioned in the article from Kendra Little.

So actually it is all about the size of the table. Even with sliding windows, you only care about it for large tables, or else one can simply truncate and reload daily.

Extra: of course if you don't have problems with the table then you don't need to partition it. No one is saying if the table is "this big" then you have to partition it. But it is all about the size of the table.

1
  • If you have a large table that never needs index maintenance, and the schema doesn't change, then it might be pointless to partition it just for the fact of it being a large table. AMtwo makes some really valid points, and there's definitely no hard and fast rule here, especially not around one specific hardware component like the amount of Memory on a server.
    – J.D.
    Jun 27, 2021 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.