USE MASTER
SET NOCOUNT ON


CREATE TABLE #wait_stats (
	wait_type            nvarchar(60) NOT NULL
	,waiting_tasks_count bigint       NOT NULL
	,wait_time_ms        bigint       NOT NULL
	,max_wait_time_ms    bigint       NOT NULL
	,signal_wait_time_ms bigint       NOT NULL
)

CREATE TABLE #wait_type (
	wait_type            nvarchar(60) NOT NULL
	,comment             varchar(max) NOT NULL
)


INSERT INTO #wait_stats (
	wait_type
	,waiting_tasks_count
	,wait_time_ms
	,max_wait_time_ms
	,signal_wait_time_ms
)
SELECT
	wait_type
	,waiting_tasks_count
	,wait_time_ms
	,max_wait_time_ms
	,signal_wait_time_ms
FROM
	sys.dm_os_wait_stats
WHERE
	waiting_tasks_count <> 0


INSERT INTO #wait_type (
	wait_type
	,comment
)
SELECT           'SOS_SCHEDULER_YIELD'              , 'CPU - Execute this script: SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 ;If runnable tasks count > zero, CPU issues if double digits for any length of time, extreme CPU concern'
UNION ALL SELECT 'CXPACKET'                         , 'SETTINGS OR CODE - Wait stats shows more than 5% of your waits are on CXPackets, you may want to test lower (or non-zero) values of “max degree of parallelism”. Never set value great than # of CPUs'
UNION ALL SELECT 'ASYNC_NETWORK_IO'                 , 'NETWORK - Occurs on network writes when the task is blocked behind the network'
UNION ALL SELECT 'LCK_M_BU'                         , 'LOCK - Waiting to acquire a Bulk Update (BU) lock'
UNION ALL SELECT 'LCK_M_IS'                         , 'LOCK - Waiting to acquire an Intent Shared (IS) lock'
UNION ALL SELECT 'LCK_M_IU'                         , 'LOCK - Waiting to acquire an Intent Update (IU) lock '
UNION ALL SELECT 'LCK_M_IX'                         , 'LOCK - Waiting to acquire an Intent Exclusive (IX) lock'
UNION ALL SELECT 'LCK_M_RIn_NL'                     , 'LOCK - Waiting to acquire a NULL lock on the current key value and an Insert Range lock between the current and previous key'
UNION ALL SELECT 'LCK_M_RIn_S'                      , 'LOCK - Waiting to acquire a shared lock on the current key value and an Insert Range lock between the current and previous key'
UNION ALL SELECT 'LCK_M_RIn_U'                      , 'LOCK - Waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key'
UNION ALL SELECT 'LCK_M_RIn_X'                      , 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key'
UNION ALL SELECT 'LCK_M_RS_S'                       , 'LOCK - Waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous'
UNION ALL SELECT 'LCK_M_RS_U'                       , 'LOCK - Waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key'
UNION ALL SELECT 'LCK_M_RX_S'                       , 'LOCK - Waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key'
UNION ALL SELECT 'LCK_M_RX_U'                       , 'LOCK - Waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key'
UNION ALL SELECT 'LCK_M_RX_X'                       , 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key'
UNION ALL SELECT 'LCK_M_S'                          , 'LOCK - Waiting to acquire a Shared lock'
UNION ALL SELECT 'LCK_M_SCH_M'                      , 'LOCK - Waiting to acquire a Schema Modify lock'
UNION ALL SELECT 'LCK_M_SCH_S'                      , 'LOCK - Waiting to acquire a Schema Modify lock'
UNION ALL SELECT 'LCK_M_SIU'                        , 'LOCK - Waiting to acquire a Shared With Intent Update lock'
UNION ALL SELECT 'LCK_M_SIX'                        , 'LOCK - Waiting to acquire a Shared With Intent Exclusive lock'
UNION ALL SELECT 'LCK_M_U'                          , 'LOCK - Waiting to acquire an Update lock'
UNION ALL SELECT 'LCK_M_UIX'                        , 'LOCK - Waiting to acquire an Update With Intent Exclusive lock'
UNION ALL SELECT 'LCK_M_X'                          , 'LOCK - Waiting to acquire an Exclusive lock'
UNION ALL SELECT 'LATCH_DT'                         , 'LOCK - Waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches'
UNION ALL SELECT 'LATCH_EX'                         , 'LOCK - Waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches'
UNION ALL SELECT 'LATCH_KP'                         , 'LOCK - Waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches'
UNION ALL SELECT 'LATCH_SH'                         , 'LOCK - Waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches'
UNION ALL SELECT 'LATCH_UP'                         , 'LOCK - Waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches'
UNION ALL SELECT 'RESOURCE_SEMAPHORE'               , 'MEMORY - Query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts'
UNION ALL SELECT 'RESOURCE_SEMAPHORE_MUTEX'         , 'MEMORY - Query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests'
UNION ALL SELECT 'RESOURCE_SEMAPHORE_QUERY_COMPILE' , 'MEMORY - Number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans'
UNION ALL SELECT 'RESOURCE_SEMAPHORE_SMALL_QUERY'   , 'MEMORY - Memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries'
UNION ALL SELECT 'WRITELOG'                         , 'MEMORY - Waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits'
UNION ALL SELECT 'PAGEIOLATCH_DT'                   , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem'
UNION ALL SELECT 'PAGEIOLATCH_EX'                   , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem'
UNION ALL SELECT 'PAGEIOLATCH_KP'                   , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem'
UNION ALL SELECT 'PAGEIOLATCH_SH'                   , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Share mode. Long waits may indicate problems with the disk subsystem'
UNION ALL SELECT 'PAGEIOLATCH_UP'                   , 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem'
UNION ALL SELECT 'PAGELATCH_DT'                     , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode'
UNION ALL SELECT 'PAGELATCH_EX'                     , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode'
UNION ALL SELECT 'PAGELATCH_KP'                     , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode'
UNION ALL SELECT 'PAGELATCH_SH'                     , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode'
UNION ALL SELECT 'PAGELATCH_UP'                     , 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode'
UNION ALL SELECT 'LOGBUFFER'                        , 'DISK - Waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server'
UNION ALL SELECT 'ASYNC_IO_COMPLETION'              , 'DISK - Waiting for I/Os to finish'
UNION ALL SELECT 'IO_COMPLETION'                    , 'DISK - Waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits'


SELECT TOP 10
	'OS WAIT STATS - WAIT TYPE' = ws.wait_type
	,'TASKS WAITING COUNT'      = ws.waiting_tasks_count
	,'TIME WAITING (MS)'        = ws.wait_time_ms
	,'MAX TIME WAITING (MS)'    = ws.max_wait_time_ms
	,'SIGNAL TIME WAITING (MS)' = ws.signal_wait_time_ms
	,'POSSIBLE ISSUES'          = wt.comment
FROM
	#wait_stats ws
	INNER JOIN
	#wait_type wt ON (ws.wait_type = wt.wait_type)
ORDER BY
	ws.wait_time_ms DESC


DROP TABLE #wait_stats
DROP TABLE #wait_type