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