• winmansoft (4/6/2013)


    I did run this query on server

    SELECT session_id AS SessionID,

    wait_duration_ms AS Wait_Time_In_Milliseconds,

    resource_description AS Type_of_Allocation_Contention

    FROM sys.dm_os_waiting_tasks

    WHERE wait_type LIKE 'PAGELATCH_%'

    AND (resource_description LIKE '2:%:1'

    OR resource_description LIKE '2:%:2'

    OR resource_description LIKE '2:%:3')

    I got no rows. Is that means we don't have allocation contention on tempdb?

    Yes, but only for the instant you ran that query.

    Again I will say that the odds that tempdb contention is the PRIMARY cause of your slowness is VERY LOW compared to the myriad other causes.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service