hints - Tosska Technologies Limited

2023-07-12

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

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

2023-07-06

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.

In the given example, we can observe a SQL query (ID 23058) that has two query plans. The yellow dot corresponds to a query plan that exhibits a relatively stable performance, whereas the blue dots indicate a more fluctuating performance plan. To enhance the stability of this SQL’s performance, we can designate the yellow dot’s query plan as the default plan by using the “Force Plan” function in Query Store.

Query Store is a powerful feature provided by SQL Server that enables users to Force a specific query plan for a SQL in Query Store. However, Query Store has limitations, as it does not allow users to create a new query plan that has not been generated before. Its use is reactive, meaning it only allows for the recovery of degraded SQL performance without providing a means for users to improve SQL statements that better plans were not generated before.

How to manually tune a SQL with Query Store?
If you want to manually improve the performance of a SQL query stored in Query Store, the process can be quite complex. Here are some general steps to follow as a guideline:

Extract the SQL text you want to tune from the system tables sys.query_store_query and sys.query_store_query_text.
Tune the SQL by injecting various hints and identifying the best hint application to improve query performance.
Create a plan guide for the SQL text, keeping the original SQL text format and incorporating the hints identified in step 2.
Execute the SQL with the newly created plan guide to generate a new query plan in Query Store.
Use SQL Server Management Studio to force the new query plan with the SQL.
Finally, drop the plan guide.

By following these steps, users can manually tune a SQL query in Query Store and achieve improved performance. However, it is important to note that this process can be complex and time-consuming, and should only be undertaken by experienced database administrators with a deep understanding of SQL performance optimization.

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

2022-10-10

How to Tune SQL Statement with CASE Expression by Hints Injection for Oracle?

Here the following is a simple SQL statement with a CASE expression syntax.

SELECT *
FROM employee
WHERE
CASE
WHEN emp_salary< 1000
THEN ‘low’
WHEN emp_salary>100000
THEN ‘high’
ELSE ‘Normal’
END = ‘low’

Here the following are the query plans of this SQL, it takes 4.64 seconds to finish. The query shows a Full Table Scan of the EMPLOYEE table due to the CASE expression cannot utilize the emp_salary index. It is because the CASE statement disabled the index range search of the emp_salary index.

Commonly, we will try to enable index search by forcing the SQL with an Index hint as the following:

SELECT /*+ INDEX(@SEL$1 EMPLOYEE) */ *
FROM employee
WHERE CASE
WHEN emp_salary < 1000
THEN ‘low’
WHEN emp_salary > 100000
THEN ‘high’
ELSE ‘Normal’
END = ‘low’

Although the CASE statement disabled the index range search of the emp_salary index, an index full scan is now enabled to help filter the result more quickly compared with the original full table scan of the EMPLOYEE table.

This hint injection takes 0.38 seconds and it is 12 times faster than the original SQL will full table scan. For this kind of SQL statement that you cannot change your source code, you can use SQL Patch with the hints and SQL text deployed to the database without the need of changing your source code.

If you can modify your source code, the best performance will be to rewrite the CASE expression into the following syntax with multiple OR conditions.

SELECT *
FROM employee
WHERE emp_salary < 1000
AND ‘low’ = ‘low’
OR NOT ( emp_salary < 1000 )
  AND emp_salary > 100000
  AND ‘high’ = ‘low’
OR NOT ( emp_salary < 1000
  OR emp_salary > 100000 )
  AND ‘Normal’ = ‘low’

The new query plan shows an INDEX RANGE SCAN OF emp_salary index.

This kind of rewrite and hints injection can be achieved by Tosska SQL Tuning Expert Pro for Oracle automatically,

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

2022-04-19

How to index SQL with aggregate function SQL for Oracle?

Here the following is an example SQL shows you that select the maximum emp_address which is not indexed in the EMPLOYEE table with 3 million records, the emp_grade is an indexed column.

select max(emp_address) from employee a
where emp_grade<4000

As 80% of the EMPLOYEE table’s records will be retrieved to examine the maximum emp_address string. The query plan of this SQL shows a Table Access Full on EMPLOYEE table is reasonable.

How many ways to build an index to improve this SQL?
Although it is simple SQL, there are still 3 ways to build an index to improve this SQL, the following are the possible indexes that can be built for the SQL, the first one is a single column index and the 2 and 3 are the composite index with a different order.
1. EMP_ADDRESS
2. EMP_GRADE, EMP_ADDRESS
3. EMP_ADDRESS, EMP_GRADE

Most people may use the EMP_ADDRESS as the first choice to improve this SQL, let’s see what the query plan is if we build a virtual index for the EMP_ADDRESS column in the following, you can see the estimated cost is reduced by almost half, but this query plan is finally not being used after the physical index is built for benchmarking due to actual statistics is collected.

The following query shows the EMP_ADDRESS index is not used and the query plan is the same as the original SQL without any new index built.

Let’s try the second composite index (EMP_GRADE, EMP_ADDRESS), the new query plan shows an Index Fast Full Scan of this index, it is a reasonable plan which no table’s data is needed to retrieve. So, the execution time is reduced from 16.83 seconds to 3.89 seconds.

Let’s test the last composite index (EMP_ADDRESS, EMP_GRADE) that EMP_ADDRESS is placed as the first column in the composite index, it creates a new query plan that shows an extra FIRST ROW operation for the INDEX FULL SCAN (MIN/MAX), it highly reduces the execution time from 16.83 seconds to 0.08 seconds.

So, indexing sometimes is an art that needs you to pay more attention to it, some potential solutions may perform excess your expectation.

The best index solution is now more than 200 times better than the original SQL without index, this kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

2022-02-102022-03-16

How to use FORCE INDEX Hints to tune an UPDATE SQL statement?

We used to use FORCE INDEX hints to enable an index search for a SQL statement if a specific index is not used. It is due to the database SQL optimizer thinking that not using the specific index will perform better. But enabling an index is not as simple as just adding an index search in the query plan, it may entirely change the structure of the query plan, which means that forecasting the performance of the new Force Index hints is not easy. Here is an example to show you how to use FORCE INDEX optimization hints to tune a SQL statement.

A simple example SQL that updates EMP_SUBSIDIARY if the emp_id is found in EMPLOYEE with certain criteria.

update EMP_SUBSIDIARY set emp_name=concat(emp_name,'(Headquarter)’)
where emp_id in
(SELECT emp_id
FROM EMPLOYEE
WHERE emp_salary <1000000
and emp_grade<1150)

Here the following is the query plan of this SQL, it takes 18.38 seconds. The query shows a Full Table Scan of EMPLOYEE and then Nested Loop to EMP_SUBSIDIARY with a Unique Key Lookup of Emp_sub_PK index.

We can see that the filter condition “emp_salary <1000000 and emp_grade<1150” is used for the full table scan of EMPLOYEE. The estimated “filtered (ratio of rows produced per rows examined): 3.79%”, it seems the MySQL SQL optimizer is failed to use an index to scan the EMPLOYEE table. We should consider forcing MySQL to use either one of emp_salary or emp_grade index.

Unless you fully understand the data distribution and do a very precise calculation, otherwise you are not able to tell which index is the best?

Let’s try to force the index of emp_salary first.

update   EMP_SUBSIDIARY
set   emp_name=concat(emp_name,‘(Headquarter)’)
where emp_id in (select  emp_id
from    EMPLOYEE FORCE INDEX(`emps_salary_inx`)
where emp_salary < 1000000
and emp_grade < 1150)

This SQL takes 8.92 seconds and is 2 times better than the original query plan without force index hints.

Let’s try to force the index of emp_grade again.

update   EMP_SUBSIDIARY
set   emp_name=concat(emp_name,‘(Headquarter)’)
where emp_id in (select  emp_id
from    EMPLOYEE FORCE INDEX(`emps_grade_inx`)
where emp_salary < 1000000
and emp_grade < 1150)

Here is the result query plan of the Hints FORCE INDEX(`emps_grade_inx`) injected SQL and the execution time is reduced to 3.95 seconds. The new query plan shows an Index Range Scan of EMPLOYEE by EMP_GRADE index, the result is fed to a subquery2(temp table) and Nested Loop to EMP_SUBSIDIARY for the update. This query plan’s estimated cost is lower and performs better than the original SQL. It is due to the limited plan space in the real-time SQL optimization process, so this query plan cannot be generated for the original SQL text, so manual hints injection is necessary for this SQL statement to help MySQL database SQL optimizer to find a better query plan.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the Hints injected SQL is more than 4.6 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

2022-01-172022-03-16

How is the order of the columns in a composite index affecting a subquery performance for Oracle?

We know the order of the columns in a composite index will determine the usage of the index or not against a table. A query will use a composite index only if the where clause of the query has at least the leading/left-most columns of the index in it. But, it is far more complicated in correlated subquery situations. Let’s have an example SQL to elaborate the details in the following.

SELECT D.*
FROM department D
WHERE EXISTS (SELECT    Count(*)
FROM    employee E
WHERE     E.emp_id < 1050000
AND E.emp_dept = D.dpt_id
GROUP BY E.emp_dept
HAVING    Count(*) > 124)

Here the following is the query plan of the SQL, it takes 10 seconds to finish. We can see that the SQL can utilize E.emp_id and E.emp_dept indexes individually.

Let’s see if a new composite index can help to improve the SQL’s performance or not, as a rule of thumb, a higher selectivity column E.emp_id will be set as the first column in a composite index (E.emp_id, E.emp_dept).

The following is the query plan of a new composite index (E.emp_id, E.emp_dept) and the result performance is not good, it takes 11.8 seconds and it is even worse than the original query plan.

If we change the order of the columns in the composite index to (E.emp_dept, E.emp_id), the following query plan is generated and the speed is improved to 0.31 seconds.

The above two query plans are similar, the only difference is the “2” operation. The first composite index with first column E.emp_id uses an INDEX RANGE SCAN of the new composite index, but the second query plan uses an INDEX SKIP SCAN for the first column of E.emp_dept composite index. You can see there is an extra filter operation for E.emp_dept in the Predicate Information of INDEX RANGE SCAN of the index (E.emp_id, E.emp_dept). But the (E.emp_dept, E.emp_id) composite index use INDEX SKIP SCAN without extra operation to filter the E.emp_dept again.

So, you have to test the order of composite index very carefully for correlated subqueries, sometimes it will give you improvements that exceed your expectation.

This kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

2021-11-262022-03-16

How to Tune SQL Statements with NO_RANGE_OPTIMIZATION Hints Injection?

There are some SQL statements with performance problem can be tuned by Hints injection only. Here is an example to show you how to use NO_RANGE_OPTIMIZATION optimization hints to tune a SQL statement.

A simple example SQL that retrieves data from EMPLOYEE and EMP_SAL_HIST tables.

select * from employee a,emp_sal_hist h
where a.emp_id =h.sal_emp_id
and a.emp_dept < ‘B’
and h.sal_salary between 1000000 and 2000000

Here the following are the query plans of this SQL, it takes 24.3 seconds. The query shows an Index Range Scan (EMPS_DPT_INX) of EMPLOYEE and then Nested Loop to EMP_SAL_HIST with a Non-Unique Key Lookup of SALS_EMP_INX index.

The EMP_SAL_HIST is the employee’s salary history table which keeps more than one salary record for each employee. So, EMPLOYEE to EMP_SAL_HIST is a one-to-many relationship. The speed of a nested loop operation is highly dependent on the driving path of two nested loop tables. MySQL SQL optimizer estimated that the condition (a.emp_dept < ‘B’) can rapidly reduce the result set, so the driving path that “from EMPLOYEE to EMP_SAL_HIST” is selected.

Unless you fully understand the data distribution and do a very precise calculation, otherwise you are not able to tell whether this driving path is the best or not.

How to make MySQL consider another driving path “from EMP_SAL_HIST to EMPLOYEE”? Let’s take a look at MySQL documentation:

NO_RANGE_OPTIMIZATION: Disable index range access for the specified table or indexes. This hint also disables Index Merge and Loose Index Scan for the table or indexes. By default, range access is a candidate optimization strategy, so there is no hint for enabling it.

This hint may be useful when the number of ranges may be high and range optimization would require many resources.

To disable the Index Range Scan of the EMPLOYEE table, I explicitly add a Hints /*+ QB_NAME(QB1) NO_RANGE_OPTIMIZATION(`a`@QB1) */ to the SQL statement and hope that MySQL will use the Index Range Scan by the condition (h.sal_salary between 1000000 and 2000000) as the first driving table.

select /*+ QB_NAME(QB1) NO_RANGE_OPTIMIZATION(`a`@QB1) */ *
from    employee a,
  emp_sal_hist h
where a.emp_id = h.sal_emp_id
  and a.emp_dept < ‘B’
  and h.sal_salary between 1000000 and 2000000

Here is the result query plan of the Hints injected SQL and the execution time is reduced to 10.01 seconds. The new query plan shows that the driving path is changed from EMP_SAL_HIST table nested loop to EMPLOYEE table. So, sometimes you may make use of the NO_RANGE_OPTIMIZATION hint to control the driving path order to see if MySQL can run your SQL faster.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the Hints injected SQL is more than 2 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

2021-11-262022-03-16

How to Tune SQL Statement with CASE Expression for SQL Server II?

We have discussed how to tune a CASE expression SQL with hardcoded literals in my last blog:

How to Tune SQL Statement with CASE Expression for SQL Server I?

SELECT *
FROM EMPLOYEE
WHERE
CASE
when emp_id < 1001000 then ‘Old Employee’
when emp_dept <‘B’ then ‘Old Department’
ELSE ‘Normal’
END = ‘old Employee’

If I change the hardcoded literal to a @var, what will be the performance of the last blog’s rewritten SQL?

SELECT *
FROM EMPLOYEE
WHERE
CASE
when emp_id < 1001000 then ‘Old Employee’
when emp_dept <‘B’ then ‘Old Department’
ELSE ‘Normal’
END = @var

I use the same method in my last blog to rewrite this SQL into the following multiple OR syntax, but the SQL Server optimizer change back to a full table scan of the EMPLOYEE table. It is because the SQL Server cannot do a good cardinality estimation of the variable of @var.

select *
from EMPLOYEE
where emp_id < 1005000
  and ‘Old Employee’ = @var
  or not ( emp_id < 1005000 )
and emp_dept < ‘B’
and ‘Old Department’ = @var
  or not ( emp_id < 1005000 )
and not ( emp_dept < ‘B’ )
and ‘Normal’ = @var

We can rewrite the CASE expression into the following syntax with multiple UNION ALL statements, this syntax is more complicated than the rewrite with multiple OR conditions in my last blog. But it can make SQL Server improve the query plan to be more efficient.

select *
from EMPLOYEE
where emp_id < (select 1005000)
  and ‘Old Employee’ = @var
union all
select *
from EMPLOYEE
where ( not ( emp_id < 1005000 )
and ‘Old Employee’ = @var
or @var is null )
  and emp_id >= 1005000
  and emp_dept < ‘B’
  and ‘Old Department’ = @var
union all
select *
from EMPLOYEE
where ( not ( emp_id < 1005000 )
and ‘Old Employee’ = @var
or @var is null )
  and ( not ( emp_id >= 1005000
and emp_dept < ‘B’
and ‘Old Department’ = @var )
or @var is null )
  and emp_id >= 1005000
  and emp_dept >= ‘B’
  and ‘Normal’ = @var

Here is the query plan of the rewritten SQL and the speed is 0.448 seconds. It is 5 times better than the original syntax. People may think that there are two table scan operations of EMPLOYEE that will slow down the whole process, but actually, the corresponding filter operations will stop the table scan operations immediately due to the filter conditions ‘Normal’ = @var and ‘Old Department’ = @var will not be satisfied. This kind of query plan cannot be generated by SQL Server’s internal SQL optimizer, it means that you cannot use Hints injection to get this query plan.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for SQL Server automatically.

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

2021-11-252021-12-15

How to Tune SQL Statement with CASE Expression for SQL Server I?

Here the following is a simple SQL statement with a CASE expression syntax.

SELECT *
FROM EMPLOYEE
WHERE
CASE
when emp_id < 1001000 then ‘Old Employee’
when emp_dept <‘B’ then ‘Old Department’
ELSE ‘Normal’
END = ‘old Employee’

Here the following are the query plans of this SQL, it takes 2.23 seconds in a cold cache situation, which means data will be cached during the SQL is executing. The query shows a Full Table Scan of the EMPLOYEE table due to the CASE expression cannot utilize the emp_id index or emp_dept index.

We can rewrite the CASE expression into the following syntax with multiple OR conditions.

select *
from EMPLOYEE
where emp_id < 1005000
and ‘Old Employee’ = ‘Old Employee’
or not ( emp_id < 1005000 )
and emp_dept < ‘B’
and ‘Old Department’ = ‘Old Employee’
or not ( emp_id < 1005000 )
and not ( emp_dept < ‘B’ )
and ‘Normal’ = ‘Old Employee’

Here is the query plan of the rewritten SQL and the speed is 0.086 seconds. It is 25 times better than the original syntax. The new query plan shows an Index Seek of EMP_ID index.

This SQL rewrite is useful when the CASE expression is equal to a hardcoded literal, but if the literal “ =’Old Employee’ ” replaced by a variable “ = :var ”, this rewrite may not be useful, I will discuss it in my next blog.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for SQL Server automatically.

Expert (TSES™) for SQL Server® – Tosska Technologies Limited

2021-07-20

How to Tune SQL Statements with Rewrite and Hints Injection for MySQL?

There are some SQL statements with performance problem have to be tuned by SQL syntax rewrite and Hints injection, it is a little bit difficult for SQL tuning newcomers to master this technique. Developers not only have to understand the relationship between SQL syntax and the final query plan generation but have to understand the usage of optimizer hints and its limitations. Sometimes these two tuning techniques application will affect each other in a complex SQL statement.

Here is a simple example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables.

select * from employee,department
where emp_dept=dpt_id
and emp_dept<‘L’
and emp_id<1500000
and emp_salary= dpt_avg_salary
order by dpt_avg_salary

Here the following are the query plans of this SQL, it takes 7.7 seconds to finish. The query shows a “Full Table Scan Department” and nested loop Employee table with a Non-Unique Key Lookup EMPS_SALARY_INX.

You can see that this SQL cannot utilize index scan even though the dpt_dept is an indexed field. It is because the condition emp_dept<‘L’ is not explicitly induced the condition dpt_id < ‘L’ although emp_dept=dpt_id is also listed in the where clause.

To enable the index search of Department table, I explicitly add a condition dpt_id < ‘L’ to the SQL statement as the following:

select *
from employee,
department
where emp_dept = dpt_id
and dpt_id < ‘L’
and emp_dept < ‘L’
and emp_id < 1500000
and emp_salary = dpt_avg_salary
order by dpt_avg_salary

Here is the query plan of the rewritten SQL and the execution time is reduced to 3.4 seconds. The new query plan shows that an Index Range Scan is used for the Department table and nested loop Employee table.

You may find that the nested loop to Employee by EMPS_SALARY_INX lookup may result into a lot of random access to the Employee table. Let me add a BKA hint to ask MySQL to use ‘Batched Key Access’ to join the two tables.

select /*+ QB_NAME(QB1) BKA(`employee`@QB1) */ *
from employee,
department
where emp_dept = dpt_id
and dpt_id < ‘L’
and emp_dept < ‘L’
and emp_id < 1500000
and emp_salary = dpt_avg_salary
order by dpt_avg_salary

The new query plan shows a Batched Key Access is used to join Department and Employee tables, you can BAK information from MySQL manual for details, the new plan takes only 1.99 seconds and it is more than 3 times better than the original SQL syntax.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/