![\"\"](\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql_server_wait_types_in_DPA_494x150.jpg\")
<\/p>\nHi,<\/p>\n
You should find that which of wait events are in the SQL Server database.<\/p>\n
<\/p>\n
<\/p>\n\n
<\/p>\n
To solve slowness of SQL Server database, you should find which wait events exists in the database.<\/p>\n
You can find wait events of database with following query.<\/p>\n
<\/p>\n
with waits as\r\n(select\r\nwait_type,\r\nwait_time_ms \/ 1000.0 as waits,\r\n(wait_time_ms - signal_wait_time_ms) \/ 1000.0 as resources,\r\nsignal_wait_time_ms \/ 1000.0 as signals,\r\nwaiting_tasks_count as waitcount,\r\n100.0 * wait_time_ms \/ sum (wait_time_ms) over() as percentage,\r\nrow_number() over(order by wait_time_ms desc) as rownum\r\nfrom sys.dm_os_wait_stats\r\nwhere wait_type not in (\r\nN'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',\r\nN'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',\r\nN'SLEEP_TASK', N'SLEEP_SYSTEMTASK',\r\nN'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',\r\nN'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',\r\nN'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',\r\nN'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',\r\nN'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',\r\nN'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',\r\nN'TRACEWRITE', N'XE_DISPATCHER_WAIT',\r\nN'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',\r\nN'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',\r\nN'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')\r\n)\r\nselect\r\nw1.wait_type as waittype, \r\ncast (w1.waits as decimal(14, 2)) wait_s,\r\ncast (w1.resources as decimal(14, 2)) resource_s,\r\ncast (w1.signals as decimal(14, 2)) signal_s,\r\nw1.waitcount wait_count,\r\ncast (w1.percentage as decimal(4, 2)) percentage,\r\ncast ((w1.waits \/ w1.waitcount) as decimal (14, 4)) avgWait_s,\r\ncast ((w1.resources \/ w1.waitcount) as decimal (14, 4)) avgResource_s,\r\ncast ((w1.signals \/ w1.waitcount) as decimal (14, 4)) avgSignal_s\r\nfrom waits as w1\r\ninner join waits as w2 on w2.rownum <= w1.rownum\r\ngroup by w1.rownum, w1.wait_type, w1.waits, w1.resources, w1.signals, w1.waitcount, w1.percentage\r\nhaving sum (w2.percentage) - w1.percentage < 95; -- percentage threshold<\/pre>\n<\/p>\n
<\/p>\n
Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n