Rule of thumb when to partition tables

Question

I've seen many entries about partitioning tables, but there is not a lot of information on when you should make a partition.

Is there a rule of thumb when you should partition tables in SQL Server.

Thanks

Answer 1

My benchmarks indicate that it depends on the query load.

• If the queries you perform ALWAYS contain a filter on the partition field the performance benefit is virtually instant (like 1000 records in the table is already beneficial)
• If the queries do NOT always contain a filter on the partition field you really have to benchmark with a good sample of the query load before making the decision.

You also have to account for the partition system you use. if you use "static" partitions there is not much harm in creating them immediately. When you use a "sliding window" system you need to take into account the overhead of creating and merging partitions. (which can take a long time on big tables)