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

Discussion in 'SQL Server' started by Richard To, Jan 24, 2022.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    72
    Likes Received:
    27
    Trophy Points:
    18
    Gender:
    Male
    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.
    [​IMG]
    The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS cannot recommend any index to improve the SQL.
    [​IMG]
    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).
    [​IMG]
    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.
    [​IMG]
    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
     
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice