We felt a huge performance improvement by changing this subquery to use "state = 1" instead of "state_desc = 'PAUSED'".
In the test scenario this query return about 130k rows. After applying the change, the time for this query went from 20 minutes to 20 seconds. If there are rows on sys.index_resumable_operations, the performance gain is even bigger.
We felt a huge performance improvement by changing this subquery to use "state = 1" instead of "state_desc = 'PAUSED'". In the test scenario this query return about 130k rows. After applying the change, the time for this query went from 20 minutes to 20 seconds. If there are rows on sys.index_resumable_operations, the performance gain is even bigger.