Post reply

tempdb contention on System tables

  • May 6, 2015 at 8:03 am

    #296707

    I have started noticing tempdb contention on system tables(sysallocunits,sysschobjs, sysidxstats) there is only one data file and log file configured on the server which has 2 physical CPU.

    Adding multiple data files does it help, we don't this issue all of a sudden it started appearing today.

  • May 6, 2015 at 8:51 am

    #1795958

    You should alse be seeing non negligible waits for PAGELATCH_UP.

    Multiple data files do definitely help alleviate contention.

    -- Gianluca Sartori

  • May 6, 2015 at 8:58 am

    #1795960

    I quickly ran wait stats on the server in question. the two session that are in blocking state have following locks

    PREEMPTIVE_OS_GETPROCADDRESS blocking

    LCK_M_S blocked

    I believe blocking session is holding lock on tempdb tables

Viewing 3 posts - 1 through 2 (of 2 total)

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