To find out the wait-types in sql server

SELECT

wait_type, waiting_tasks_count

, wait_time_ms, max_wait_time_ms, signal_wait_time_ms

INTO #PreWorkWaitStats

FROM sys.dm_os_wait_stats

WAITFOR delay '00:00:30'

SELECT

wait_type, waiting_tasks_count, wait_time_ms

, max_wait_time_ms, signal_wait_time_ms

INTO #PostWorkWaitStats

FROM sys.dm_os_wait_stats

SELECT

p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms

, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms

, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0)) - (p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)))

AS RealWait

, p2.wait_type

,GETDATE() as lastupdated

FROM #PreWorkWaitStats p1

RIGHT OUTER JOIN

#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)

WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0

AND p2.wait_type NOT LIKE '%SLEEP%'

AND p2.wait_type != 'WAITFOR'

ORDER BY RealWait DESC