Query Batch Compilation Locking to Prevent SQL Compile Storms

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

In SQL systems, parallel processing of identical ad hoc query batches leads to 'compile storms' during failover, causing CPU usage spikes and resource contention due to redundant plan compilation.

Innovation Solution

Implement a mechanism to compile ad hoc query batches sequentially, using a lock to ensure that only one batch processes at a time, allowing cached plans to be reused for subsequent batches with identical query text.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If ad hoc query batches are processed in parallel, then processing throughput is improved, but CPU usage spikes and resource contention occurs due to redundant compilation

Engineering Contradiction:

Improvequery batch processing throughputVSAvoidCPU usage

Core Design Contradiction:

ProductivityVSLoss of energy

Solution Approach 1:

The system performs a cache lookup before compilation to check if a plan already exists. This preliminary check prevents redundant compilation by identifying queries that can reuse existing plans, thereby reducing CPU usage while maintaining parallel processing throughput.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system creates and stores compiled query plans in a cache for future use. When identical or semantically equivalent queries are encountered, the cached plan is copied and reused instead of recompiling, eliminating redundant CPU work while allowing parallel query batch processing to continue.

Inventive Principle:

Principle #26Copying

2Loss of energy

If plan caching is implemented, then recompilation cost is eliminated for frequently executed queries, but compile storms occur during failover when plan cache is empty

Engineering Contradiction:

Improverecompilation costVSAvoidCPU usage during failover

Core Design Contradiction:

Loss of energyVSProductivity

Solution Approach 1:

The system compares the semantic meaning of incoming queries against cached plans using semantic equivalence detection. This feedback mechanism identifies queries that match cached plans even if their text differs, allowing the system to reuse existing plans and avoid compilation storms during failover while maintaining energy efficiency.

Inventive Principle:

Principle #23Feedback

3Productivity

If semantic equivalence detection is implemented, then query plan reuse is improved for semantically identical queries, but system complexity increases

Engineering Contradiction:

Improvequery plan reuse efficiencyVSAvoidsystem complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The system transforms query text into semantic parameters or representations that capture the meaning rather than the exact wording. By changing the parameter space from raw text to semantic features, the system can efficiently detect equivalent queries using simpler comparisons, improving plan reuse efficiency while managing system complexity through parameter transformation.

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS12511288B1Ad hoc query batch compilation serialization

Publication Date: 2025.12.30 MICROSOFT TECHNOLOGY LICENSING LLC

AI summary

When two ad hoc query batches with the same query text are to be compiled, one locking key is created for both ad hoc query batches. The locking key is allocated to the first ad hoc query batch. In a subsequent cache lookup stage, if no cache is found for the first ad hoc query batch, a plan is compiled for the first ad hoc query batch, and inserted into a cache. The locking key is released and allocated to the second ad hoc query batch. If the locking key for the second ad hoc query batch is the same as for the first ad hoc query batch, the compiled plan is identified in the cache. Plan compilation is bypassed for the second ad hoc query batch.