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.
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.
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?