Information About Waits Stats in SQL Server HTTP, I/O and Kernel Waits

SQLServerF1

SQL Server internally uses worker threads to execute SQL queries sent by various applications. While threads are executing they may be either running on CPU processing the request or would be waiting on a certain resource or waiting in waiting for its chance to run on CPU. SQL Server assigns a certain wait type to the worker thread that is waiting.

If a query is taking long time to complete, we can look at the query sessions wait type to get an understanding of what kind of resource it is the query waiting for and take appropriate action to avoid the queries to wait, thus making the queries complete fast.

Below are some of the SQL Server Wait Types related to HTTP related waits, I/O related waits and SQL Server Kernel related waits.

HTTP_ENUMERATION
Occurs at startup to enumerate the HTTP endpoints to start HTTP.

HTTP_START
Occurs when a connection is waiting for HTTP to complete initialization.

IMPPROV_IOWAIT
Occurs when SQL Server waits for a bulkload I/O to finish.

INTERNAL_TESTING
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

IO_AUDIT_MUTEX
Occurs during synchronization of trace event buffers.

IO_COMPLETION
Occurs while 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.

IO_RETRY
Occurs when an I/O operation such as a read or a write to disk fails because of insufficient resources, and is then retried.

IOAFF_RANGE_QUEUE
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

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.

KTM_ENLISTMENT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

KTM_RECOVERY_MANAGER
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

KTM_RECOVERY_RESOLUTION
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *