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?