A client wants our application to process more data faster so arranged a meeting with their dba to discuss options.

This application generates quite a lot of data that is used for reporting. Before each run the old data for that item is deleted, the calculations are performed and then the new data inserted. In busy periods the users queue up hundreds of these generation tasks and we run upto 30 of them concurrently. Each run might create 60K rows.

The dba has suggested we could change the application to use 30 partitions (eg. one per thread) to reduce locking between threads during insert and delete. They suggested that in standard sql we could do something like

INSERT INTO schema.table.partition (...) VALUES (...)

I do not see this syntax in the msdn docs and this will mean changing this application which is a pain but is it even possible to do this? As I understand we would instead partition based on columns of the tables using partition functions?

I've read the create partition function docs but am not completely sure how to create a function to meet our needs. To make matters worse I don't yet have enterprise edition to try this out on so my apologies for incorrect syntax.

I am thinking that for example if we have an items table and an itemdata table with data for that item we might partition itemdata table by splitting the data based on a function like itemid mod 30. This would put item 1 in partition 1, item 2 in partition 2, etc. I'm not sure if we could do this in the partition function, in the scheme, table declaration or would we need to create a calculated column and use a values clause? Also not sure if we are going to see any performance improvement?

This is how I think we could implement this:

CREATE PARTITION FUNCTION SplittingItemIds_PFunc(decimal(18,0)) AS
(0,1,2,3, ... ,29)

AS PARTITION SplittingItemIds_PFunc

    Id decimal(18,0),
    ItemId decimal(18,0),
ON PartitionSplittingItemIds_Scheme(ItemId % 30)

CREATE INDEX ItemData_ItemId_Idx ON ItemData(ItemId);

Is this kindof right? From what I've read the index will be automatically partitioned - is that correct?

  • 2
    Quick FYI - you can use Developer Edition (rather than Enterprise) for your test/dev purposes. Its functionally equivalent to Enterprise. Sep 1, 2011 at 11:30
  • On the main question, can you add some more detail on the problem you are hoping to solve. What is the nature of the contention you're seeing? Is it locking/blocking/deadlocking? Whats the IO configuration? Is there a raw IO throughput issue? Sep 1, 2011 at 11:41
  • It is really a raw IO throughput issue - we want to improve the speed of many concurrent threads that insert/delete into the same tables. It is not related to my previous question regarding deadlocks. As we run many of these tasks simultaneously there is locking between the tasks. So we suspect that reducing locking will improve throughput. We had occasionally seen deadlocks on this part of the app but this has been reduced through smaller transaction sizes, rowlock hints and removal of foreign keys on specific tables. Perhaps partitioning would have been a better solution? Sep 1, 2011 at 12:03
  • As for the I/O configuration - I don't know, this server is managed by a third party and I am only able to implement a software soluton Sep 1, 2011 at 12:06

2 Answers 2


It sounds like the dba is talking about horizontal partitioning rather than table partitioning, by breaking the troublesome tables using rules, such as all customers that start with the letter a go in tableA, b in tableB, etc. This can be helpful in some circumstances, and can be done with any edition of SQL server, but has many of the same issues already mentioned, i.e. I/O.


Software can't fix this if the underlying IO/disks are opaque to you.

  • If you add partitions, you won't spawn 30 threads
  • If all your partitions are on the same volume you'll decrease throughput

I've worked on similar systems where we had

  • a staging DB
  • SIMPLE recovery on staging
  • deferred deletes (eg a new or updated run is an INSERT) that run out of hours
  • each run has a header row to track this status
  • flush to real DB when client is ready

We've also

  • removed FKs and other tweaks to the main table
  • put the staging DB onto separate volumes.
  • Eaxctly where I was going with this. Understand the underlying problem first, then evaluate the possible solutions. Sep 1, 2011 at 15:14
  • The threads spawned are on the application server - we have found running these in parallel helps overall throughput however it seems to slow up when many are inserting/deleting at the same time. Apparently the clients dba will be looking into the IO/disk configuration. They have suggested we change the application to support partitioning. I believe an application change is unnecessary for partitioning support and this is my immediate problem. Sep 1, 2011 at 21:38
  • @gbn - so if the partitions are all on the same volume there is a decrease of performance? Is that because the db engine will syncronously try to hit multiple partitions, and if they are on the same partition it'll cause bottlenecking? So basically we should be putting our partitions/filegroups on different partitions? Could we even go as far to say they should be on different physical drives? Sep 1, 2011 at 23:22
  • 1
    @Surfer513 - Yes, yes, yes, and yes. One benefit you can still get with having multiple partitions on the same drive is partition elimination in your queries. Oct 13, 2011 at 6:13
  • @Nick: Good read that article. Just goes to show ORs are bad :)
    – gbn
    Oct 13, 2011 at 7:18

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.