Building a Sustainable Slow‑SQL Governance Process: Phased Metrics and Practices

Governance Background

Database performance degrades when slow SQL queries accumulate, leading to higher latency, request back‑log, and potential system crashes. Traditional governance only reacts during high‑traffic events, leaving daily slow‑SQL monitoring insufficient. A continuous slow‑SQL governance project was therefore launched to maintain stability and prevent emergency incidents.

Phase Planning

1.0 Phase – Establish Routine Governance

Goal : Build a daily‑tracking mechanism for slow SQL and evaluate bi‑weekly effectiveness.

Key Metrics :

Overdue Rate = (Number of tickets overdue >14 days created this quarter) ÷ (Total tickets created this quarter)

Reopen Rate = (Number of ticket reopenings created this quarter) ÷ (Total tickets created this quarter)

2.0 Phase – Eliminate Historical Debt

Goal : Clear all queries whose execution time exceeds 0.9 s and achieve a weekly zero‑count for that threshold.

Key Metrics :

P0 Ticket Count = Number of tickets pushed in the week for queries > 0.9 s

Ticket Priority Definitions

P0 – Execution > 0.9 s and the threshold reached ≥10 times

P1 – Execution > 0.9 s but threshold <10 times

P2 – Execution < 0.9 s without index, threshold ≥10 times

P3 – Execution < 0.9 s without index, threshold <10 times

P0 Backlog = Weekly P0 tickets that remain unresolved

Resolution Rate = Resolved P0 tickets ÷ Total weekly tickets

3.0 Phase – Gradually Lower the Slow‑SQL Threshold

Goal : Improve overall system performance by tightening the slow‑SQL definition in steps.

Key Metrics (example thresholds):

P0 – Execution > 0.9 s

P1 – 0.7 s ≤ Execution ≤ 0.9 s

P2 – 0.5 s ≤ Execution < 0.7 s

P3 – Execution ≤ 0.5 s without index

For each level, track weekly push count, backlog count, and resolution rate.

4.0 Phase – Prevent New Slow SQL

Goal : Enforce database operation standards to stop the creation of new slow queries.

Key Metrics :

New Ticket Count = Weekly tickets with previously unseen fingerprint IDs

Backlog Count = Weekly unresolved tickets

Resolution Rate = Resolved tickets ÷ Total weekly tickets

Implementation Plan

1. Data Preparation

Define two categories: (a) execution time > 0.9 s, and (b) execution time < 0.9 s but without an index. Collect daily SQL execution logs for the previous day (T‑1). Exclude samples from known low‑impact services (e.g., bi_cx, wlcx) and aggregate identical fingerprinted queries into a single risk record. For each record, capture diagnostic details such as database IP, schema, full execution plan, and duration.

2. Ticket Advancement

Assign tickets to owners based on business line. Within each line, a designated interface owner distributes tickets to team members and tracks progress. Resolution follows DBA‑recommended patterns and internal best‑practice templates to accelerate remediation.

3. Trend Analysis

Generate weekly dashboards that display:

Slow‑SQL count per instance

Resolution trend over time

Backlog distribution across priority levels

These visualizations support transparent monitoring and are reviewed in a weekly sync meeting.

4. Process Tracking

Monitor the effectiveness of each phase:

1.0 – Focus on ticket‑resolution efficiency.

2.0 – Measure reduction of > 0.9 s debt.

3.0 – Track adoption of tighter thresholds.

4.0 – Verify that new slow‑SQL creation stays at zero.

Results

System Assurance

Before reaching the 529‑ticket milestone, the team resolved 831 slow‑SQL entries, cleared all historical debt during the 618 promotion period, and maintained daily index addition and code optimization. No incidents caused by slow SQL occurred in the subsequent six months.

Solution Consolidation

The governance practice was codified into the "JD Logistics Database Development Specification V1.0" and a reusable solution guide covering SQL analysis, rapid定位, and efficient remediation.

Summary

The phased, metric‑driven slow‑SQL governance transformed ad‑hoc emergency fixes into a sustainable, data‑backed process. It delivered measurable performance improvements, clear accountability, and a lasting set of standards that protect system stability.