The boss had a breakdown, a slow SQL query crashed the entire core library

I am a DBA at a company, let me share my personal experience. Not long ago, my boss was furious, and I almost ended up on the layoff list, why? The core library was down! The customer's business system collapsed entirely, and the R&D and after-sales teams worked overtime for 8 hours straight...

The reason was that a new programmer deployed an unindexed SELECT query on a large table to the live environment, causing a backend overload.

Such issues are countless and exist in many enterprises. Due to various unforeseeable reasons, problematic SQL queries cannot be discovered during testing and are only encountered by customers after going live. At this point, a rollback operation has to be executed, which severely affects the continuity of the business. Most importantly, it impacts the user experience and leaves an indelible stain on the company's reputation. Ultimately, the responsibility falls on the DBA team or the development team.

Therefore, as one of the important means of database operations and maintenance, SQL review is particularly crucial in ensuring the stability of online databases. For the core library of a company, to prevent release accidents and standardize SQL statements, in principle, all SQL queries that have not been reviewed should be rejected from being published to the live environment. This usually requires professional DBAs to review each SQL statement that needs to be published.

With the rapid iteration of business and the expansion of scale, manual review by DBAs is becoming increasingly difficult:

Firstly, manual review is inefficient and cannot meet the demands of high-speed iteration. The reality for most companies is that DBAs will overlook SQL queries with lower weight and focus mainly on those with higher weight, leading to low-weight businesses being unsupervised for a long time, posing significant security risks.

Secondly, manual review may have oversights, and once unreviewed SQL is published online, it may cause serious online issues.

Lastly, DBAs need to spend a lot of time and energy dealing with mechanical, repetitive review work, which is too stressful and prevents them from focusing on more complex and advanced database management tasks.

Are there any solutions to these problems?

The answer is yes, we urgently need an automated SQL review method to replace the mechanical review work. After trying out NineData's SQL automated review feature, the company has achieved tracking and governance of SQL quality during the development phase, fundamentally avoiding the aforementioned problems and preventing online accidents.

Haven't heard of NineData SQL Automated Review?

Let me briefly introduce this NineData SQL review tool, which is specifically tailored for enterprises as a SQL release security solution. It targets the phase before the application code is released and supports the review of all SQL statements that need to be published. Based on intelligent algorithms and enterprise-configured SQL development standards, it automatically identifies and prevents problematic SQL. Only after the SQL review passes can the application be released.

NineData SQL Review has the following features:

• Approval Process: Provides a comprehensive SQL approval process, supporting various process configurations such as Intelligent Pre-Review Only (suitable for low-weight businesses), Intelligent Pre-Review + Manual Review (suitable for high-weight businesses), and supports customizing reviewers and review levels.
• Intelligent Pre-Review: Based on intelligent algorithms and enterprise-configured SQL development standards, it automatically identifies and prevents problematic SQL. According to the approval process configured by the administrator, it can intercept problematic SQL before entering the manual review stage, and the process can only proceed after the user has corrected the problematic SQL.
• Optimization Suggestions: During the intelligent pre-review stage, the system provides optimization suggestions for the submitted SQL, including syntax, indexing, naming standards, etc., helping developers optimize the enterprise database and completely eliminate online issues.
• Manual Review: In addition to intelligent pre-review, NineData SQL Review also supports manual review. After the intelligent pre-review has completed the complex mechanical review tasks, the DBA can do the final review to ensure everything is in order.

Through NineData SQL Review, the company has achieved several goals:

• Accelerated Development Iteration: SQL review can quickly review a large number of SQL statements, providing rapid feedback to the development team. It helps the business iterate more quickly in line with market changes.
• Improved Development Quality: Discovering and fixing SQL quality issues during the development phase can reduce the occurrence of online issues. By standardizing SQL statements, ensuring the robustness and efficiency of published SQL, and improving the overall quality of the application.
• Reduced DBA Work Pressure: Handing over mechanical, repetitive review work to SQL review allows DBAs to focus more on handling more complex and advanced database management tasks, increasing work efficiency.

How to Use NineData SQL Review

1. Create an SQL review task.

   

2. View the intelligent pre-review suggestion overview.

   

3. View optimization suggestions for the target SQL, including Standard Review based on SQL development standards, and Index Recommendations based on AI automatic judgment.

   

   tip

   After optimizing the suggestions, you can click on Recheck on the page to see if it has been fully optimized.

   

4. Submit for manual approval.

   

5. After the administrator approves, the process is complete.