SQL Azure "hot" table query slowdown over time
The problem might be cardinality estimation, index fragmentation and stale statistics causing performance issues that increase over time. If you're not rebuilding your indexes and updating your statistics, this can be affecting the optimiser's plan choices and causing performance degradation over time. It might also explain why TRUNCATE seems to correct the issue (see Kendra Little's article, basically the optimiser tracks TRUNCATE to ensure it makes sensible decisions on empty tables even without statistics updates).

You need to do a couple of things to track down the root cause:


Enable Query Store to identify regressions in query performance over time. If a new plan is being selected due to cardinality issues or stale statistics, Query Store will highlight them.
Implement regular index and statistics maintenance tasks if you haven't already. If you have stats or index frag issues, this should help alleviate them.
Track your wait statistics over the one-two week period to see if a particular wait is increasing over time causing the degradation.
Next time it occurs, don't truncate to fix - you need to collect as much data as you can to try and determine the root cause. Get the statistics details for the table, get the index frag details for the table, get the current execution plan details, collect BlitzCache details, these will all help you identify the root cause.