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:
For the updates, the query plans are following: