Partitioning big tables

Question

I have a DB with multiple large tables. For example one single table has 738,829,922 rows.

The primary key of the table is a datetime. For better performance and maintainability the table is partitioned by monthly range.

Here is an example of the partition stats:

Around 95% of all query are hitting only the partition of the current month (also only the last 3 days).

Over the month I notice a performance drop the seems to be related to the row count of the havely use partition. It instancly goes back to normal after switching to a new partition.

I worked hard with the microsoft support to optimize all to the maximum. Thinks like statistics, fragmentation, parameter sniffing, query plans are well handled and leave not much room for optimization.

Question

I hope smaller partitions (weekly or daily) might solve the problem. To try this I have a gerneral questions abount partitions.

Are there some negative effects of having around 3,000 partitions on a single table? Are there other tips/tricks about partitioning table of this size?

Answer 1

General answer is that you could remove the paritioning and the performance would not drop. Hitting only one partition is quite similar in performance compared to filtering out 95% of the row based on a proper index. Partitiions generally are not for increasing select performance, but for allowing super fast DROPPING of partitions (when the data falls out of scope).

So, you really need to do the basics - get the slow queries, analyze them, find out how to write them better and / or whether other queries are blocking them. Unless you have really special cases (which we can not evaluate given the information you give)... the partition is totally not helping you in the selects.