Partition strategy

Question

Starting point:
I fill one table with data and in order to compare the data from run to run, I added a column "run" (Integer).
I keep the last 5 runs. As I keep getting timeouts after 2 runs are in the table, I decided to partition the table on "run".

What I did:
1. I created the partitioned table with 1000 partitions (I suppose "run" to be from 1 to 1000), maximally only 5 partitions are there, the other 995 are empty and waiting until the moment when the next run gonna be put there.
2. I created partition scheme that uses the partition function described above and keeps all partitions (i.e. 1000) in PRIMARY file group.
3. I partitioned the table on "run".

Result: no timeout anymore, but the processing time increased by 50%.

Question: which points were suboptimal in my partitioning strategy? Should I have created 5 partitions and changed the way I number the runs?

Answer 1

Partitioning is not a solution for performance issues. You are looking for indexes. Partitioning will slow down processing and the best you can hope for is on-par performance with non-partitioned tables. Partitioning is a good fit for data management scenarios, for fast data move using partition switch (ETL or data expiration).

You need to investigate why are you getting time outs. An educated guess would indicate table scans (ie. missing indexes). A good methodology is Waits and Queues.