• George M Parker (11/11/2012)


    Make sure you have a tempdb allocation problem first by confirming that you are seeing file I/O latch waits in tempdb (database = 2).

    No.....

    IO latch contention is not why one would typically split TempDB. IO latch contention (PageIOLatch) suggests that the IO subsystem is being driven beyond what it can handle, maybe because there's too much demand, maybe because there's a poor IO subsystem.

    If there's excessive IO latch contention in TempDB that would typically suggest that TempDB needs to be moved to different storage array, one that can handle more throughput (potentially SSD) or that the app needs to be tuned to read/write less to TempDB.

    The indicator that TempDB has the allocation problem cmmonly solved by adding files is latch contention (PageLatch) on the first set of allocation pages in the file. These typically appear as PageLatch waits on 2:1:3 (most common), 2:1:2 or 2:1:1.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass