Abnormal behavior in TempdB SQL Server 2019

Question

enter image description hereWe have recently migrated from a version of SQL Server 2014 to a SQL Server 2019 CU 26. enter image description here enter image description here In all our tests everything went well before performing the migration but when we made the move to production we began to have serious problems with the TempDB contention.

Many processes that used tempdb blocked each other and the entire system was very slow. We have uninstalled CU 26 from SQL Server 2019 and we are with SQL Server 2019 RTM, in this case the SQL is behaving in a better way, it shows me that it is already using the TempDB datafiles and it shows me better behavior but still I have performance problems.

The production database uses TempDB in almost all its processes, since it is impossible for me to rollback to the version of SQL Server 2014, is there a way for SQL to treat TempDB as it did in version 2014 when it is in a SQL Server 2019?

The database engine has 12 TempDB datafiles.

• Martin Smith: What exactly are you seeing contention on in tempdb? Various different contention points have been addressed over recent versions
• Sean Gallardy: There really isn't any data here for us to look at and help you. Please update the question with data points such as output of queries, load, execution plans, lock blocking information, etc., so that others may help.
• Jose Navarro: LCK_M_IX,LCK_M_X,LCK_M_S,LCK_M_RS_U,LATCH_EX [ACCESS_METHODS_DATASET_PARENT]. Those are the types of waits I have when there is more concurrency on the server. I have added an image of what I get in the tempdb. Please let me know if you need more information
• Jonathan Fite: the query you show only shows 3 tempdb files, are there really 12 attached or are you looking at file explorer?
• Jose Navarro: I have been able to find the problem apparently. I have activated trace flag 9481 on the server and the behavior is very different. I no longer have tempdb contention on the server. This server was part of a 5-node AlwaysON array, activating the flag on all nodes fixed the problem

Answer 1

Check the following before vs after and see if that helps,

1. Any change in instance MAXDOP or Cost threhosld for parallelsism.
2. IOPS and throughput on tempdb volume.
3. If you have updated the compatiblilty to latest 2019, sometimes the legacy cardinality estimator gives better performance, so condider turning it ON and see how the performance looks. If this is the cause you may have to gradually update the code to better perform based on new cardinality estimator and turn the legacy OFF.

    ALTER DATABASE SCOPED CONFIGURATION 
    SET LEGACY_CARDINALITY_ESTIMATION = ON; 
    GO 
   

Refer this.

• Jose Navarro: The compatibility of the database had already been lowered to 2012 but the problems persist