Do partitions allow multiple bulk loads?

Question

I have a database that contains data for many "clients". Currently, we insert tens of thousands of rows into multiple tables every so often using .Net SqlBulkCopy which causes the entire tables to be locked and inaccessible for the duration of the transaction.

As most of our business processes rely upon accessing data for only one client at a time, we would like to be able to load data for one client, while updating data for another client.

To make things more fun, all PKs, FKs and clustered indexes are on GUID columns (I am looking at changing this).

I'm looking at adding the ClientID into all tables, then partitioning on this. Would this give me the functionality I require?

Answer 1

I haven't used the built-in partitioning functionality of SQL Server, but it's something I am particularly interested in. My understanding is that this would solve your problem.

From this article

This allows you to operate on a partition even with performace critical operation, such as reindexing, without affecting the others.

And a great whitepaper on partitioning by Kimberly L Tripp is here. Well worth a read - I won't even try to paraphrase it - covers it all in a lot of detail.

Hope this helps.

Answer 2

Can you partition on Client ID : Yes, but partitioning is limited to 1000 partitions so that is 1000 clients before it hits a hard limit. The only way to get around that is to start using partitioned views across multiple partitioned tables - it gets a bit messy.

Will is help your locking situation : In SQL 2005 the lock escalation is row -> page -> table, but in 2008 they introduced a new level allowing row -> page -> partition -> table. So it might get round it, depending on your SQL version (unspecified).

If 2008 is not an option, then there is a trace flag (TF 1211 / 1224) feature that turns off lock escalations, but I would not jump in and use it without some serious testing.

The partitioning feature remains an enterprise upwards feature as well which puts some people off.

The most ideal way in which to perform a data load with partitioning, but avoiding locks is to bring the data into a staging table and then swap the data into a new partition - but this requires that the data is somewhat sequence based (such as datetime) so that new data can be brought in to an entirely new partition whilst older data eventually is removed. (rolling the partition window.)