locked
temp tables performance in sql2016 RRS feed

  • Question

  • HI everyone,

    I just restored a DB on sql2016 from sql2012. One of my sql queries that heavily uses temp tables is running 3x slowin sql 2016 . I am still baffled with this performance degradation. I read some articles on google to set Compatibility level to 2012 (110) and Legacy Cardinality Estimation to TRUE that will boost the performance. But even changing the parameters didnt help. Same queries using temp table is running 2x faster on sql 2012. 

    Has anyone faced this kind of performance degradation before? I would look into the query but again reason it is running faster in sql2012 tells me there is some performance configuration I am missing. 

    Any help is appreciated.

    Thanks,

    Rohit

    Sunday, July 12, 2020 6:39 AM

All replies

  • Where do you have your SQL Server installed and physical place for your temp DB? Are you using different servers?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, July 12, 2020 6:09 PM
  • Hi ,

    Its a different windows machine. Sql2016 is on windows 2016 machine. But the way physical files are organized is exactly the same. 

    Sunday, July 12, 2020 6:21 PM
  • One more thing - did you re-build all the indexes are restoring DB? This may help with the performance.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, July 12, 2020 8:18 PM
  • Hi Rohit,

    After upgrading from the old version of the database to the new version, you must rebuild the index and update the statistics, otherwise the performance will be affected.

    In addition try to use the Query Tuning Assistant(QTA) feature in SSMS 18.X to keep performance stability.

    Please refer to Upgrading Databases by using the Query Tuning Assistant.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 13, 2020 2:52 AM
  • Hi Guys,

    Yes, I have rebuild the index and I checked to make sure that all index and stats were same as in the old sql 2012 machine.

    sELECT name AS index_name,

    STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
    FROM sys.indexes
    where STATS_DATE(OBJECT_ID, index_id) is not NULL
    order by STATS_DATE(OBJECT_ID, index_id) desc

    I also ran this query to make sure of any configuration diff:

    SELECT *

    FROM sys.configurations
    where value_in_use = 1
    ORDER BY name ;
    GO

    Also, I said above, I set the Legacy Cardinality Estimation in both tempdb and main DB to True. they way data files are organized in both 2012 ans 2016 are the same as well.

    Tuesday, July 14, 2020 1:50 AM
  • a deep diving in the query I see this an issue of "wait info cxconsumer". One of the query is waiting on producer and CPU allocation. But unfortunately nothing else is running on the machine. could this a bad temp allocation and I need to rewrite the query? here are some parameters:

    wait_info                                               CPU                    tempdb_allocations tempdb_current
    (2880151ms)CXCONSUMER                       0           3,918,328              2,655,848

    Tuesday, July 14, 2020 6:56 AM
  • Is it your local development environment? If yes, can you try re-starting the server (so we start with the fresh TempDB)?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 14, 2020 12:53 PM
  • have you got your .ldf and .mdf TEMPDB files spreaded accordingly cores VM?

    In a separated storage from .mdf and .ldf files for the rest of the databases

    Tuesday, July 14, 2020 12:57 PM
  • Hi Rohit,

    Is there any update on this case?

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 24, 2020 1:17 AM