Wait types in SQL Server 2016

Question

Hi

What are these waitypes? i am getting on one of my DEV SQL 2016 server.

Answer 1

Hi @Vijay Kumar ,

Please do not worry. These wait types are benign and can be ignored.

How to clear all these wait types?

You can reset them with the following command:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);    
GO    

Please refer to this article which might help.
Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

Here is a link for one of them:
https://www.sqlskills.com/help/waits/ksource_wakeup/

And that link should give you an idea how you find information about the other wait types as well.

Answer 3

Hi @Vijay Kumar ,

• WAIT_XTP_HOST_WAIT Occurs when waits are triggered by the database engine and implemented by the host.
• PVS_PREALLOCATE The PVS_PREALLOCATOR process starts up along with the SQL Server service and has a wait type of PVS_PREALLOCATE until it wakes up and has something to do. Please refer to this article for more details.
• FT_IFTSHC_MUTEX Full-text is waiting on communication operation.
• HADR_NOTIFICATION_DEQUEUE A background task that processes Windows Server Failover Clustering notifications is waiting for the next notification.
• BROKER_TRANSMITTER Occurs when the Service Broker transmitter is waiting for work. Service Broker has a component known as the Transmitter which schedules messages from multiple dialogs to be sent across the wire over one or more connection endpoints. The transmitter has 2 dedicated threads for this purpose. This wait type is charged when these transmitter threads are waiting for dialog messages to be sent using the transport connections. High values of waiting_tasks_count for this wait type point to intermittent work for these transmitter threads and are not indications of any performance problem. If service broker is not used at all, waiting_tasks_count should be 2 (for the 2 transmitter threads) and wait_time_ms should be twice the duration since instance startup.
• KSOURCE_WAKEUP Used by the service control task while waiting for requests from the Service Control Manager. Long waits are expected and do not indicate a problem.
• ONDEMAND_TASK_QUEUE Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern.
• BROKER_EVENTHANDLER Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.
  For more information, please refer to the official documentation.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet