How to Build a Proactive SQL Defense System: From Early Detection to Capacity Forecasting

1. Proactive SQL Defense System

SQL governance is divided into three stages: development (pre‑stage), testing (mid‑stage), and production operations (post‑stage). Traditionally, SQL review occurs only after issues arise, but moving detection to the testing phase reduces remediation cost and production impact.

1) SQLReview performs basic audits when code moves from development to testing or production. Rules are refined from DBA feedback, third‑party standards, and specific requirements such as mandatory time fields for big‑data extraction.

2) New‑SQL Detection intercepts SQL before it reaches production by recording all test and production SQL via a database proxy middleware, then comparing fingerprints to identify newly introduced statements. Fingerprint analysis checks for full‑table scans, index merges, file sorts, execution time, and prohibited syntax.

Challenges include handling peak QPS of millions per second and improving fingerprint accuracy by using syntax‑tree based algorithms that normalize query objects and WHERE clauses.

2. Deep Observation: Full SQL Analysis and Mining

Full‑SQL analysis enables problem diagnosis, hotspot detection, index evaluation, and data activity monitoring. By aggregating SQL metrics such as response time, row count, and scan rows, teams can pinpoint CPU spikes and unstable clusters.

Use cases include:

Identifying problematic SQL causing high CPU or latency.

Analyzing table and index usage to recommend optimal indexing strategies.

Supporting multi‑cloud compatibility for unified issue tracking.

The analysis pipeline forwards SQL to Kafka, then processes it for sampling, hotspot identification, and trend analysis.

3. Capacity Prediction: Database Simulation Load Testing

Capacity assessment leverages cloud‑native storage‑compute separation to enable rapid scaling. Current evaluation still relies on DBA experience, lacking data‑driven metrics.

Benchmarking is insufficient due to disparity with real workloads. Replay tools like SQLReplay miss detailed dimensions.

Full‑link load testing attempts to emulate complex APP‑ID interactions but can suffer from data distortion and insufficient cache behavior.

Simulated traffic load testing records peak‑period SQL to Kafka, then replays it at low‑traffic periods, allowing controlled scaling experiments. Limitations include difficulty maintaining exact concurrency and ensuring 100% fidelity.

Capacity planning sets a CPU utilization threshold (e.g., 45%). When testing reaches this limit, the corresponding QPS defines the system’s capacity ceiling, guiding scaling decisions.

Future work aims to automate index recommendations across the entire schema and enhance the DAS platform toward a mature product.