temp table performance in sql 2016

  • 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

  • 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?

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson
    I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

  • 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.

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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.

  • 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.

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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