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