I'm designing the partitioning for our new database, but we are facing performance issues in inserting or updating the partitioned table.

For simplicity suppose we have one table T, which have an identity column as primary key, another column date which is used for partitioning and then some data columns. We expect several millions of rows inserted each day, all queries into this table have WHERE date = xxx condition and also we would need to be able to delete all rows for given day in some reasonable time (partition switching would be best solution).

With this requirements we decided to partition the table by days. As a first step we generated partition function with all the days from 2014-01-01 to 2025-12-31, created the partitioning schema and partitioned the clustered index on column date by this schema. This generated about 4000 partitions where most of them are empty for now. The non-clustered index remains unpartitioned (but when it is also partitioned nothing changes).

But from that time, we are facing serious performance issues. By profiling we found that the problem is in inserts and updates. Fast inserts are quite crucial for us. We are using bulk inserts into temporary table and than insert into the data table using INSERT INTO T SELECT FROM #tmpTable. In the log I found for example this query:

INSERT INTO T ([date],[assign_type],[source_table],[source_column],[source_id],[target_table],[target_column],[target_id],[comment],id)
SELECT [date],[assign_type],[source_table],[source_column],[source_id],[target_table],[target_column],[target_id],[comment],id FROM ##9cf8bd79_c3d5_4f18_af54_fd96b671d6a7

When partitioning is not used the statistics for this query are following:
CPU: 15, Reads: 7520, Writes: 36, Duration: 27

With partitioning scheme described above statistics are:
CPU: 499, Reads: 2792078, Writes: 16, Duration: 511

All the data in temporary table are with same date, so all of them should be inserted into the same partition. But based on the statistics it seems that for some reason the SQL server spends a lot of time to find correct partition.

So we tried to reduce the number of partitions and we created just partitions for 2 years (700 partitions instead of 4000). The results are better:
CPU: 109, Reads: 472412, Writes: 48, Duration: 109
But it is still unusable, this query was inserting just few hundreds of rows, we need to insert tens/hundreds of thousands rows.

The situation is similar in bulk updating, where we are updating just the data columns, not the date value, so the rows do not have to be moved between partitions and for finding the id can be used non-clustered non-partitioned index. Also all the updated rows are from same partition. The query is:

UPDATE T SET processing_status = tmp.processing_status,processing_datetime = tmp.processing_datetime,processing_message = tmp.processing_message FROM
(SELECT processing_status,processing_datetime,processing_message,record_id FROM ##e5c95cc6_a6db_4b45_b7fe_d4c929b0caed) tmp WHERE tmp.id = T.id

And the statistics are following:
Without partitioning: CPU: 0, Reads: 1964, Writes: 0, Duration: 7
With 700 partitions: CPU: 31, Reads: 1964, Writes: 0, Duration: 29
With 4000 partitions: CPU: 982, Reads: 1964, Writes: 0, Duration: 985

The difference is that the number of reads is the same, the UPDATE is just CPU-bound.

I'm quite new to partitioning, so I'm not sure if it is a good idea to use so many partitions, I didn't find any articles where authors use so many partitions, in most of them they are using usually just several partitions (e.g. partitioning by year or month).

Then I would like to know where is the overhead in inserts and updates - I think that it is inefficient implementation on MSSQL side, I suppose that they are searching for correct partition for each row again and again - and if I can to improve it, e.g. with providing some hint, rewriting the query or using another workaround.

I will be very grateful for any advice or hint, please let me know if you need to provide any other details.

UPDATE: Here are the execution plans for inserts:

Without partitioning:
Insert into non-partitioned table - queryplan
Statistics for clustered index insert are on following picture:
Insert into non-partitioned table - clustered index statistics

With partitioning:
Insert into partitioned table - queryplan
Statistics for clustered index insert are on following picture:
Insert into partitioned table - clustered index statistics

For the updates, the query plans are following:

Without partitioning:
Update of non-partitioned table

WIth partitioning:
Update of partitioned table

  • I updated the original post and added the query plans, now I understand why there is such a difference. So the question now is how to avoid using clustered partitioned index in insert. Aaron: We have all partitions in the same filegroup. – Lukas.Navratil Oct 8 '15 at 7:52
  • I created a test script and found that when inserts/updates targeted only a single partition, the number of reads and overall performance was actually better for the partitioned table. Full script here. So I'm not sure what's going on with your case and why mine is so different. If you can post a full test script that creates your schema, inserts fake data, and re-produces the problem, I'm pretty confident we'll be able to help further. – Geoff Patterson Oct 8 '15 at 19:45
  • Which SQL version are you using? I think that there are some pretty major differences in partitioned table implementations between 2005 and 2008+. – Geoff Patterson Oct 8 '15 at 19:46
  • I'm using MSSQL 2014. I will prepare a script to reproduce the problem. If I compare it with yours, it is similar, but there are some differences. I'm using partitioning over datetime column instead of int and the primary key contains just one column which uses identity - so it is not partitioned and not aligned with partitioned data. Maybe this can be the problem. I tried also to include partitioned column into the primary key and partition it - the results were much better, but still about twice slower than non-partitioned table. But we would like to avoid using composite primary key... – Lukas.Navratil Oct 8 '15 at 21:32
  • If you are using an index that is not partition-aligned, what is the primary goal you are trying to achieve by using partitioning? The non partition-aligned index is going to prevent loading data by partition switching, which is often the primary reason to use partitioned tables. It seems like you may not want to use partitioned tables at all if you do not plan to have your indexes be partition-aligned. – Geoff Patterson Oct 12 '15 at 17:43

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.