locked
PAGELATCH_UP:tempdb:12(PFS) contention RRS feed

  • Question

  • Hi All,

    I am observing multiple occurences of (814ms)PAGELATCH_UP:tempdb:12(PFS) wait type on my production server and all the sessions are blocked aswell, these occurences are been observed only during a particular time frame.

     

    Server Details:

     

    Microsoft SQL Server 2016 (SP1)

    Max server memory - 149 GB

    18 processors

    MaxDop - 4

     

    Initially we have 8 tempdb datafiles, i have added 4 more tempdb datafiles to check if it can help in fixing this contention, but unfortunately it doesn't worked.

     

    Not enabling Trace : 1117/1118 since this is sql 2016

     

    There is a spike in my Tasks,Workers and threads during same time frame

     

    most of the occurrences are from same database

     

    can i change the isolation level to Read_committed_snapshot isolation to see if it helps in reducing blockings, but this is a datawarehousing server , so not sure it can impact the data.

     

    i am attaching the screenshot for reference

     

    can someone help me how to solve this issue.

    Regards,

    Attachments area
    Wednesday, July 29, 2020 2:11 PM

All replies

  • Are you creating lot of tempdb tables, updating and deleting it ? Is this contention causing any issue. Is this happening all the time or just when few queries run ?

    8 Tempdb files are sufficient

    Did you made sure that initial size and autogrowth of all the tempdb files are same, this is important


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, July 29, 2020 2:24 PM
  • yes tempdb table creation , updation and deletion are happening , it is a datawarehouisng server.

    I am observing this contention only during specific time period, this contention is leading in blockings of all queries that ran at the same time 

    Initially we had 8 tempdb files , i added 4 more datafiles to check if it can help me in resolving the contention of PAGELATCH_UP:tempdb:12(PFS)

     Initial size and autogrowth of all files are same.

    Thursday, July 30, 2020 8:44 AM

  • I am observing this contention only during specific time period, this contention is leading in blockings of all queries that ran at the same time 

    I believe this is query or workload specific. You need to ask developer to have a look at query and ask him to. if possible, use less temp tables. 

    Plus if your tempdb on fast SSD drive, what is underlying storage


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, July 30, 2020 1:45 PM