Lessons Learned #541:Automatic Plan Correction vs External Tables: A Practical Lesson from the Field
Azure SQL Database's Automatic Plan Correction is a valuable feature for addressing query plan regressions. It leverages Query Store to detect when a query's execution plan degrades and can automatically revert to a previously good plan. However, queries involving external tables have unique execution characteristics due to their reliance on remote data access. These queries might not always be suitable candidates for Automatic Plan Correction because their performance is influenced by factors beyond the local database. Therefore, it's important to identify queries that reference external tables and assess if they should participate in the FORCE_LAST_GOOD_PLAN feature. Reviewing the execution plan of an external table query reveals a Remote Query operator, indicating its dependency on remote resources. The performance of such queries is affected by the remote database, network latency, and data retrieval size, not just the local plan. Consequently, potential benefits of automatic plan forcing for these queries need careful evaluation. The process involves finding the specific Query Store query ID associated with external table queries. Once identified, that query ID can be excluded from Automatic Plan Correction. This is achieved by disabling FORCE_LAST_GOOD_PLAN for that particular query. This targeted exclusion allows Automatic Plan Correction to remain active for other queries while ensuring that external table queries are managed individually.