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
