Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
RANGE LEFT FOR VALUES
(0,1,2,3, ... ,29)

CREATE PARTITION SCHEME SplittingItemIds_Scheme 
AS PARTITION SplittingItemIds_PFunc
ALL TO ([PRIMARY]);

CREATE TABLE ItemData  
(
    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?

share|improve this question
2  
Quick FYI - you can use Developer Edition (rather than Enterprise) for your test/dev purposes. Its functionally equivalent to Enterprise. –  Mark Storey-Smith Sep 1 '11 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? –  Mark Storey-Smith Sep 1 '11 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? –  Adam Butler Sep 1 '11 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 –  Adam Butler Sep 1 '11 at 12:06

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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.
share|improve this answer
    
Eaxctly where I was going with this. Understand the underlying problem first, then evaluate the possible solutions. –  Mark Storey-Smith Sep 1 '11 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. –  Adam Butler Sep 1 '11 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? –  Thomas Stringer Sep 1 '11 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. –  Nick Chammas Oct 13 '11 at 6:13
    
@Nick: Good read that article. Just goes to show ORs are bad :) –  gbn Oct 13 '11 at 7:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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