How to use Query Rewriter Plugin to tune SQL in MySQL database I

The Query Rewriter Plugin in MySQL is a component that allows you to modify incoming SQL queries before execution. It provides the ability to transform, route, filter, or expand queries based on specific requirements. This plugin operates at the SQL layer and can be utilized for optimizing query performance, enforcing security policies, implementing data partitioning strategies, or adding additional business logic to queries. With the Query Rewriter Plugin, you have the power to customize and shape SQL queries to meet your specific needs, providing flexibility and control over query execution within the MySQL server.

The Query Transformation feature enables you to rewrite or transform the original query into an equivalent or more efficient form. This can be useful for optimizing performance, simplifying complex queries, or enforcing certain query plans.

You must install Query Rewriter Plugin before using this feature, the concept of Query Rewriter is simple, it is a set of predefined SQL statements that is used to replace a certain pattern of SQL statements that is fired from your application programs.

If you have installed the plugin, the following SQL statements can be used to defined your SQL replacement rules and error message handling.

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

The query_rewrite.rewrite_rules table in MySQL stores the rules used by the Query Rewriter Plugin to rewrite SQL queries. The table has two columns:

Pattern – This column represents the pattern or condition that triggers the rewriting of a SQL query. It defines the specific query or query pattern to match.

Replacement – This column specifies the replacement or transformation that should be applied to the matched query or query pattern.

When a SQL query is executed, the Query Rewriter Plugin checks the query_rewrite.rewrite_rules table for matching patterns. If a pattern matches the executed query, the plugin rewrites the query using the corresponding replacement. This allows you to modify the query structure, optimize it, or add custom logic based on specific patterns or conditions.

I utilize the message column to define a temporary unique id for the SQL replacement rule, so the actual rule id can be extracted with the following SQL.

SELECT id into :SID FROM query_rewrite.rewrite_rules where message=Unique_ID;

When you make changes to the query rewrite rules in the query_rewrite.rewrite_rules table, those changes are not immediately applied. Instead, MySQL caches the rules in memory for better performance. However, if you want to ensure that the updated rules take effect immediately, you can call the query_rewrite.flush_rewrite_rules() function.

CALL query_rewrite.flush_rewrite_rules();

If a load error occurs, the plugin also sets the Rewriter_reload_error status variable to ON and the error message will be stored in the Message column.

SELECT message FROM query_rewrite.rewrite_rules where id=:SID;

Actually, the Query Rewriter Plugin is powerful and easy to use. The most challenging aspect is finding a replacement SQL for your poorly performing SQL statement. Tosska DB Ace Enterprise for MySQL can assist you in automating this process, from identifying poorly performing SQL statements to rewriting SQL syntax and deploying replacement rules.

Tosska DB Ace Enterprise for MySQL – Tosska Technologies Limited

DBAM Tune Rewriter demo – YouTube

Tosska DB Ace Enterprise (DBAS™) for SQL Server® – System Requirements

Before installing Tosska DB Ace Enterprise (DBAS™) for SQL Server®, please make sure your system meets the following minimum hardware and software requirements:

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 500 MB of disk space for 64-bit installation
Operating System Microsoft Windows® 7 64-bit
Microsoft Windows® 10 64-bit
.NET Framework Microsoft .NET Framework 4.5.0
Database Server SQL Server database 2005 or higher

Tosska DB Ace Enterprise (DBAM™) for MySQL® – System Requirements

Before installing Tosska DB Ace Enterprise (DBAM™) for MySQL®, please make sure your system meets the following minimum hardware and software requirements:

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 400 MB of disk space for 64-bit installation
Operating System Microsoft Windows® 7 64-bit
Microsoft Windows® 10 64-bit
.NET Framework Microsoft .NET Framework 4.5
Database Server MySQL 5.6 or higher version

How to tune SQL in a Database Object with Plan Guide for SQL Server ?

Leveraging Plan Guides in MS SQL Server can prove beneficial for optimizing the performance of a particular query within database objects like stored procedures and trigger applications. This technique allows you to enhance query efficiency without requiring modifications to the application’s source code.

Here are the steps to use Plan Guides to tune third-party applications SQL in MS SQL Server without changing the source code:

  1. Identify the SQL that are causing performance issues in the database object.
  2. Create a Plan Guide that offers an optimized execution plan for the specified query by incorporating query hints to influence the decision-making process of the optimizer.
  3. Test the Plan Guide to ensure that it provides the desired performance improvements and does not cause any unintended side effects.
  4. Deploy the Plan Guide to production and monitor the performance of the application to ensure that the Plan Guide is being used and is providing the desired performance improvements.

Before optimizing an database object’s SQL statement executed by an application program without modifying the source code, it is crucial to understand how the SQL statement matches the one specified in the Plan Guide, which includes whitespace and comments. Additionally, it is important to match the database object that execute the SQL statement.
Below is an example that demonstrates how to optimize a SQL statement from a database object called getRD2. The SQL statement is selected and highlighted by the user.

Creating a plan guide may initially seem complex, but it is a valuable method for improving SQL performance without modifying the source code or lacking the necessary permissions. The most time-consuming part involves finding the best query hint for the SQL statement using the @hints = N’OPTION(query_hint [ ,…n ]) parameter in the sp_create_plan_guide system procedure. If you don’t have extensive knowledge of SQL tuning techniques or enough time for experimentation, a solution is available that simplifies the process. It captures SQL, identifies the SQL source type, automatically optimizes query hints, and facilitates easy deployment of plan guides. The following product automatically identifies a Plan Guide, as depicted in the accompanying screenshot, which can enhance SQL performance by 75.81%.

After identifying the optimal Plan Guide, we can proceed to deploy it along with the stored procedure to the SQL Server database. This deployment will result in improved performance for the stored procedure named getRD2, all without requiring any modifications to the source code of the stored procedure.

You also have the option to manually create the Plan Guide using the system procedure sp_create_plan_guide once you have obtained a suitable hint solution for the SQL statement.

For detailed information, kindly visit our website and take a look at our demo video.

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Object – YouTube

How to tune Ad-hoc SQL with Plan Guide for SQL Server ?

Using Plan Guides to tune third-party applications SQL in MS SQL Server can be a useful technique when you need to optimize the performance of a specific query or set of queries generated by the application, without making changes to the application’s source code.

Here are the steps to use Plan Guides to tune third-party applications SQL in MS SQL Server without changing the source code:

  1. Identify the query or queries that are causing performance issues in the application. You can use SQL Server Profiler or Extended Events to capture and analyze the SQL statements generated by the application.
  2. Create a Plan Guide that provides an optimized execution plan for the identified query or queries. This can involve modifying the query text or providing query hints to influence the optimizer’s decisions.
  3. Test the Plan Guide to ensure that it provides the desired performance improvements and does not cause any unintended side effects.
  4. Deploy the Plan Guide to production and monitor the performance of the application to ensure that the Plan Guide is being used and is providing the desired performance improvements.

Before optimizing an ad-hoc SQL statement executed by an application program without modifying the source code, it is crucial to understand how the SQL statement matches the one specified in the Plan Guide, which includes whitespace and comments. Additionally, it is important to match the source of the SQL statement. The following is the system procedure used to create a Plan Guide.

Today, the focus will be on tuning ad-hoc SQL (@type = N’SQL’) using a Plan Guide. There are two types of SQL: standalone SQL (@module_or_batch = NULL) and SQL within a batch text (@module_or_batch = N’batch_text’). For instance, if an application program sends the following SQL, and it is executed independently without any other code, it falls under standalone SQL.
select top 10 * from  employee;
The example below illustrates a batch text that contains one of the SQL statements listed above, which needs optimization by Plan Guide. This SQL statement is located in the middle of the batch text. Since the same SQL statement can originate from a batch text, we must specify the specific batch text by using the variable @module_or_batch = N’batch_text’. Consequently, two Plan Guides must be created for the same SQL statement, one for ad-hoc SQL and one for batch text. To accurately identify the source of an Ad-hoc SQL, it is recommended to use SQL Profiler to capture the SQL statement that requires optimization by Plan Guide.

select count(*) from employee;
select top 10 * from  employee;
where emp_id in (select emp_id id
                             from emp_subsidiary
                             where emp_dept<‘h’)

order by emp_name;

Microsoft SQL Server Management Studio provides a useful tool that assists users in creating a plan guide for a SQL statement without requiring manual execution of the system stored procedure. However, it is crucial to have knowledge about the type of SQL statement being optimized and the meanings of the corresponding parameters that need to be input.

Although the steps to create plan guide may seem complicated for newcomers, they are worthwhile for improving SQL performance without altering the source code or lacking the permission to modify it. However, the most challenging and time-consuming aspect is finding the optimal query hint for the SQL statement (@hints = N’OPTION(query_hint [ ,…n ])). Unless you have an in-depth knowledge of SQL tuning techniques and enough time to experiment, you may require a product that streamlines the process from capturing SQL, identifying SQL source type, automatically tuning query hints, and facilitates easy deployment of Plan Guides.

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Standalone – YouTube
DBAS Tune SQL PG Batch – YouTube

The Overlooked Significance of Performance Deployment in Software Deployment

Performance deployment is a critical aspect of software deployment that is often undervalued. While it’s important to ensure that software is deployed correctly, it’s equally important to optimize its performance in the production environment. By recognizing the significance of performance deployment during the software deployment process, developers can ensure that their software performs well and meets the needs of its users. Focusing on performance deployment can help prevent performance issues and improve user satisfaction with the software.

The Missing Link of Performance Deployment Between Testing Database and Production Database
Despite extensive pre-deployment testing, there is still a chance of encountering performance problems in specific development environments during software performance deployment. The following issues may arise :

  1. Inability to copy production data to the testing database.
  2. Significant differences in hardware and software configuration between the testing and production databases.
  3. Inability to test software in the production database due to security restrictions.
  4. The utilization of DML SQL statements in the new software that may damage the data integrity of the production database.

It is not unusual for users to face performance issues or encounter application errors following a release of new application code.

Ensuring Performance Deployment with a Pre-Deployment Process
The subsequent instructions present a novel approach for guaranteeing performance reliability while deploying software. The idea is uncomplicated: since it is not feasible to run the new application code on the production database, why not obtain query plans for every SQL statement in the production database? This way, we can assess the performance of each SQL statement in the application code that is intended for deployment on the production database.

Suppose there are 10 SQL statements in the new application code that we need to identify in the testing database. In that case, we need to clear the shared pool and execute the new application in the testing database first to isolate these 10 statements. This process will enable us to capture and analyze the 10 SQL statements and obtain their query plan from the production database. The table below presents various potential outcomes resulting from the query plan comparison.

Observation Possible reasons
Explain Plan error in the production database The SQL statements requiring access to objects not present in the production database.
Query plan changes Significant statistical differences between the testing and production databases, including differences in the database schema. These schema differences may involve missing or new partitions and other changes affecting the database’s structure and organization. Benchmarking the SQL may be necessary due to the potential significant changes in performance.
Unused indexes Some indexes used in the testing database are not used in the production database. Benchmarking the SQL may be necessary due to the potential significant changes in performance.
New used indexes Some indexes used in the production database are not used in the testing database. Benchmarking the SQL may be necessary due to the potential significant changes in performance.
Total cost changes Changes in the overall query plan cost for the 10 SQL statements. If the production database has a larger data volume than the testing database, the cost change will be higher.

DBAO SQL Performance Tracker – YouTube
Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

How to analyze the performance impact of a database environment change using what-if analysis ?

To properly evaluate the impact of a set of SQL statements during a database environment change, it’s crucial to have a thorough understanding of how SQL query performance can be impacted. There are two primary types of performance changes that can occur in SQL queries. The first type, which I refer to as “progressive change,” is typically caused by changes in statistics, such as fluctuations in data volume in relevant tables or index pages. If the statistics change is not significant enough to trigger a new query plan, the query plan will remain the same, and the performance of the SQL query will not be significantly altered compared to the original statistics.

The second type of performance change, which I refer to as “quantum change” occurs when a new query plan is introduced due to a significant change in statistics or schema. This type of change can have a major impact on performance, occasionally resulting in performance disasters.

When making changes to the database environment, it is essential to closely monitor the performance of SQL queries and take appropriate measures to optimize the affected statements. To track critical SQL statements before and after environment changes, there are some general steps you can follow :

  1. Extract the SQL statements along with their query plan and performance statistics from SGA or AWR.
  2. Apply the environment changes to the database, such as creating new indexes, gathering statistics, upgrading the database, or forecasting the performance of a software deployment in the production database.
  3. Obtain the query plan from the changed database environment.
  4. Compare the query plan for each SQL statement to identify any differences.
  5. Look for potential issues such as unused indexes, high-cost SQL changes, etc.
  6. Benchmark the query plan for the modified SQL statements to detect any degraded performance.

If you only have a small number of SQL statements to track in terms of performance before and after environment changes, the above steps can be done manually. However, it can be challenging if you have hundreds of SQL statements to monitor without a tool. Tosska DB Ace for Oracle is equipped with a robust tool that can assist you in tracking the performance differences of SQL statements between two databases.

DBAO SQL Performance Tracker – YouTube
Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

An Example to Show How to Tune SQL with Query Store for SQL Server

The Query Store feature in SQL Server serves as a valuable tool for troubleshooting performance issues by allowing users to quickly identify performance degradation caused by changes to query plans.
For example, when the following SQL statement is executed in SSMS, it takes 15,579 ms to finish.

Using the Top Resource Consuming Queries feature in Query Store, we can see that the SQL with Query ID 23713 and its corresponding Plan ID 37290 are displayed in the Plan Summary window.

To obtain the SQL text from SQL Server, you can manually extract it using the Query Id and accessing the relevant system tables, namely sys.query_store_query and sys.query_store_query_text. Alternatively, if you have a tool that can help extract the SQL text, it may be displayed on the screen below.
The tool accept a Query Id or partial SQL text to locate a specific SQL statement from Query Store for SQL tuning.

The screen below shows how the product endeavors to enhance SQL performance by injecting a range of Hint combinations into queries and creating corresponding Plan Guides for analysis. When done manually, this process can be difficult, as there are many possible permutations of Hints to assess. Without a comprehensive understanding of SQL tuning and the underlying problems with the query plan, identifying the best combination of Hints may require extensive trial and error.
This tool is a fully automated SQL tuning solution that utilizes Query Store. In its investigation, the tool injected 100 different Hints into the SQL queries and identified 75 unique query plans. After conducting a benchmark, it was found that the Query Store 66 (QS 66) resulted in the best performance, achieving a processing time savings of 98.45%. The optimized query included the following Hints:
OPTION(HASH JOIN, TABLE HINT(employee, INDEX(EMPS_GRADE_INX)))

Once we have determined the optimal Hints for the SQL statement, we can Force this plan for the SQL query, as displayed on the screen below. By doing so, the performance of the SQL will be improved the next time it is executed by the user’s program, without requiring any modifications to its source code.

Displayed on the screen below is evidence that executing the same SQL statement in SSMS results in significantly improved performance. The CPU time has decreased from 54202 ms to 391 ms, resulting in a 138-fold improvement, while the elapsed time has reduced from 15579 ms to 294 ms, resulting in a 52-fold improvement.

A new product designed to optimize SQL statements for Query Store
Tosska DB Ace for SQL Server marks a significant leap forward in this domain since it surpasses the reactive recovery capabilities of Query Store and introduces proactive SQL performance enhancement. This pioneering technology allows users to extract SQL from the Query Store and optimize it by creating new and improved query plans within the Query Store. With Tosska DB Ace, users can implement these new plans to their SQL without requiring any modifications to the program source code or extensive testing.

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL QS – YouTube