9

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.

  • Partitioning tables is something you plan in your database design, prior to actually writing data preferably. It is much more difficult and tedious to do this after the fact. – user507 Jan 2 '15 at 20:49
  • 1
    It depends more on your scenario: is performance fine? Can you archive some of the data? Are tables this big reasonable to efficiently backup/restore? Are they compressed? It would've been good to partition from day one, but the next best day is today if you're concerned about future performance if you want to follow best practices. – LowlyDBA Jan 2 '15 at 20:56
  • 2
    I think with this amount of data you will need to split your database on architectural level, OLTP database and OLAP database, Your application database "OLTP" should only keep the minimum data required for application and business, the rest should be dumped into a data warehouse "OLAP". As far as the question is when you should start partitioning your tables have a look at this article by Kendra Little How To Decide if You Should Use Table Partitioning – M.Ali Jan 2 '15 at 21:06
  • 3
    Performance never tanks just be the fact that a table is big. In fact what's big to many is small to some. Understand what operations are being made faster and which slower by partitioning. Partitioning is not a go faster switch. It's a mostly slower switch and some things become blindingly fast. – usr Jan 2 '15 at 22:55
  • 4
    I highly recommend the MCM training video on partitioning by Kimberly Tripp. – Paul White 9 Jan 4 '15 at 6:33
11

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.

| improve this answer | |
  • Thanks Ben. I am starting to appreciate that there are more variables at play than I first thought. And I accept that, practically speaking, 'try it and see' is a the most sensible approach. But as SQL Server is essentially a program (albeilt a very complicated one) part of me is frustrated at this lack of predictability. – Martin Hansen Lennox Jan 3 '15 at 18:30
  • 1
    @MartinHansenLennox and Ben: I definitely agree with the "try it" approach as opposed to just listening to advice or personal speculation. But, I would recommend stating more explicitly in that paragraph what it means to really try it out. It is more than just loading it and running queries. Testing has to include incrementally adding data to see if / how things change as statistics change and indexes get fragmented, etc. And try backing up, restoring, rebuilding indexes, etc. It should be noted that partitioned indexes, starting in 2012, no longer get a full status update when rebuilding. – Solomon Rutzky Jan 3 '15 at 21:34
  • @MartinHansenLennox: You are right to be frustrated by the "try it and see" approach. SQL Server is very predictable and it is at least in theory possible to analyse the problem before trying it. However, the amount of background knowledge required to do so often makes this difficult. – Thomas Kejser Jan 4 '15 at 2:06
7

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.1

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.

| improve this answer | |
  • 2
    "Partitioning isn't for query performance, it's primarily for data management and administration" - seems obvious when you say it, but I had never quite got it before. Great links btw, thanks – Martin Hansen Lennox Jan 3 '15 at 18:37
  • Thank you for mentioning that this feature is primarily for management and not performance. I rarely see that being mentioned and it is quite frustrating. – Solomon Rutzky Jan 3 '15 at 18:52
  • 1
    @MartinHansenLennox: There are great uses of partitioning for performance too. For example, if you use hash partitioning tricks and for values that have low cardinality. – Thomas Kejser Jan 4 '15 at 9:30
7

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.

| improve this answer | |
  • Thanks Thomas, good advice, particularly appreciate the explanations around partition sizing. – Martin Hansen Lennox Jan 3 '15 at 18:32
7

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".

| improve this answer | |
  • 2
    Great point on the UPDATE STATISTICS. Indexed views work around a lot of partitioning problems if you can handle the optimiser impact. – Thomas Kejser Jan 4 '15 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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