SQL Server database and SQL - Tosska Technologies Limited

2023-08-26

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:

Identify the SQL that are causing performance issues in the database object.
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.
Test the Plan Guide to ensure that it provides the desired performance improvements and does not cause any unintended side effects.
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

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-04-19

How to build indexes for slow first execution SQL – SQL Server?

You may suffer from SQL statements with a slow first execution time due to the long data cache process. The following SQL is simple that retrieves records from the EMPLOYEE table that if EMP_SALARY < 500000 and the result set is ordered by EMP_NAME.

Select emp_id,
emp_name,
emp_salary,
emp_address,
emp_telephone
from employee
where emp_salary < 500000
order by emp_name;

The following is the query plan that takes 9.51 seconds for the first execution and takes 0.99 seconds for the second execution without data cache.

The SQL cannot be tuned by SQL syntax rewrite or hints injection for both the first execution and the second execution, it is because SQL Server has selected the best query plan for this simple SQL statement. But the problem is that if the condition “where emp_salary < 500000” is changed; say from 500000 to 510000 or the EMPLOYEE data is flushed out from the memory, the execution time will then be prolonged up to 9.51 seconds.

Let’s see if we can build indexes to improve this situation. There is a common perception that a good index can help to improve both the first execution time and the second execution time. So, I use a tool to explore a lot of indexes configurations, but none of them can improve both executions’ performance. Here the following is the performance of the second execution with data cached for different indexes proposed by the tool. You can see the performance of “Index Set 1” is close to the original SQL performance with a little performance variation due to the system’s loading status and all other indexes sets are worse than the original SQL. Normally, we will give up the tuning of the SQL statement without even trying to see whether those recommended indexes are good for the first execution time.

I did a test for those recommended indexes to see whether they are helpful to improve the first execution time, it surprises me that the “Index Set 1” is tested with a significant improvement and improves the first execution time from 9.51 seconds to 0.65 seconds. It is a 14 times improvement that can make my database run more efficiently. So, you should be very careful to tune your SQL with new indexes that may not be good for your second execution with all data cached, but it may be very good for your first execution without data cached.

This kind of indexes recommendation can be achieved by Tosska SQL Tuning Expert Pro for SQL Server automatically.

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

2022-02-102022-03-16

How to build indexes for multiple Max() functions for SQL Server?

For some SQL statements with multiple Max() functions in the select list and nothing in the Where clause, we have different methods to create new indexes to improve the SQL speed.

Here is an example SQL, it is to retrieve the maximum name and age from the employee table.

select max(emp_name),
max(emp_age)
from employee

The following is the query plan that takes 9.27 seconds.

The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS cannot recommend any index to improve the SQL.

For this kind of SQL that we can consider building a composite index or two individual indexes for emp_name and emp_age. A new composite of these two columns (emp_age, emp_name) can improve the SQL around 7 times. The following is the query plan shows that the new composite index is used, but it has to scan the entire index for these two stream aggregate operations before getting the max(emp_name) and max(emp_age).

How about if we build two individual indexes for emp_name and emp_age. The following is the result and query plan of these two indexes created. A Top operator selects the first row from each index and returns to the Stream Aggregate operation, and then a Nested Loops join the two maximum results together. It is 356 times much faster than the original SQL.

This kind of indexes recommendation can be achieved by Tosska SQL Tuning Expert Pro for SQL Server automatically.

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

2022-01-172022-03-16

Do not undermine your SQL Server’s potential ability

For some SQL statements that are failed to be tuned by syntax rewrite, hints injection, and all necessary indexes are built, people may think that hardware upgrade is the only way to resolve the performance problem. But, please don’t undermine your SQL Server’s SQL optimizer which can provide you with the ultimate performance solution that you may not have imagined before. What you need to do is to provide SQL Server with a set of proper new indexes.

Here is an example SQL, it is to retrieve the minimum employee’s salary and the emp_id that with salary greater than all salary of the emp_subsidiary table with subsidiary’s employees’ department = “AAA”.

SELECT emp_id,
(SELECT min(emp_salary)
FROM employee)
FROM employee
WHERE emp_salary > (SELECT max(emp_salary)
FROM emp_subsidiary
where emp_dept = ‘AAA’)

Although all columns that show in the SQL are indexed, the following query plan takes 44 seconds.

The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS can recommend only one index on one table for a SQL statement, it is failed to recommend any good index. So, the SQL cannot be tuned in any traditional way.

Let’s use our new A.I. index recommendation engine to see if there are any good index solutions. A set of indexes is recommended listed in the following. It takes only 0.55 seconds.

Example: 80 times faster A.I. SQL index recommendation

The query plan shows that two new indexes are used at the same time that the SSMS is not able to provide.

Tosska SQL Tuning Expert Pro is in-built with an A.I. engine to recommend indexes for multiple tables at the same time for a SQL statement. The new technology is so powerful to recommend multiple tables’ new indexes for a SQL at the same time, it means that how each new table’s indexes affect each other in the query plan will be considered by the engine. It is very helpful for SQL Server’s SQL optimizer to explore more potential query plans that could not be generated before. So, don’t undermine your SQL Server’s ability. Instead, use the right tool to tune your SQL statements before you are planning to upgrade your hardware.

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

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-08-182023-02-18

Selecting a SQL Server Database Version for Your Company

Choosing the right version of SQL Server is important for the performance you desire. If you’re installing an older one because your organization’s management prefers an older build or the vendor is unable to support newer versions, it is important to let them know which version your company needs, and why.

For this reason, we will discuss some popular versions of SQL Server from older to newer and mention their advantages in this blog.

Which SQL Server Version Works Best with SQL Performance Tuning?

Knowing the versions that support this task is extremely important because it will give you the ability to improve the SQL Server database and SQL performance.

To that effect, we will discuss the SQL Server 2016, 2017, and 2019 versions here.

SQL Server 2016

This version was chosen by a lot of independent software vendors or ISVs for one reason – 2016’s Service Pack 1 edition came with Enterprise features in Standard mode. These helped create a single application version that worked simultaneously for both Standard as well as Enterprise clients.

Advantages of Choosing this Version:

It is easy to find support material online as this version is quite popular and numerous database professionals are well-versed with this version’s tools.
Standard Edition users may find this version appealing since it supports 128GB RAM and additional space for internal functions such as query plans.
Support for this version ends after 2026 – longer than the older versions (2012/2014).
Newer applications that have additional compliance requirements will benefit from features in this version such as Always Encrypted, temporal tables, and Dynamic Data Masking. These will make it somewhat easier to protect and monitor sensitive information.
You can have both row store and column store indexes in this version, unlike the earlier ones that only had row store indexes.
If you need query plan monitoring to help with SQL performance tuning, you can use the Query Store’s features provided in SQL Server 2016 for this purpose.

SQL Server 2017

Being a newer release, it is one of the most regularly updated versions with patches coming in almost every other month. These patches are important because they resolve significant problems. It also comes with a minimum commit replica configuration to ensure commits are accepted by several replicas.

Advantages of Choosing this Version:

The upgrades are easier to get from this version onward due to a Distributed Availability Group that contains multiple SQL Server versions in it. Before this, we had AG version upgrades that were not as convenient, leading most users to construct a new cluster and migrate to it rather than opt for an upgrade.
This version contains batch mode execution plans, which gives those who require high-performance column store statements an advantage.
If you must run your SQL Server on Linux, you may consider SQL Server 2017 as several bugs have been resolved in the Cumulative Updates.
It’s a newer version so support will last longer than that of its predecessor.

SQL Server 2019

Released on November 4, 2019, this version is the latest in the SQL Server series. Naturally, it comes with the longest support lifespan, i.e. it will be supported until 2030. This version also receives regular patch updates to fix many significant issues in the form of Cumulative Updates.

Changes and Features in this Version:

Patch contents aren’t documented anymore. Moreover, you are likely to receive updates with undocumented new features – something to consider in case you require it for mission-critical production environments.
There is a bit of a learning curve thanks to some cutting-edge features in this version, so be prepared to perform some experimentation as you learn.
Some of the best performance features are included in the 2019 compatibility mode. However, you will have to keep a close eye on all SQL Server databases and SQL queries – even the ones running fast at present – as these will alter your current execution plans. In other words, you will have to test both slow and fast queries to make sure the slow ones speed up and the fast ones don’t fall behind in performance.
Table variables have gotten better in this version along with user-defined functions.
Additional features to watch out for including Big Data Clusters, Java support, and high container availability, so you may want to explore this version if you’re looking for perks like these in the SQL Server you want.

In Conclusion

At this point, SQL Server 2017 might seem like the best version to go with, thanks to a balance of features, stability, and support lifespan. Furthermore, you’ll receive plenty of help with SQL performance tuning – a lifesaver for overworked professionals who may not have the time or resources to upgrade every server every year.

2021-07-202021-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’

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

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 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