temp table performance in sql 2016

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    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

  • david.edwards 76768

    SSC Eights!

    Points: 999

    Apologies if you have already checked the obvious, but you haven't mentioned it. Is the SQL version the /only/ difference?

    Is tempDB configured the same on both servers: separate, faster storage, same appropriate number of files, log file location comparable etc etc?

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    HI David,

    tempdb is configured the same way on both machines. But I have Compatibility level to 2012 (110) and Legacy Cardinality Estimation to TRUE on sql 2016 temodb as well. Files, log file location etc are exactly the same.

  • Grant Fritchey

    SSC Guru

    Points: 396617

    Compare the execution plans. Capture a full set of wait statistics for the query. Use the information from those two sources to see what the primary differences are. Overall, 2016 is faster than 2012. There are exceptions, but they're usually caused by edge cases.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    Hi Grant,

    What concerning is that same query is running  faster in 2012. I would look at the query and execution plan but at this point of stage in this project, we do not want to change the queries specially if this has bene running fine. I was wondering I am missing something on a sql server level and not at the query level.

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    Also, 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.

  • Grant Fritchey

    SSC Guru

    Points: 396617

    OK. Have you compared the execution plans as I suggested? Yes, it could be a system or database setting. However, it could be that the query is simply getting resolved differently on 2016 than on 2012. Making that comparison will give you information. It's possible that you may have to rearchitect the query. It happens. It's not always convenient or enjoyable, but there we are.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply