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

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

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

Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server® – System Requirements

Before installing Tosska SQL Tuning Expert Pro (TSES Pro™) 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