Given the append nature of these two tables they incrementally get larger in size with each publishing attempt. Most of the queries are hitting indexes, but if any query falls back to a table scan, then things can slow down. We're approaching ~100k rows in each table.
Make sure the fillfactor on these tables is set to something appropriate (like 70%)
Setup a job to prune old publishing queue items and publishing states
Set up time-frames for what needs to be kept versus what is prunable -- something like a few dozen publishing attempts?
Given the append nature of these two tables they incrementally get larger in size with each publishing attempt. Most of the queries are hitting indexes, but if any query falls back to a table scan, then things can slow down. We're approaching ~100k rows in each table.