none
In SQL 2016, Is really need to specify all the columns in select clause to the non-clustered index ?

    General discussion

  • Hello Everyone,

    We are currently moving from SQL 2012 to SQL 2016. I am testing existing queries for the performance regressing.

    One thing I found is, the query which was running under 2012 was taking 1 second whereas the same query is taking 10 seconds in SQL 2012. After few research I added SET STATISTICS IO ON and was wonder about the results, and it shows really high value for the logical reads, read ahead reads. The value in SQL 2012 was

    Table '_EventAttribute'. Scan count 204, logical reads 7856, physical reads 2, read-ahead reads 152, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    and in SQL 2016 is now really high

    Table '_EventAttribute'. Scan count 1, logical reads 153827, physical reads 2, read-ahead reads 153865, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


    Is there any changes in SQL 2016 database engine side, especially covering index ?

    Because if I add the rest of the columns in the select clause other than key columns to the index with INCLUDE, it gives the expected result(1 second).

    Or Is there any other thing I need to investigate more ?


    Maharajan

    Monday, April 3, 2017 2:39 AM

All replies

  • Is there any changes in SQL 2016 database engine side


    Yes, we got a new Cardinality Estimation (SQL Server). Change the comp level of the database down to SQL Server 2012 (120) and runt the query again to see if you get a different runtime.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, April 3, 2017 3:59 AM
  • Olaf Helper,

    Yes, That's right.

    After I turn-on Legacy Cardinality Estimation in Database Scoped Configuration, my query executes with faster query plan.

    In addition to this, I found two more ways to resolve the issue

    1. Used Query Store to collect query plan with different compatibility level and forced faster query plan and it solved my issue.

    2. Redesigned my non clustered index to covering index to include the columns in the select clause. This also resolved.

    Now I have small confusion to choose the best option for the production environment. 


    Maharajan

    Monday, April 3, 2017 6:57 AM
  • >Now I have small confusion to choose the best option for the production environment. 

    In order from Best to Worst:

    -Fixing your query or indexing to work well with the SQL 2016 Query Optimizer.

    -Using Query Store to force the plan.

    -Turning on Legacy Cardinality Estimation for the Database.

    David


    Microsoft Technology Center - Dallas
    My blog

    Monday, April 3, 2017 2:07 PM
  • I like to butt in here. Fixing the query is a good idea. Adding columns in the SELECT list as included columns? Hm, what if a new user requirement in two months calls for adding a new column to the query? The index will no longer be covering. (And if you forced a plan through Query Store, you did not force it for the new query.)

    Without seeing the full query, it is difficult to give exact advice. My experience is that when you run into things like this that is something which is not optimal with the query or the indexing.

    I should add that if there is good reason to believe that the SELECT list is indeed stable voer time, then adding columns as included in the index is alright.

    Monday, April 3, 2017 3:28 PM
  • Yes, I agree with that.

    Because If I fix the query or index I don't to think lot about future vNext. :)


    Maharajan

    Wednesday, April 5, 2017 12:26 AM
  • Erland, 

    I do agree your point too..

    But in my case, I don't have db changes often. So for the time being, I can go for redesign covering index.

    One thing, I really don't know, how many indexes I have to redesign throughout the database. ;)


    Maharajan

    Wednesday, April 5, 2017 12:33 AM