Wait Stats capture script
Performance troubleshooting should begin with capturing wait stats so we can understand where SQL Server is busy. The script below captures wait stats into a table in tempdb; the script should be ran via a SQL Server Agent job or some other scheduling mechanism.
The dbo.WaitDefs table contains a listing of all documented wait stats – for comprehensive information regarding specific wait types, check Paul Randal’s wait-types library.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 |
/* Get a snapshot of the waiting task types. Typically, this would be ran on a schedule allowing analysis of wait types over time. by: Hannah Vernon date: 2018-08-13 */ USE tempdb; SET NOCOUNT ON; DECLARE @BatchReqSec int; DECLARE @RunNum int; DECLARE @Reset bit = 0; IF @Reset = 1 BEGIN DROP TABLE dbo.ws; DROP TABLE dbo.ws_header; DROP TABLE dbo.WaitDefs; DROP SEQUENCE dbo.ws_sequence; END IF OBJECT_ID(N'dbo.ws_sequence', N'SO') IS NULL BEGIN CREATE SEQUENCE dbo.ws_sequence AS int START WITH 1 INCREMENT BY 1 NO CYCLE NO CACHE; END IF OBJECT_ID(N'dbo.ws_header', N'U') IS NULL BEGIN CREATE TABLE dbo.ws_header ( RunNum int NOT NULL , datestamp datetime NOT NULL CONSTRAINT ws_header_df DEFAULT (GETDATE()) , batch_requests_per_sec bigint NOT NULL ); END IF OBJECT_ID(N'dbo.ws', N'U') IS NULL BEGIN CREATE TABLE dbo.ws ( RunNum int NOT NULL , wait_type_id int 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 , CONSTRAINT ws_pk PRIMARY KEY CLUSTERED (RunNum, wait_type_id) ); END IF OBJECT_ID(N'dbo.WaitDefs', N'U') IS NULL BEGIN CREATE TABLE dbo.WaitDefs ( wait_type_id int NOT NULL CONSTRAINT WaitDefs_pk PRIMARY KEY CLUSTERED IDENTITY(1,1) , WaitType sysname , WaitDesc varchar(640) , SQLSkillsURL AS N'https://www.sqlskills.com/help/waits/' + LOWER(WaitType) ); INSERT INTO dbo.WaitDefs (WaitType, WaitDesc) VALUES ('ABR','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('ASSEMBLY_LOAD','Occurs during exclusive access to assembly loading.') , ('ASYNC_DISKPOOL_LOCK','Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.') , ('ASYNC_IO_COMPLETION','Occurs when a task is waiting for I/Os to finish.') , ('ASYNC_NETWORK_IO','Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.') , ('AUDIT_GROUPCACHE_LOCK','Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit each audit action group.') , ('AUDIT_LOGINCACHE_LOCK','Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit login audit action groups.') , ('AUDIT_ON_DEMAND_TARGET_LOCK','Occurs when there is a wait on a lock that is used to ensure single initialization of audit related Extended Event targets.') , ('AUDIT_XE_SESSION_MGR','Occurs when there is a wait on a lock that is used to synchronize the starting and stopping of audit related Extended Events sessions.') , ('BACKUP','Occurs when a task is blocked as part of backup processing.') , ('BACKUP_OPERATOR','Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive.') , ('BACKUPBUFFER','Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.') , ('BACKUPIO','Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.') , ('BACKUPTHREAD','Occurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.') , ('BAD_PAGE_PROCESS','Occurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently.') , ('BROKER_CONNECTION_RECEIVE_TASK','Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.') , ('BROKER_ENDPOINT_STATE_MUTEX','Occurs when there is contention to access the state of a Service Broker connection endpoint. Access to the state for changes is serialized.') , ('BROKER_EVENTHANDLER','Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.') , ('BROKER_INIT','Occurs when initializing Service Broker in each active database. This should occur infrequently.') , ('BROKER_MASTERSTART','Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.') , ('BROKER_RECEIVE_WAITFOR','Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.') , ('BROKER_REGISTERALLENDPOINTS','Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.') , ('BROKER_SERVICE','Occurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized.') , ('BROKER_SHUTDOWN','Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.') , ('BROKER_TASK_STOP','Occurs when the Service Broker queue task handler tries to shut down the task. The state check is serialized and must be in a running state beforehand.') , ('BROKER_TO_FLUSH','Occurs when the Service Broker lazy flusher flushes the in-memory transmission objects to a work table.') , ('BROKER_TRANSMITTER','Occurs when the Service Broker transmitter is waiting for work.') , ('BUILTIN_HASHKEY_MUTEX','May occur after startup of instance, while internal data structures are initializing. Will not recur once data structures have initialized.') , ('CHECK_PRINT_RECORD','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('CHECKPOINT_QUEUE','Occurs while the checkpoint task is waiting for the next checkpoint request.') , ('CHKPT','Occurs at server startup to tell the checkpoint thread that it can start.') , ('CLEAR_DB','Occurs during operations that change the state of a database, such as opening or closing a database.') , ('CLR_AUTO_EVENT','Occurs when a task is currently performing common language runtime (CLR) execution and is waiting for a particular autoevent to be initiated. Long waits are typical, and do not indicate a problem.') , ('CLR_CRST','Occurs when a task is currently performing CLR execution and is waiting to enter a critical section of the task that is currently being used by another task.') , ('CLR_JOIN','Occurs when a task is currently performing CLR execution and waiting for another task to end. This wait state occurs when there is a join between tasks.') , ('CLR_MANUAL_EVENT','Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.') , ('CLR_MEMORY_SPY','Occurs during a wait on lock acquisition for a data structure that is used to record all virtual memory allocations that come from CLR. The data structure is locked to maintain its integrity if there is parallel access.') , ('CLR_MONITOR','Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.') , ('CLR_RWLOCK_READER','Occurs when a task is currently performing CLR execution and is waiting for a reader lock.') , ('CLR_RWLOCK_WRITER','Occurs when a task is currently performing CLR execution and is waiting for a writer lock.') , ('CLR_SEMAPHORE','Occurs when a task is currently performing CLR execution and is waiting for a semaphore.') , ('CLR_TASK_START','Occurs while waiting for a CLR task to complete startup.') , ('CLRHOST_STATE_ACCESS','Occurs where there is a wait to acquire exclusive access to the CLR-hosting data structures. This wait type occurs while setting up or tearing down the CLR runtime.') , ('CMEMTHREAD','Occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.') , ('CXPACKET','Occurs with parallel query plans when trying to synchronize the query processor exchange iterator. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism.') , ('CXROWSET_SYNC','Occurs during a parallel range scan.') , ('DAC_INIT','Occurs while the dedicated administrator connection is initializing.') , ('DBMIRROR_DBM_EVENT','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('DBMIRROR_DBM_MUTEX','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('DBMIRROR_EVENTS_QUEUE','Occurs when database mirroring waits for events to process.') , ('DBMIRROR_SEND','Occurs when a task is waiting for a communications backlog at the network layer to clear to be able to send messages. Indicates that the communications layer is starting to become overloaded and affect the database mirroring data throughput.') , ('DBMIRROR_WORKER_QUEUE','Indicates that the database mirroring worker task is waiting for more work.') , ('DBMIRRORING_CMD','Occurs when a task is waiting for log records to be flushed to disk. This wait state is expected to be held for long periods of time.') , ('DEADLOCK_ENUM_MUTEX','Occurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time.') , ('DEADLOCK_TASK_SEARCH','Large waiting time on this resource indicates that the server is executing queries on top of sys.dm_os_waiting_tasks, and these queries are blocking deadlock monitor from running deadlock search. This wait type is used by deadlock monitor only. Queries on top of sys.dm_os_waiting_tasks use DEADLOCK_ENUM_MUTEX.') , ('DEBUG','Occurs during Transact-SQL and CLR debugging for internal synchronization.') , ('DISABLE_VERSIONING','Occurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement.') , ('DISKIO_SUSPEND','Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.') , ('DISPATCHER_QUEUE_SEMAPHORE','Occurs when a thread from the dispatcher pool is waiting for more work to process. The wait time for this wait type is expected to increase when the dispatcher is idle.') , ('DLL_LOADING_MUTEX','Occurs once while waiting for the XML parser DLL to load.') , ('DROPTEMP','Occurs between attempts to drop a temporary object if the previous attempt failed. The wait duration grows exponentially with each failed drop attempt.') , ('DTC','Occurs when a task is waiting on an event that is used to manage state transition. This state controls when the recovery of Microsoft Distributed Transaction Coordinator (MS DTC) transactions occurs after SQL Server receives notification that the MS DTC service has become unavailable. This state also describes a task that is waiting when a commit of a MS DTC transaction is initiated by SQL Server and SQL Server is waiting for the MS DTC commit to finish.') , ('DTC_ABORT_REQUEST','Occurs in a MS DTC worker session when the session is waiting to take ownership of a MS DTC transaction. After MS DTC owns the transaction, the session can roll back the transaction. Generally, the session will wait for another session that is using the transaction.') , ('DTC_RESOLVE','Occurs when a recovery task is waiting for the master database in a cross-database transaction so that the task can query the outcome of the transaction.') , ('DTC_STATE','Occurs when a task is waiting on an event that protects changes to the internal MS DTC global state object. This state should be held for very short periods of time.') , ('DTC_TMDOWN_REQUEST','Occurs in a MS DTC worker session when SQL Server receives notification that the MS DTC service is not available. First, the worker will wait for the MS DTC recovery process to start. Then, the worker waits to obtain the outcome of the distributed transaction that the worker is working on. This may continue until the connection with the MS DTC service has been reestablished.') , ('DTC_WAITFOR_OUTCOME','Occurs when recovery tasks wait for MS DTC to become active to enable the resolution of prepared transactions.') , ('DUMP_LOG_COORDINATOR','Occurs when a main task is waiting for a subtask to generate data. Ordinarily, this state does not occur. A long wait indicates an unexpected blockage. The subtask should be investigated.') , ('DUMPTRIGGER','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('EC','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('EE_PMOLOCK','Occurs during synchronization of certain types of memory allocations during statement execution.') , ('EE_SPECPROC_MAP_INIT','Occurs during synchronization of internal procedure hash table creation. This wait can only occur during the initial accessing of the hash table after the SQL Server instance starts.') , ('ENABLE_VERSIONING','Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.') , ('ERROR_REPORTING_MANAGER','Occurs during synchronization of multiple concurrent error log initializations.') , ('EXCHANGE','Occurs during synchronization in the query processor exchange iterator during parallel queries.') , ('EXECSYNC','Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such areas are bitmaps, large binary objects (LOBs), and the spool iterator. LOBs may frequently use this wait state.') , ('EXECUTION_PIPE_EVENT_INTERNAL','Occurs during synchronization between producer and consumer parts of batch execution that are submitted through the connection context.') , ('FAILPOINT','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('FCB_REPLICA_READ','Occurs when the reads of a snapshot (or a temporary snapshot created by DBCC) sparse file are synchronized.') , ('FCB_REPLICA_WRITE','Occurs when the pushing or pulling of a page to a snapshot (or a temporary snapshot created by DBCC) sparse file is synchronized.') , ('FS_FC_RWLOCK','Occurs when there is a wait by the FILESTREAM garbage collector to do either of the following: 1) Disable garbage collection (used by backup and restore). 2) Execute one cycle of the FILESTREAM garbage collector.') , ('FS_GARBAGE_COLLECTOR_SHUTDOWN','Occurs when the FILESTREAM garbage collector is waiting for cleanup tasks to be completed.') , ('FS_HEADER_RWLOCK','Occurs when there is a wait to acquire access to the FILESTREAM header of a FILESTREAM data container to either read or update contents in the FILESTREAM header file (Filestream.hdr).') , ('FS_LOGTRUNC_RWLOCK','Occurs when there is a wait to acquire access to FILESTREAM log truncation to do either of the following: 1) Temporarily disable FILESTREAM log (FSLOG) truncation (used by backup and restore). 2) Execute one cycle of FSLOG truncation.') , ('FSA_FORCE_OWN_XACT','Occurs when a FILESTREAM file I/O operation needs to bind to the associated transaction, but the transaction is currently owned by another session.') , ('FSAGENT','Occurs when a FILESTREAM file I/O operation is waiting for a FILESTREAM agent resource that is being used by another file I/O operation.') , ('FSTR_CONFIG_MUTEX','Occurs when there is a wait for another FILESTREAM feature reconfiguration to be completed.') , ('FSTR_CONFIG_RWLOCK','Occurs when there is a wait to serialize access to the FILESTREAM configuration parameters.') , ('FT_COMPROWSET_RWLOCK','Full-text is waiting on fragment metadata operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('FT_IFTS_RWLOCK','Full-text is waiting on internal synchronization. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('FT_IFTS_SCHEDULER_IDLE_WAIT','Full-text scheduler sleep wait type. The scheduler is idle.') , ('FT_IFTSHC_MUTEX','Full-text is waiting on an fdhost control operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('FT_IFTSISM_MUTEX','Full-text is waiting on communication operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('FT_MASTER_MERGE','Full-text is waiting on master merge operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('FT_METADATA_MUTEX','Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('FT_RESTART_CRAWL','Occurs when a full-text crawl needs to restart from a last known good point to recover from a transient failure. The wait lets the worker tasks currently working on that population to complete or exit the current step.') , ('FULLTEXT GATHERER','Occurs during synchronization of full-text operations.') , ('GUARDIAN','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('HADR_AG_MUTEX','Occurs when an AlwaysOn DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the configuration of an availability group.') , ('HADR_AR_CRITICAL_SECTION_ENTRY','Occurs when an AlwaysOn DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the runtime state of the local replica of the associated availability group.') , ('HADR_AR_MANAGER_MUTEX','Occurs when an availability replica shutdown is waiting for startup to complete or an availability replica startup is waiting for shutdown to complete. Internal use only. Availability replica shutdown is initiated either by SQL Server shutdown or by SQL Server handling the loss of quorum by the Windows Server Failover Clustering node. Availability replica startup is initiated either by SQL Server startup or by SQL Server recovering from the loss of quorum by the Windows Server Failover Clustering node.') , ('HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST','The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers. Internal use only.') , ('HADR_BACKUP_BULK_LOCK','The AlwaysOn primary database received a backup request from a secondary database and is waiting for the background thread to finish processing the request on acquiring or releasing the BulkOp lock.') , ('HADR_BACKUP_QUEUE','The backup background thread of the AlwaysOn primary database is waiting for a new work request from the secondary database. (typically, this occurs when the primary database is holding the BulkOp log and is waiting for the secondary database to indicate that the primary database can release the lock).') , ('HADR_CLUSAPI_CALL','A SQL Server thread is waiting to switch from non-preemptive mode (scheduled by SQL Server) to preemptive mode (scheduled by the operating system) in order to invoke Windows Server Failover Clustering APIs.') , ('HADR_COMPRESSED_CACHE_SYNC','Waiting for access to the cache of compressed log blocks that is used to avoid redundant compression of the log blocks sent to multiple secondary databases.') , ('HADR_DATABASE_FLOW_CONTROL','Waiting for messages to be sent to the partner when the maximum number of queued messages has been reached. Indicates that the log scans are running faster than the network sends. This is an issue only if network sends are slower than expected.') , ('HADR_DATABASE_VERSIONING_STATE','Occurs on the versioning state change of an AlwaysOn secondary database. This wait is for internal data structures and is usually is very short with no direct effect on data access.') , ('HADR_DATABASE_WAIT_FOR_RESTART','Waiting for the database to restart under AlwaysOn Availability Groups control. Under normal conditions, this is not a customer issue because waits are expected here.') , ('HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING','A query on object(s) in a readable secondary database of an AlwaysOn availability group is blocked on row versioning while waiting for commit or rollback of all transactions that were in-flight when the secondary replica was enabled for read workloads. This wait type guarantees that row versions are available before execution of a query under snapshot isolation.') , ('HADR_DB_COMMAND','Waiting for responses to conversational messages (which require an explicit response from the other side, using the AlwaysOn conversational message infrastructure). A number of different message types use this wait type.') , ('HADR_DB_OP_COMPLETION_SYNC','Waiting for responses to conversational messages (which require an explicit response from the other side, using the AlwaysOn conversational message infrastructure). A number of different message types use this wait type.') , ('HADR_DB_OP_START_SYNC','An AlwaysOn DDL statement or a Windows Server Failover Clustering command is waiting for serialized access to an availability database and its runtime state.') , ('HADR_DBR_SUBSCRIBER','The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the runtime state of an event subscriber that corresponds to an availability database. Internal use only.') , ('HADR_DBR_SUBSCRIBER_FILTER_LIST','The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers that correspond to availability databases. Internal use only.') , ('HADR_DBSTATECHANGE_SYNC','Concurrency control wait for updating the internal state of the database replica.') , ('HADR_FILESTREAM_BLOCK_FLUSH','The FILESTREAM AlwaysOn transport manager is waiting until processing of a log block is finished.') , ('HADR_FILESTREAM_FILE_CLOSE','The FILESTREAM AlwaysOn transport manager is waiting until the next FILESTREAM file gets processed and its handle gets closed.') , ('HADR_FILESTREAM_FILE_REQUEST','An AlwaysOn secondary replica is waiting for the primary replica to send all requested FILESTREAM files during UNDO.') , ('HADR_FILESTREAM_IOMGR','The FILESTREAM AlwaysOn transport manager is waiting for R/W lock that protects the FILESTREAM AlwaysOn I/O manager during startup or shutdown.') , ('HADR_FILESTREAM_IOMGR_IOCOMPLETION','The FILESTREAM AlwaysOn I/O manager is waiting for I/O completion.') , ('HADR_FILESTREAM_MANAGER','The FILESTREAM AlwaysOn transport manager is waiting for the R/W lock that protects the FILESTREAM AlwaysOn transport manager during startup or shutdown.') , ('HADR_GROUP_COMMIT','Transaction commit processing is waiting to allow a group commit so that multiple commit log records can be put into a single log block. This wait is an expected condition that optimizes the log I/O, capture, and send operations.') , ('HADR_LOGCAPTURE_SYNC','Concurrency control around the log capture or apply object when creating or destroying scans. This is an expected wait when partners change state or connection status.') , ('HADR_LOGCAPTURE_WAIT','Waiting for log records to become available. Can occur either when waiting for new log records to be generated by connections or for I/O completion when reading log not in the cache. This is an expected wait if the log scan is caught up to the end of log or is reading from disk.') , ('HADR_LOGPROGRESS_SYNC','Concurrency control wait when updating the log progress status of database replicas.') , ('HADR_NOTIFICATION_DEQUEUE','A background task that processes Windows Server Failover Clustering notifications is waiting for the next notification. Internal use only.') , ('HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS','The AlwaysOn availability replica manager is waiting for serialized access to the runtime state of a background task that processes Windows Server Failover Clustering notifications. Internal use only.') , ('HADR_NOTIFICATION_WORKER_STARTUP_SYNC','A background task is waiting for the completion of the startup of a background task that processes Windows Server Failover Clustering notifications. Internal use only.') , ('HADR_NOTIFICATION_WORKER_TERMINATION_SYNC','A background task is waiting for the termination of a background task that processes Windows Server Failover Clustering notifications. Internal use only.') , ('HADR_PARTNER_SYNC','Concurrency control wait on the partner list.') , ('HADR_READ_ALL_NETWORKS','Waiting to get read or write access to the list of WSFC networks. Internal use only. The engine keeps a list of WSFC networks that is used in dynamic management views (such as sys.dm_hadr_cluster_networks) or to validate AlwaysOn Transact-SQL statements that reference WSFC network information. This list is updated upon engine startup, WSFC related notifications, and internal AlwaysOn restart (for example, losing and regaining of WSFC quorum). Tasks will usually be blocked when an update in that list is in progress.') , ('HADR_RECOVERY_WAIT_FOR_CONNECTION','Waiting for the secondary database to connect to the primary database before running recovery. This is an expected wait, which can lengthen if the connection to the primary is slow to establish.') , ('HADR_RECOVERY_WAIT_FOR_UNDO','Database recovery is waiting for the secondary database to finish the reverting and initializing phase to bring it back to the common log point with the primary database. This is an expected wait after failovers.Undo progress can be tracked through the Windows System Monitor (perfmon.exe) and dynamic management views.') , ('HADR_REPLICAINFO_SYNC','Waiting for concurrency control to update the current replica state.') , ('HADR_SYNC_COMMIT','Waiting for transaction commit processing for the synchronized secondary databases to harden the log. This wait is also reflected by the Transaction Delay performance counter. This wait type is expected for synchronized availability groups and indicates the time to send, write, and acknowledge log to the secondary databases.') , ('HADR_SYNCHRONIZING_THROTTLE','Waiting for transaction commit processing to allow a synchronizing secondary database to catch up to the primary end of log in order to transition to the synchronized state. This is an expected wait when a secondary database is catching up.') , ('HADR_TDS_LISTENER_SYNC','Either the internal AlwaysOn system or the WSFC cluster will request that listeners are started or stopped. The processing of this request is always asynchronous, and there is a mechanism to remove redundant requests. There are also moments that this process is suspended because of configuration changes. All waits related with this listener synchronization mechanism use this wait type. Internal use only.') , ('HADR_TDS_LISTENER_SYNC_PROCESSING','Used at the end of an AlwaysOn Transact-SQL statement that requires starting and/or stopping an availability group listener. Since the start/stop operation is done asynchronously, the user thread will block using this wait type until the situation of the listener is known.') , ('HADR_TIMER_TASK','Waiting to get the lock on the timer task object and is also used for the actual waits between times that work is being performed. For example, for a task that runs every 10 seconds, after one execution, AlwaysOn Availability Groups waits about 10 seconds to reschedule the task, and the wait is included here.') , ('HADR_TRANSPORT_DBRLIST','Waiting for access to the transport layers database replica list. Used for the spinlock that grants access to it.') , ('HADR_TRANSPORT_FLOW_CONTROL','Waiting when the number of outstanding unacknowledged AlwaysOn messages is over the out flow control threshold. This is on an availability replica-to-replica basis (not on a database-to-database basis).') , ('HADR_TRANSPORT_SESSION','AlwaysOn Availability Groups is waiting while changing or accessing the underlying transport state.') , ('HADR_WORK_POOL','Concurrency control wait on the AlwaysOn Availability Groups background work task object.') , ('HADR_WORK_QUEUE','AlwaysOn Availability Groups background worker thread waiting for new work to be assigned. This is an expected wait when there are ready workers waiting for new work, which is the normal state.') , ('HADR_XRF_STACK_ACCESS','Accessing (look up, add, and delete) the extended recovery fork stack for an AlwaysOn availability database.') , ('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.') , ('LATCH_DT','Occurs when waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LATCH_EX','Occurs when waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LATCH_KP','Occurs when waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LATCH_NL','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('LATCH_SH','Occurs when waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LATCH_UP','Occurs when waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LAZYWRITER_SLEEP','Occurs when lazywriter tasks are suspended. This is a measure of the time spent by background tasks that are waiting. Do not consider this state when you are looking for user stalls.') , ('LCK_M_BU','Occurs when a task is waiting to acquire a Bulk Update (BU) lock.') , ('LCK_M_BU_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_BU_LOW_PRIORITY','Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_IS','Occurs when a task is waiting to acquire an Intent Shared (IS) lock.') , ('LCK_M_IS_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_IS_LOW_PRIORITY','Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_IU','Occurs when a task is waiting to acquire an Intent Update (IU) lock.') , ('LCK_M_IU_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Intent Update (IU) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_IU_LOW_PRIORITY','Occurs when a task is waiting to acquire an Intent Update (IU) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_IX','Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.') , ('LCK_M_IX_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_IX_LOW_PRIORITY','Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RIn_NL','Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock.') , ('LCK_M_RIn_NL_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a NULL lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RIn_NL_LOW_PRIORITY','Occurs when a task is waiting to acquire a NULL lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RIn_S','Occurs when a task is waiting to acquire a shared lock on the current key value, and an Insert Range lock between the current and previous key.') , ('LCK_M_RIn_S_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a shared lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RIn_S_LOW_PRIORITY','Occurs when a task is waiting to acquire a shared lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RIn_U','Task is waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key.') , ('LCK_M_RIn_U_ABORT_BLOCKERS','Task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RIn_U_LOW_PRIORITY','Task is waiting to acquire an Update lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RIn_X','Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key.') , ('LCK_M_RIn_X_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RIn_X_LOW_PRIORITY','Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RS_S','Occurs when a task is waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous key.') , ('LCK_M_RS_S_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and a Shared Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RS_S_LOW_PRIORITY','Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and a Shared Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RS_U','Occurs when a task is waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key.') , ('LCK_M_RS_U_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Update Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RS_U_LOW_PRIORITY','Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Update Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RX_S','Occurs when a task is waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key.') , ('LCK_M_RX_S_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and an Exclusive Range with Abort Blockers lock between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RX_S_LOW_PRIORITY','Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and an Exclusive Range with Low Priority lock between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RX_U','Occurs when a task is waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key.') , ('LCK_M_RX_U_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Exclusive range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RX_U_LOW_PRIORITY','Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Exclusive range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RX_X','Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key.') , ('LCK_M_RX_X_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Exclusive Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_RX_X_LOW_PRIORITY','Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Exclusive Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_S','Occurs when a task is waiting to acquire a Shared lock.') , ('LCK_M_S_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a Shared lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_S_LOW_PRIORITY','Occurs when a task is waiting to acquire a Shared lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_SCH_M','Occurs when a task is waiting to acquire a Schema Modify lock.') , ('LCK_M_SCH_M_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a Schema Modify lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_SCH_M_LOW_PRIORITY','Occurs when a task is waiting to acquire a Schema Modify lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_SCH_S','Occurs when a task is waiting to acquire a Schema Share lock.') , ('LCK_M_SCH_S_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a Schema Share lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_SCH_S_LOW_PRIORITY','Occurs when a task is waiting to acquire a Schema Share lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_SIU','Occurs when a task is waiting to acquire a Shared With Intent Update lock.') , ('LCK_M_SIU_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a Shared With Intent Update lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_SIU_LOW_PRIORITY','Occurs when a task is waiting to acquire a Shared With Intent Update lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_SIX','Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.') , ('LCK_M_SIX_ABORT_BLOCKERS','Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_SIX_LOW_PRIORITY','Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_U','Occurs when a task is waiting to acquire an Update lock.') , ('LCK_M_U_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Update lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_U_LOW_PRIORITY','Occurs when a task is waiting to acquire an Update lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_UIX','Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.') , ('LCK_M_UIX_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_UIX_LOW_PRIORITY','Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_X','Occurs when a task is waiting to acquire an Exclusive lock.') , ('LCK_M_X_ABORT_BLOCKERS','Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LCK_M_X_LOW_PRIORITY','Occurs when a task is waiting to acquire an Exclusive lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)') , ('LOGBUFFER','Occurs when a task is 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.') , ('LOGGENERATION','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('LOGMGR','Occurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database.') , ('LOGMGR_FLUSH','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('LOGMGR_QUEUE','Occurs while the log writer task waits for work requests.') , ('LOGMGR_RESERVE_APPEND','Occurs when a task is waiting to see whether log truncation frees up log space to enable the task to write a new log record. Consider increasing the size of the log file(s) for the affected database to reduce this wait.') , ('LOWFAIL_MEMMGR_QUEUE','Occurs while waiting for memory to be available for use.') , ('MISCELLANEOUS','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('MSQL_DQ','Occurs when a task is waiting for a distributed query operation to finish. This is used to detect potential Multiple Active Result Set (MARS) application deadlocks. The wait ends when the distributed query call finishes.') , ('MSQL_XACT_MGR_MUTEX','Occurs when a task is waiting to obtain ownership of the session transaction manager to perform a session level transaction operation.') , ('MSQL_XACT_MUTEX','Occurs during synchronization of transaction usage. A request must acquire the mutex before it can use the transaction.') , ('MSQL_XP','Occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends.') , ('MSSEARCH','Occurs during Full-Text Search calls. This wait ends when the full-text operation completes. It does not indicate contention, but rather the duration of full-text operations.') , ('NET_WAITFOR_PACKET','Occurs when a connection is waiting for a network packet during a network read.') , ('OLEDB','Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.') , ('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.') , ('PAGEIOLATCH_DT','Occurs when a task is 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.') , ('PAGEIOLATCH_EX','Occurs when a task is 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.') , ('PAGEIOLATCH_KP','Occurs when a task is 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.') , ('PAGEIOLATCH_NL','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('PAGEIOLATCH_SH','Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.') , ('PAGEIOLATCH_UP','Occurs when a task is 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.') , ('PAGELATCH_DT','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode.') , ('PAGELATCH_EX','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.') , ('PAGELATCH_KP','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode.') , ('PAGELATCH_NL','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('PAGELATCH_SH','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.') , ('PAGELATCH_UP','Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode.') , ('PARALLEL_BACKUP_QUEUE','Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.') , ('PREEMPTIVE_ABR','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('PREEMPTIVE_AUDIT_ACCESS_EVENTLOG','Occurs when the SQL Server Operating System (SQLOS) scheduler switches to preemptive mode to write an audit event to the Windows event log.') , ('PREEMPTIVE_AUDIT_ACCESS_SECLOG','Occurs when the SQLOS scheduler switches to preemptive mode to write an audit event to the Windows Security log.') , ('PREEMPTIVE_CLOSEBACKUPMEDIA','Occurs when the SQLOS scheduler switches to preemptive mode to close backup media.') , ('PREEMPTIVE_CLOSEBACKUPTAPE','Occurs when the SQLOS scheduler switches to preemptive mode to close a tape backup device.') , ('PREEMPTIVE_CLOSEBACKUPVDIDEVICE','Occurs when the SQLOS scheduler switches to preemptive mode to close a virtual backup device.') , ('PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL','Occurs when the SQLOS scheduler switches to preemptive mode to perform Windows failover cluster operations.') , ('PREEMPTIVE_COM_COCREATEINSTANCE','Occurs when the SQLOS scheduler switches to preemptive mode to create a COM object.') , ('PREEMPTIVE_HADR_LEASE_MECHANISM','AlwaysOn Availability Groups lease manager scheduling for CSS diagnostics.') , ('PREEMPTIVE_SOSTESTING','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('PREEMPTIVE_STRESSDRIVER','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('PREEMPTIVE_TESTING','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('PREEMPTIVE_XETESTING','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('PRINT_ROLLBACK_PROGRESS','Used to wait while user processes are ended in a database that has been transitioned by using the ALTER DATABASE termination clause. For more information, see ALTER DATABASE (Transact-SQL).') , ('PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC','Occurs when a background task is waiting for the termination of the background task that receives (via polling) Windows Server Failover Clustering notifications.') , ('PWAIT_HADR_CLUSTER_INTEGRATION','An append, replace, and/or remove operation is waiting to grab a write lock on an AlwaysOn internal list (such as a list of networks, network addresses, or availability group listeners). Internal use only') , ('PWAIT_HADR_OFFLINE_COMPLETED','An AlwaysOn drop availability group operation is waiting for the target availability group to go offline before destroying Windows Server Failover Clustering objects.') , ('PWAIT_HADR_ONLINE_COMPLETED','An AlwaysOn create or failover availability group operation is waiting for the target availability group to come online.') , ('PWAIT_HADR_POST_ONLINE_COMPLETED','An AlwaysOn drop availability group operation is waiting for the termination of any background task that was scheduled as part of a previous command. For example, there may be a background task that is transitioning availability databases to the primary role. The DROP AVAILABILITY GROUP DDL must wait for this background task to terminate in order to avoid race conditions.') , ('PWAIT_HADR_WORKITEM_COMPLETED','Internal wait by a thread waiting for an async work task to complete. This is an expected wait and is for CSS use.') , ('PWAIT_MD_LOGIN_STATS','Occurs during internal synchronization in metadata on login stats.') , ('PWAIT_MD_RELATION_CACHE','Occurs during internal synchronization in metadata on table or index.') , ('PWAIT_MD_SERVER_CACHE','Occurs during internal synchronization in metadata on linked servers.') , ('PWAIT_MD_UPGRADE_CONFIG','Occurs during internal synchronization in upgrading server wide configurations.') , ('PWAIT_METADATA_LAZYCACHE_RWLOCk','Occurs during internal synchronization in metadata cache along with iterating index or stats in a table.') , ('QPJOB_KILL','Indicates that an asynchronous automatic statistics update was canceled by a call to KILL as the update was starting to run. The terminating thread is suspended, waiting for it to start listening for KILL commands. A good value is less than one second.') , ('QPJOB_WAITFOR_ABORT','Indicates that an asynchronous automatic statistics update was canceled by a call to KILL when it was running. The update has now completed but is suspended until the terminating thread message coordination is complete. This is an ordinary but rare state, and should be very short. A good value is less than one second.') , ('QRY_MEM_GRANT_INFO_MUTEX','Occurs when Query Execution memory management tries to control access to static grant information list. This state lists information about the current granted and waiting memory requests. This state is a simple access control state. There should never be a long wait on this state. If this mutex is not released, all new memory-using queries will stop responding.') , ('QUERY_ERRHDL_SERVICE_DONE','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN','Occurs in certain cases when offline create index build is run in parallel, and the different worker threads that are sorting synchronize access to the sort files.') , ('QUERY_NOTIFICATION_MGR_MUTEX','Occurs during synchronization of the garbage collection queue in the Query Notification Manager.') , ('QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX','Occurs during state synchronization for transactions in Query Notifications.') , ('QUERY_NOTIFICATION_TABLE_MGR_MUTEX','Occurs during internal synchronization within the Query Notification Manager.') , ('QUERY_NOTIFICATION_UNITTEST_MUTEX','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('QUERY_OPTIMIZER_PRINT_MUTEX','Occurs during synchronization of query optimizer diagnostic output production. This wait type only occurs if diagnostic settings have been enabled under direction of Microsoft Product Support.') , ('QUERY_TRACEOUT','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('QUERY_WAIT_ERRHDL_SERVICE','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('RECOVER_CHANGEDB','Occurs during synchronization of database status in warm standby database.') , ('REPL_CACHE_ACCESS','Occurs during synchronization on a replication article cache. During these waits, the replication log reader stalls, and data definition language (DDL) statements on a published table are blocked.') , ('REPL_SCHEMA_ACCESS','Occurs during synchronization of replication schema version information. This state exists when DDL statements are executed on the replicated object, and when the log reader builds or consumes versioned schema based on DDL occurrence. This wait type is also used by log reader agent to synchronize memory access. If a publisher has large number of very active published databases and log reader agents, contention can be seen on this wait type.') , ('REPLICA_WRITES','Occurs while a task waits for completion of page writes to database snapshots or DBCC replicas.') , ('REQUEST_DISPENSER_PAUSE','Occurs when a task is waiting for all outstanding I/O to complete, so that I/O to a file can be frozen for snapshot backup.') , ('REQUEST_FOR_DEADLOCK_SEARCH','Occurs while the deadlock monitor waits to start the next deadlock search. This wait is expected between deadlock detections, and lengthy total waiting time on this resource does not indicate a problem.') , ('RESMGR_THROTTLED','Occurs when a new request comes in and is throttled based on the GROUP_MAX_REQUESTS setting.') , ('RESOURCE_QUEUE','Occurs during synchronization of various internal resource queues.') , ('RESOURCE_SEMAPHORE','Occurs when a 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.') , ('RESOURCE_SEMAPHORE_MUTEX','Occurs while a query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests.') , ('RESOURCE_SEMAPHORE_QUERY_COMPILE','Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.') , ('RESOURCE_SEMAPHORE_SMALL_QUERY','Occurs when 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, because the server transfers the request to the main query memory pool if it fails to grant the requested memory within a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries.') , ('SEC_DROP_TEMP_KEY','Occurs after a failed attempt to drop a temporary security key before a retry attempt.') , ('SECURITY_MUTEX','Occurs when there is a wait for mutexes that control access to the global list of Extensible Key Management (EKM) cryptographic providers and the session-scoped list of EKM sessions.') , ('SEQUENTIAL_GUID','Occurs while a new sequential GUID is being obtained.') , ('SERVER_IDLE_CHECK','Occurs during synchronization of SQL Server instance idle status when a resource monitor is attempting to declare a SQL Server instance as idle or trying to wake up.') , ('SHUTDOWN','Occurs while a shutdown statement waits for active connections to exit.') , ('SLEEP_BPOOL_FLUSH','Occurs when a checkpoint is throttling the issuance of new I/Os in order to avoid flooding the disk subsystem.') , ('SLEEP_DBSTARTUP','Occurs during database startup while waiting for all databases to recover.') , ('SLEEP_DCOMSTARTUP','Occurs once at most during SQL Server instance startup while waiting for DCOM initialization to complete.') , ('SLEEP_MSDBSTARTUP','Occurs when SQL Trace waits for the msdb database to complete startup.') , ('SLEEP_SYSTEMTASK','Occurs during the start of a background task while waiting for tempdb to complete startup.') , ('SLEEP_TASK','Occurs when a task sleeps while waiting for a generic event to occur.') , ('SLEEP_TEMPDBSTARTUP','Occurs while a task waits for tempdb to complete startup.') , ('SNI_CRITICAL_SECTION','Occurs during internal synchronization within SQL Server networking components.') , ('SNI_HTTP_WAITFOR_0_DISCON','Occurs during SQL Server shutdown, while waiting for outstanding HTTP connections to exit.') , ('SNI_LISTENER_ACCESS','Occurs while waiting for non-uniform memory access (NUMA) nodes to update state change. Access to state change is serialized.') , ('SNI_TASK_COMPLETION','Occurs when there is a wait for all tasks to finish during a NUMA node state change.') , ('SOAP_READ','Occurs while waiting for an HTTP network read to complete.') , ('SOAP_WRITE','Occurs while waiting for an HTTP network write to complete.') , ('SOS_CALLBACK_REMOVAL','Occurs while performing synchronization on a callback list in order to remove a callback. It is not expected for this counter to change after server initialization is completed.') , ('SOS_DISPATCHER_MUTEX','Occurs during internal synchronization of the dispatcher pool. This includes when the pool is being adjusted.') , ('SOS_LOCALALLOCATORLIST','Occurs during internal synchronization in the SQL Server memory manager.') , ('SOS_MEMORY_USAGE_ADJUSTMENT','Occurs when memory usage is being adjusted among pools.') , ('SOS_OBJECT_STORE_DESTROY_MUTEX','Occurs during internal synchronization in memory pools when destroying objects from the pool.') , ('SOS_PHYS_PAGE_CACHE','Accounts for the time a thread waits to acquire the mutex it must acquire before it allocates physical pages or before it returns those pages to the operating system. Waits on this type only appear if the instance of SQL Server uses AWE memory.') , ('SOS_PROCESS_AFFINITY_MUTEX','Occurs during synchronizing of access to process affinity settings.') , ('SOS_RESERVEDMEMBLOCKLIST','Occurs during internal synchronization in the SQL Server memory manager.') , ('SOS_SCHEDULER_YIELD','Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.') , ('SOS_SMALL_PAGE_ALLOC','Occurs during the allocation and freeing of memory that is managed by some memory objects.') , ('SOS_STACKSTORE_INIT_MUTEX','Occurs during synchronization of internal store initialization.') , ('SOS_SYNC_TASK_ENQUEUE_EVENT','Occurs when a task is started in a synchronous manner. Most tasks in SQL Server are started in an asynchronous manner, in which control returns to the starter immediately after the task request has been placed on the work queue.') , ('SOS_VIRTUALMEMORY_LOW','Occurs when a memory allocation waits for a resource manager to free up virtual memory.') , ('SOSHOST_EVENT','Occurs when a hosted component, such as CLR, waits on a SQL Server event synchronization object.') , ('SOSHOST_INTERNAL','Occurs during synchronization of memory manager callbacks used by hosted components, such as CLR.') , ('SOSHOST_MUTEX','Occurs when a hosted component, such as CLR, waits on a SQL Server mutex synchronization object.') , ('SOSHOST_RWLOCK','Occurs when a hosted component, such as CLR, waits on a SQL Server reader-writer synchronization object.') , ('SOSHOST_SEMAPHORE','Occurs when a hosted component, such as CLR, waits on a SQL Server semaphore synchronization object.') , ('SOSHOST_SLEEP','Occurs when a hosted task sleeps while waiting for a generic event to occur. Hosted tasks are used by hosted components such as CLR.') , ('SOSHOST_TRACELOCK','Occurs during synchronization of access to trace streams.') , ('SOSHOST_WAITFORDONE','Occurs when a hosted component, such as CLR, waits for a task to complete.') , ('SQLCLR_APPDOMAIN','Occurs while CLR waits for an application domain to complete startup.') , ('SQLCLR_ASSEMBLY','Occurs while waiting for access to the loaded assembly list in the appdomain.') , ('SQLCLR_DEADLOCK_DETECTION','Occurs while CLR waits for deadlock detection to complete.') , ('SQLCLR_QUANTUM_PUNISHMENT','Occurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks.') , ('SQLSORT_NORMMUTEX','Occurs during internal synchronization, while initializing internal sorting structures.') , ('SQLSORT_SORTMUTEX','Occurs during internal synchronization, while initializing internal sorting structures.') , ('SQLTRACE_BUFFER_FLUSH','Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.') , ('SQLTRACE_FILE_BUFFER','Occurs during synchronization on trace buffers during a file trace.') , ('SQLTRACE_SHUTDOWN','Occurs while trace shutdown waits for outstanding trace events to complete.') , ('SQLTRACE_WAIT_ENTRIES','Occurs while a SQL Trace event queue waits for packets to arrive on the queue.') , ('SRVPROC_SHUTDOWN','Occurs while the shutdown process waits for internal resources to be released to shutdown cleanly.') , ('TEMPOBJ','Occurs when temporary object drops are synchronized. This wait is rare, and only occurs if a task has requested exclusive access for temp table drops.') , ('THREADPOOL','Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking unusually long, thus reducing the number of workers available to satisfy other batches.') , ('TIMEPRIV_TIMEPERIOD','Occurs during internal synchronization of the Extended Events timer.') , ('TRACEWRITE','Occurs when the SQL Trace rowset trace provider waits for either a free buffer or a buffer with events to process.') , ('TRAN_MARKLATCH_DT','Occurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRAN_MARKLATCH_EX','Occurs when waiting for an exclusive mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRAN_MARKLATCH_KP','Occurs when waiting for a keep mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRAN_MARKLATCH_NL','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('TRAN_MARKLATCH_SH','Occurs when waiting for a shared mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRAN_MARKLATCH_UP','Occurs when waiting for an update mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRANSACTION_MUTEX','Occurs during synchronization of access to a transaction by multiple batches.') , ('UTIL_PAGE_ALLOC','Occurs when transaction log scans wait for memory to be available during memory pressure.') , ('VIA_ACCEPT','Occurs when a Virtual Interface Adapter (VIA) provider connection is completed during startup.') , ('VIEW_DEFINITION_MUTEX','Occurs during synchronization on access to cached view definitions.') , ('WAIT_FOR_RESULTS','Occurs when waiting for a query notification to be triggered.') , ('WAIT_XTP_CKPT_CLOSE','Occurs when waiting for a checkpoint to complete.') , ('WAIT_XTP_CKPT_ENABLED','Occurs when checkpointing is disabled, and waiting for checkpointing to be enabled.') , ('WAIT_XTP_CKPT_STATE_LOCK','Occurs when synchronizing checking of checkpoint state.') , ('WAIT_XTP_GUEST','Occurs when the database memory allocator needs to stop receiving low-memory notifications.') , ('WAIT_XTP_HOST_WAIT','Occurs when waits are triggered by the database engine and implemented by the host.') , ('WAIT_XTP_OFFLINE_CKPT_LOG_IO','Occurs when offline checkpoint is waiting for a log read IO to complete.') , ('WAIT_XTP_OFFLINE_CKPT_NEW_LOG','Occurs when offline checkpoint is waiting for new log records to scan.') , ('WAIT_XTP_PROCEDURE_ENTRY','Occurs when a drop procedure is waiting for all current executions of that procedure to complete.') , ('WAIT_XTP_TASK_SHUTDOWN','Occurs when waiting for an In-Memory OLTP thread to complete.') , ('WAIT_XTP_TRAN_COMMIT','Occurs when execution of a natively compiled stored procedure is waiting for an XTP transaction to commit (waiting for transactions dependent on for instance).') , ('WAIT_XTP_TRAN_DEPENDENCY','Occurs when waiting for transaction dependencies.') , ('WAITFOR','Occurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait.') , ('WAITFOR_TASKSHUTDOWN','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('WAITSTAT_MUTEX','Occurs during synchronization of access to the collection of statistics used to populate sys.dm_os_wait_stats.') , ('WCC','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('WORKTBL_DROP','Occurs while pausing before retrying, after a failed worktable drop.') , ('WRITE_COMPLETION','Occurs when a write operation is in progress.') , ('WRITELOG','Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.') , ('XACT_OWN_TRANSACTION','Occurs while waiting to acquire ownership of a transaction.') , ('XACT_RECLAIM_SESSION','Occurs while waiting for the current owner of a session to release ownership of the session.') , ('XACTLOCKINFO','Occurs during synchronization of access to the list of locks for a transaction. In addition to the transaction itself, the list of locks is accessed by operations such as deadlock detection and lock migration during page splits.') , ('XACTWORKSPACE_MUTEX','Occurs during synchronization of defections from a transaction, as well as the number of database locks between enlist members of a transaction.') , ('XE_BUFFERMGR_ALLPROCESSED_EVENT','Occurs when Extended Events session buffers are flushed to targets. This wait occurs on a background thread.') , ('XE_BUFFERMGR_FREEBUF_EVENT','Occurs when either of the following conditions is true: An Extended Events session is configured for no event loss, and all buffers in the session are currently full. This can indicate that the buffers for an Extended Events session are too small, or should be partitioned. Audits experience a delay. This can indicate a disk bottleneck on the drive where the audits are written.') , ('XE_DISPATCHER_CONFIG_SESSION_LIST','Occurs when an Extended Events session that is using asynchronous targets is started or stopped. This wait indicates either of the following: An Extended Events session is registering with a background thread pool. The background thread pool is calculating the required number of threads based on current load.') , ('XE_DISPATCHER_JOIN','Occurs when a background thread that is used for Extended Events sessions is terminating.') , ('XE_DISPATCHER_WAIT','Occurs when a background thread that is used for Extended Events sessions is waiting for event buffers to process.') , ('XE_MODULEMGR_SYNC','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('XE_OLS_LOCK','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('XE_PACKAGE_LOCK_BACKOFF','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.') , ('XTPPROC_CACHE_ACCESS','Occurs when for accessing all natively compiled stored procedure cache objects.') , ('XTPPROC_PARTITIONED_STACK_CREATE','Occurs when allocating per-NUMA node natively compiled stored procedure cache structures (must be done single threaded) for a given procedure.') --this adds wait types from Microsoft Docs that I don't already have. INSERT INTO dbo.WaitDefs SELECT v.wait_type , v.wait_type_description FROM (VALUES ('ABR', '') , ('AM_INDBUILD_ALLOCATION', '') , ('AM_SCHEMAMGR_UNSHARED_CACHE', '') , ('ASSEMBLY_FILTER_HASHTABLE', '') , ('ASSEMBLY_LOAD', 'Occurs during exclusive access to assembly loading.') , ('ASYNC_DISKPOOL_LOCK', 'Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.') , ('ASYNC_IO_COMPLETION', 'Occurs when a task is waiting for I/Os to finish.') , ('ASYNC_NETWORK_IO', 'Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.') , ('ASYNC_OP_COMPLETION', '') , ('ASYNC_OP_CONTEXT_READ', '') , ('ASYNC_OP_CONTEXT_WRITE', '') , ('ASYNC_SOCKETDUP_IO', '') , ('AUDIT_GROUPCACHE_LOCK', 'Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit each audit action group.') , ('AUDIT_LOGINCACHE_LOCK', 'Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit login audit action groups.') , ('AUDIT_ON_DEMAND_TARGET_LOCK', 'Occurs when there is a wait on a lock that is used to ensure single initialization of audit related Extended Event targets.') , ('AUDIT_XE_SESSION_MGR', 'Occurs when there is a wait on a lock that is used to synchronize the starting and stopping of audit related Extended Events sessions.') , ('BACKUP', 'Occurs when a task is blocked as part of backup processing.') , ('BACKUP_OPERATOR', 'Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive.') , ('BACKUPBUFFER', 'Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.') , ('BACKUPIO', 'Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.') , ('BACKUPTHREAD', 'Occurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.') , ('BAD_PAGE_PROCESS', 'Occurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently.') , ('BLOB_METADATA', '') , ('BMPALLOCATION', '') , ('BMPBUILD', '') , ('BMPREPARTITION', '') , ('BMPREPLICATION', '') , ('BPSORT', '') , ('BROKER_CONNECTION_RECEIVE_TASK', 'Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.') , ('BROKER_DISPATCHER', '') , ('BROKER_ENDPOINT_STATE_MUTEX', 'Occurs when there is contention to access the state of a Service Broker connection endpoint. Access to the state for changes is serialized.') , ('BROKER_EVENTHANDLER', 'Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.') , ('BROKER_FORWARDER', '') , ('BROKER_INIT', 'Occurs when initializing Service Broker in each active database. This should occur infrequently.') , ('BROKER_MASTERSTART', 'Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.') , ('BROKER_RECEIVE_WAITFOR', 'Occurs when the RECEIVE WAITFOR is waiting. This may mean that either no messages are ready to be received in the queue or a lock contention is preventing it from receiving messages from the queue.') , ('BROKER_REGISTERALLENDPOINTS', 'Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.') , ('BROKER_SERVICE', 'Occurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized.') , ('BROKER_SHUTDOWN', 'Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.') , ('BROKER_START', '') , ('BROKER_TASK_SHUTDOWN', '') , ('BROKER_TASK_STOP', 'Occurs when the Service Broker queue task handler tries to shut down the task. The state check is serialized and must be in a running state beforehand.') , ('BROKER_TASK_SUBMIT', '') , ('BROKER_TO_FLUSH', 'Occurs when the Service Broker lazy flusher flushes the in-memory transmission objects to a work table.') , ('BROKER_TRANSMISSION_OBJECT', '') , ('BROKER_TRANSMISSION_TABLE', '') , ('BROKER_TRANSMISSION_WORK', '') , ('BROKER_TRANSMITTER', 'Occurs when the Service Broker transmitter is waiting for work.') , ('BUILTIN_HASHKEY_MUTEX', 'May occur after startup of instance, while internal data structures are initializing. Will not recur once data structures have initialized.') , ('CHANGE_TRACKING_WAITFORCHANGES', '') , ('CHECK_PRINT_RECORD', '') , ('CHECK_SCANNER_MUTEX', '') , ('CHECK_TABLES_INITIALIZATION', '') , ('CHECK_TABLES_SINGLE_SCAN', '') , ('CHECK_TABLES_THREAD_BARRIER', '') , ('CHECKPOINT_QUEUE', 'Occurs while the checkpoint task is waiting for the next checkpoint request.') , ('CHKPT', 'Occurs at server startup to tell the checkpoint thread that it can start.') , ('CLEAR_DB', 'Occurs during operations that change the state of a database, such as opening or closing a database.') , ('CLR_AUTO_EVENT', 'Occurs when a task is currently performing common language runtime (CLR) execution and is waiting for a particular autoevent to be initiated. Long waits are typical, and do not indicate a problem.') , ('CLR_CRST', 'Occurs when a task is currently performing CLR execution and is waiting to enter a critical section of the task that is currently being used by another task.') , ('CLR_JOIN', 'Occurs when a task is currently performing CLR execution and waiting for another task to end. This wait state occurs when there is a join between tasks.') , ('CLR_MANUAL_EVENT', 'Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.') , ('CLR_MEMORY_SPY', 'Occurs during a wait on lock acquisition for a data structure that is used to record all virtual memory allocations that come from CLR. The data structure is locked to maintain its integrity if there is parallel access.') , ('CLR_MONITOR', 'Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.') , ('CLR_RWLOCK_READER', 'Occurs when a task is currently performing CLR execution and is waiting for a reader lock.') , ('CLR_RWLOCK_WRITER', 'Occurs when a task is currently performing CLR execution and is waiting for a writer lock.') , ('CLR_SEMAPHORE', 'Occurs when a task is currently performing CLR execution and is waiting for a semaphore.') , ('CLR_TASK_START', 'Occurs while waiting for a CLR task to complete startup.') , ('CLRHOST_STATE_ACCESS', 'Occurs where there is a wait to acquire exclusive access to the CLR-hosting data structures. This wait type occurs while setting up or tearing down the CLR runtime.') , ('CMEMPARTITIONED', '') , ('CMEMTHREAD', 'Occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.') , ('COLUMNSTORE_BUILD_THROTTLE', '') , ('COLUMNSTORE_COLUMNDATASET_SESSION_LIST', '') , ('COMMIT_TABLE', 'TBD') , ('CONNECTION_ENDPOINT_LOCK', '') , ('COUNTRECOVERYMGR', '') , ('CREATE_DATINISERVICE', '') , ('CXCONSUMER', 'Occurs with parallel query plans when a consumer thread waits for a producer thread to send rows. This is a normal part of parallel query execution. Applies to: SQL Server (Starting with SQL Server 2016 (13.x) SP2, SQL Server 2017 (14.x) CU3), SQL Database') , ('CXPACKET', 'Occurs with parallel query plans when synchronizing the query processor exchange iterator, and when producing and consuming rows. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism.Note: Starting with SQL Server 2016 (13.x) SP2, SQL Server 2017 (14.x) CU3, and SQL Database, CXPACKET only refers to synchronizing the query processor exchange iterator, and to producing rows for consumer threads. Consumer threads are tracked separately in the CXCONSUMER wait type.') , ('CXROWSET_SYNC', 'Occurs during a parallel range scan.') , ('DAC_INIT', 'Occurs while the dedicated administrator connection is initializing.') , ('DBCC_SCALE_OUT_EXPR_CACHE', '') , ('DBMIRROR_DBM_EVENT', '') , ('DBMIRROR_DBM_MUTEX', '') , ('DBMIRROR_EVENTS_QUEUE', 'Occurs when database mirroring waits for events to process.') , ('DBMIRROR_SEND', 'Occurs when a task is waiting for a communications backlog at the network layer to clear to be able to send messages. Indicates that the communications layer is starting to become overloaded and affect the database mirroring data throughput.') , ('DBMIRROR_WORKER_QUEUE', 'Indicates that the database mirroring worker task is waiting for more work.') , ('DBMIRRORING_CMD', 'Occurs when a task is waiting for log records to be flushed to disk. This wait state is expected to be held for long periods of time.') , ('DBSEEDING_FLOWCONTROL', '') , ('DBSEEDING_OPERATION', '') , ('DEADLOCK_ENUM_MUTEX', 'Occurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time.') , ('DEADLOCK_TASK_SEARCH', 'Large waiting time on this resource indicates that the server is executing queries on top of sys.dm_os_waiting_tasks, and these queries are blocking deadlock monitor from running deadlock search. This wait type is used by deadlock monitor only. Queries on top of sys.dm_os_waiting_tasks use DEADLOCK_ENUM_MUTEX.') , ('DEBUG', 'Occurs during Transact-SQL and CLR debugging for internal synchronization.') , ('DIRECTLOGCONSUMER_LIST', '') , ('DIRTY_PAGE_POLL', '') , ('DIRTY_PAGE_SYNC', '') , ('DIRTY_PAGE_TABLE_LOCK', '') , ('DISABLE_VERSIONING', 'Occurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement.') , ('DISKIO_SUSPEND', 'Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.') , ('DISPATCHER_PRIORITY_QUEUE_SEMAPHORE', '') , ('DISPATCHER_QUEUE_SEMAPHORE', 'Occurs when a thread from the dispatcher pool is waiting for more work to process. The wait time for this wait type is expected to increase when the dispatcher is idle.') , ('DLL_LOADING_MUTEX', 'Occurs once while waiting for the XML parser DLL to load.') , ('DPT_ENTRY_LOCK', '') , ('DROP_DATABASE_TIMER_TASK', '') , ('DROPTEMP', 'Occurs between attempts to drop a temporary object if the previous attempt failed. The wait duration grows exponentially with each failed drop attempt.') , ('DTC', 'Occurs when a task is waiting on an event that is used to manage state transition. This state controls when the recovery of Microsoft Distributed Transaction Coordinator (MS DTC) transactions occurs after SQL Server receives notification that the MS DTC service has become unavailable.') , ('DTC_ABORT_REQUEST', 'Occurs in a MS DTC worker session when the session is waiting to take ownership of a MS DTC transaction. After MS DTC owns the transaction, the session can roll back the transaction. Generally, the session will wait for another session that is using the transaction.') , ('DTC_RESOLVE', 'Occurs when a recovery task is waiting for the master database in a cross-database transaction so that the task can query the outcome of the transaction.') , ('DTC_STATE', 'Occurs when a task is waiting on an event that protects changes to the internal MS DTC global state object. This state should be held for very short periods of time.') , ('DTC_TMDOWN_REQUEST', 'Occurs in a MS DTC worker session when SQL Server receives notification that the MS DTC service is not available. First, the worker will wait for the MS DTC recovery process to start. Then, the worker waits to obtain the outcome of the distributed transaction that the worker is working on. This may continue until the connection with the MS DTC service has been reestablished.') , ('DTC_WAITFOR_OUTCOME', 'Occurs when recovery tasks wait for MS DTC to become active to enable the resolution of prepared transactions.') , ('DTCNEW_ENLIST', '') , ('DTCNEW_PREPARE', '') , ('DTCNEW_RECOVERY', '') , ('DTCNEW_TM', '') , ('DTCNEW_TRANSACTION_ENLISTMENT', '') , ('DTCPNTSYNC', '') , ('DUMP_LOG_COORDINATOR', 'Occurs when a main task is waiting for a subtask to generate data. Ordinarily, this state does not occur. A long wait indicates an unexpected blockage. The subtask should be investigated.') , ('DUMP_LOG_COORDINATOR_QUEUE', 'TBD') , ('DUMPTRIGGER', '') , ('EC', '') , ('EE_PMOLOCK', 'Occurs during synchronization of certain types of memory allocations during statement execution.') , ('EE_SPECPROC_MAP_INIT', 'Occurs during synchronization of internal procedure hash table creation. This wait can only occur during the initial accessing of the hash table after the SQL Server instance starts.') , ('ENABLE_EMPTY_VERSIONING', '') , ('ENABLE_VERSIONING', 'Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.') , ('ERROR_REPORTING_MANAGER', 'Occurs during synchronization of multiple concurrent error log initializations.') , ('EXCHANGE', 'Occurs during synchronization in the query processor exchange iterator during parallel queries.') , ('EXECSYNC', 'Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such areas are bitmaps, large binary objects (LOBs), and the spool iterator. LOBs may frequently use this wait state.') , ('EXECUTION_PIPE_EVENT_INTERNAL', 'Occurs during synchronization between producer and consumer parts of batch execution that are submitted through the connection context.') , ('EXTERNAL_RG_UPDATE', '') , ('EXTERNAL_SCRIPT_NETWORK_IO', 'Applies to: SQL Server 2017 (14.x) through current.') , ('EXTERNAL_SCRIPT_PREPARE_SERVICE', '') , ('EXTERNAL_SCRIPT_SHUTDOWN', '') , ('EXTERNAL_WAIT_ON_LAUNCHER,', '') , ('FABRIC_HADR_TRANSPORT_CONNECTION', '') , ('FABRIC_REPLICA_CONTROLLER_LIST', '') , ('FABRIC_REPLICA_CONTROLLER_STATE_AND_CONFIG', '') , ('FABRIC_REPLICA_PUBLISHER_EVENT_PUBLISH', '') , ('FABRIC_REPLICA_PUBLISHER_SUBSCRIBER_LIST', '') , ('FABRIC_WAIT_FOR_BUILD_REPLICA_EVENT_PROCESSING', '') , ('FAILPOINT', '') , ('FCB_REPLICA_READ', 'Occurs when the reads of a snapshot (or a temporary snapshot created by DBCC) sparse file are synchronized.') , ('FCB_REPLICA_WRITE', 'Occurs when the pushing or pulling of a page to a snapshot (or a temporary snapshot created by DBCC) sparse file is synchronized.') , ('FEATURE_SWITCHES_UPDATE', '') , ('FFT_NSO_DB_KILL_FLAG', '') , ('FFT_NSO_DB_LIST', '') , ('FFT_NSO_FCB', '') , ('FFT_NSO_FCB_FIND', '') , ('FFT_NSO_FCB_PARENT', '') , ('FFT_NSO_FCB_RELEASE_CACHED_ENTRIES', '') , ('FFT_NSO_FCB_STATE', '') , ('FFT_NSO_FILEOBJECT', '') , ('FFT_NSO_TABLE_LIST', '') , ('FFT_NTFS_STORE', '') , ('FFT_RECOVERY', '') , ('FFT_RSFX_COMM', '') , ('FFT_RSFX_WAIT_FOR_MEMORY', '') , ('FFT_STARTUP_SHUTDOWN', '') , ('FFT_STORE_DB', '') , ('FFT_STORE_ROWSET_LIST', '') , ('FFT_STORE_TABLE', '') , ('FILE_VALIDATION_THREADS', '') , ('FILESTREAM_CACHE', '') , ('FILESTREAM_CHUNKER', '') , ('FILESTREAM_CHUNKER_INIT', '') , ('FILESTREAM_FCB', '') , ('FILESTREAM_FILE_OBJECT', '') , ('FILESTREAM_WORKITEM_QUEUE', '') , ('FILETABLE_SHUTDOWN', '') , ('FOREIGN_REDO', 'Applies to: SQL Server 2017 (14.x) through current.') , ('FORWARDER_TRANSITION', '') , ('FS_FC_RWLOCK', 'Occurs when there is a wait by the FILESTREAM garbage collector to do either of the following:') , ('FS_GARBAGE_COLLECTOR_SHUTDOWN', 'Occurs when the FILESTREAM garbage collector is waiting for cleanup tasks to be completed.') , ('FS_HEADER_RWLOCK', 'Occurs when there is a wait to acquire access to the FILESTREAM header of a FILESTREAM data container to either read or update contents in the FILESTREAM header file (Filestream.hdr).') , ('FS_LOGTRUNC_RWLOCK', 'Occurs when there is a wait to acquire access to FILESTREAM log truncation to do either of the following:') , ('FSA_FORCE_OWN_XACT', 'Occurs when a FILESTREAM file I/O operation needs to bind to the associated transaction, but the transaction is currently owned by another session.') , ('FSAGENT', 'Occurs when a FILESTREAM file I/O operation is waiting for a FILESTREAM agent resource that is being used by another file I/O operation.') , ('FSTR_CONFIG_MUTEX', 'Occurs when there is a wait for another FILESTREAM feature reconfiguration to be completed.') , ('FSTR_CONFIG_RWLOCK', 'Occurs when there is a wait to serialize access to the FILESTREAM configuration parameters.') , ('FT_COMPROWSET_RWLOCK', 'Full-text is waiting on fragment metadata operation. Documented for informational purposes only. Not supported. ') , ('FT_IFTS_RWLOCK', 'Full-text is waiting on internal synchronization. Documented for informational purposes only. Not supported. ') , ('FT_IFTS_SCHEDULER_IDLE_WAIT', 'Full-text scheduler sleep wait type. The scheduler is idle.') , ('FT_IFTSHC_MUTEX', 'Full-text is waiting on an fdhost control operation. Documented for informational purposes only. Not supported. ') , ('FT_IFTSISM_MUTEX', 'Full-text is waiting on communication operation. Documented for informational purposes only. Not supported. ') , ('FT_MASTER_MERGE', 'Full-text is waiting on master merge operation. Documented for informational purposes only. Not supported. ') , ('FT_MASTER_MERGE_COORDINATOR', '') , ('FT_METADATA_MUTEX', 'Documented for informational purposes only. Not supported. ') , ('FT_PROPERTYLIST_CACHE', '') , ('FT_RESTART_CRAWL', 'Occurs when a full-text crawl needs to restart from a last known good point to recover from a transient failure. The wait lets the worker tasks currently working on that population to complete or exit the current step.') , ('FULLTEXT GATHERER', 'Occurs during synchronization of full-text operations.') , ('GDMA_GET_RESOURCE_OWNER', '') , ('GHOSTCLEANUP_UPDATE_STATS', '') , ('GHOSTCLEANUPSYNCMGR', '') , ('GLOBAL_QUERY_CANCEL', '') , ('GLOBAL_QUERY_CLOSE', '') , ('GLOBAL_QUERY_CONSUMER', '') , ('GLOBAL_QUERY_PRODUCER', '') , ('GLOBAL_TRAN_CREATE', '') , ('GLOBAL_TRAN_UCS_SESSION', '') , ('GUARDIAN', '') , ('HADR_AG_MUTEX', 'Occurs when an Always On DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the configuration of an availability group., ') , ('HADR_AR_CRITICAL_SECTION_ENTRY', 'Occurs when an Always On DDL statement or Windows Server Failover Clustering command is waiting for exclusive read/write access to the runtime state of the local replica of the associated availability group., ') , ('HADR_AR_MANAGER_MUTEX', 'Occurs when an availability replica shutdown is waiting for startup to complete or an availability replica startup is waiting for shutdown to complete. Internal use only., ') , ('HADR_AR_UNLOAD_COMPLETED', '') , ('HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST', 'The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers. Internal use only., ') , ('HADR_BACKUP_BULK_LOCK', 'The Always On primary database received a backup request from a secondary database and is waiting for the background thread to finish processing the request on acquiring or releasing the BulkOp lock., ') , ('HADR_BACKUP_QUEUE', 'The backup background thread of the Always On primary database is waiting for a new work request from the secondary database. (typically, this occurs when the primary database is holding the BulkOp log and is waiting for the secondary database to indicate that the primary database can release the lock)., ') , ('HADR_CLUSAPI_CALL', 'A SQL Server thread is waiting to switch from non-preemptive mode (scheduled by SQL Server) to preemptive mode (scheduled by the operating system) in order to invoke Windows Server Failover Clustering APIs., ') , ('HADR_COMPRESSED_CACHE_SYNC', 'Waiting for access to the cache of compressed log blocks that is used to avoid redundant compression of the log blocks sent to multiple secondary databases., ') , ('HADR_CONNECTIVITY_INFO', '') , ('HADR_DATABASE_FLOW_CONTROL', 'Waiting for messages to be sent to the partner when the maximum number of queued messages has been reached. Indicates that the log scans are running faster than the network sends. This is an issue only if network sends are slower than expected., ') , ('HADR_DATABASE_VERSIONING_STATE', 'Occurs on the versioning state change of an Always On secondary database. This wait is for internal data structures and is usually is very short with no direct effect on data access., ') , ('HADR_DATABASE_WAIT_FOR_RECOVERY', '') , ('HADR_DATABASE_WAIT_FOR_RESTART', 'Waiting for the database to restart under Always On Availability Groups control. Under normal conditions, this is not a customer issue because waits are expected here., ') , ('HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING', 'A query on object(s) in a readable secondary database of an Always On availability group is blocked on row versioning while waiting for commit or rollback of all transactions that were in-flight when the secondary replica was enabled for read workloads. This wait type guarantees that row versions are available before execution of a query under snapshot isolation., ') , ('HADR_DB_COMMAND', 'Waiting for responses to conversational messages (which require an explicit response from the other side, using the Always On conversational message infrastructure). A number of different message types use this wait type., ') , ('HADR_DB_OP_COMPLETION_SYNC', 'Waiting for responses to conversational messages (which require an explicit response from the other side, using the Always On conversational message infrastructure). A number of different message types use this wait type., ') , ('HADR_DB_OP_START_SYNC', 'An Always On DDL statement or a Windows Server Failover Clustering command is waiting for serialized access to an availability database and its runtime state., ') , ('HADR_DBR_SUBSCRIBER', 'The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the runtime state of an event subscriber that corresponds to an availability database. Internal use only., ') , ('HADR_DBR_SUBSCRIBER_FILTER_LIST', 'The publisher for an availability replica event (such as a state change or configuration change) is waiting for exclusive read/write access to the list of event subscribers that correspond to availability databases. Internal use only., ') , ('HADR_DBSEEDING', '') , ('HADR_DBSEEDING_LIST', '') , ('HADR_DBSTATECHANGE_SYNC', 'Concurrency control wait for updating the internal state of the database replica., ') , ('HADR_FABRIC_CALLBACK', '') , ('HADR_FILESTREAM_BLOCK_FLUSH', 'The FILESTREAM Always On transport manager is waiting until processing of a log block is finished., ') , ('HADR_FILESTREAM_FILE_CLOSE', 'The FILESTREAM Always On transport manager is waiting until the next FILESTREAM file gets processed and its handle gets closed., ') , ('HADR_FILESTREAM_FILE_REQUEST', 'An Always On secondary replica is waiting for the primary replica to send all requested FILESTREAM files during UNDO., ') , ('HADR_FILESTREAM_IOMGR', 'The FILESTREAM Always On transport manager is waiting for R/W lock that protects the FILESTREAM Always On I/O manager during startup or shutdown., ') , ('HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'The FILESTREAM Always On I/O manager is waiting for I/O completion., ') , ('HADR_FILESTREAM_MANAGER', 'The FILESTREAM Always On transport manager is waiting for the R/W lock that protects the FILESTREAM Always On transport manager during startup or shutdown., ') , ('HADR_FILESTREAM_PREPROC', '') , ('HADR_GROUP_COMMIT', 'Transaction commit processing is waiting to allow a group commit so that multiple commit log records can be put into a single log block. This wait is an expected condition that optimizes the log I/O, capture, and send operations., ') , ('HADR_LOGCAPTURE_SYNC', 'Concurrency control around the log capture or apply object when creating or destroying scans. This is an expected wait when partners change state or connection status., ') , ('HADR_LOGCAPTURE_WAIT', 'Waiting for log records to become available. Can occur either when waiting for new log records to be generated by connections or for I/O completion when reading log not in the cache. This is an expected wait if the log scan is caught up to the end of log or is reading from disk., ') , ('HADR_LOGPROGRESS_SYNC', 'Concurrency control wait when updating the log progress status of database replicas., ') , ('HADR_NOTIFICATION_DEQUEUE', 'A background task that processes Windows Server Failover Clustering notifications is waiting for the next notification. Internal use only., ') , ('HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS', 'The Always On availability replica manager is waiting for serialized access to the runtime state of a background task that processes Windows Server Failover Clustering notifications. Internal use only., ') , ('HADR_NOTIFICATION_WORKER_STARTUP_SYNC', 'A background task is waiting for the completion of the startup of a background task that processes Windows Server Failover Clustering notifications. Internal use only., ') , ('HADR_NOTIFICATION_WORKER_TERMINATION_SYNC', 'A background task is waiting for the termination of a background task that processes Windows Server Failover Clustering notifications. Internal use only., ') , ('HADR_PARTNER_SYNC', 'Concurrency control wait on the partner list., ') , ('HADR_READ_ALL_NETWORKS', 'Waiting to get read or write access to the list of WSFC networks. Internal use only. Note: The engine keeps a list of WSFC networks that is used in dynamic management views (such as sys.dm_hadr_cluster_networks) or to validate Always On Transact-SQL statements that reference WSFC network information. This list is updated upon engine startup, WSFC related notifications, and internal Always On restart (for example, losing and regaining of WSFC quorum). Tasks will usually be blocked when an update in that list is in progress. , ') , ('HADR_RECOVERY_WAIT_FOR_CONNECTION', 'Waiting for the secondary database to connect to the primary database before running recovery. This is an expected wait, which can lengthen if the connection to the primary is slow to establish., ') , ('HADR_RECOVERY_WAIT_FOR_UNDO', 'Database recovery is waiting for the secondary database to finish the reverting and initializing phase to bring it back to the common log point with the primary database. This is an expected wait after failovers.Undo progress can be tracked through the Windows System Monitor (perfmon.exe) and dynamic management views., ') , ('HADR_REPLICAINFO_SYNC', 'Waiting for concurrency control to update the current replica state., ') , ('HADR_SEEDING_CANCELLATION', '') , ('HADR_SEEDING_FILE_LIST', '') , ('HADR_SEEDING_LIMIT_BACKUPS', '') , ('HADR_SEEDING_SYNC_COMPLETION', '') , ('HADR_SEEDING_TIMEOUT_TASK', '') , ('HADR_SEEDING_WAIT_FOR_COMPLETION', '') , ('HADR_SYNC_COMMIT', 'Waiting for transaction commit processing for the synchronized secondary databases to harden the log. This wait is also reflected by the Transaction Delay performance counter. This wait type is expected for synchronized availability groups and indicates the time to send, write, and acknowledge log to the secondary databases., ') , ('HADR_SYNCHRONIZING_THROTTLE', 'Waiting for transaction commit processing to allow a synchronizing secondary database to catch up to the primary end of log in order to transition to the synchronized state. This is an expected wait when a secondary database is catching up., ') , ('HADR_TDS_LISTENER_SYNC', 'Either the internal Always On system or the WSFC cluster will request that listeners are started or stopped. The processing of this request is always asynchronous, and there is a mechanism to remove redundant requests. There are also moments that this process is suspended because of configuration changes. All waits related with this listener synchronization mechanism use this wait type. Internal use only., ') , ('HADR_TDS_LISTENER_SYNC_PROCESSING', 'Used at the end of an Always On Transact-SQL statement that requires starting and/or stopping anavailability group listener. Since the start/stop operation is done asynchronously, the user thread will block using this wait type until the situation of the listener is known., ') , ('HADR_THROTTLE_LOG_RATE_GOVERNOR', '') , ('HADR_THROTTLE_LOG_RATE_LOG_SIZE', '') , ('HADR_THROTTLE_LOG_RATE_SEEDING', '') , ('HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE', '') , ('HADR_TIMER_TASK', 'Waiting to get the lock on the timer task object and is also used for the actual waits between times that work is being performed. For example, for a task that runs every 10 seconds, after one execution, Always On Availability Groups waits about 10 seconds to reschedule the task, and the wait is included here., ') , ('HADR_TRANSPORT_DBRLIST', 'Waiting for access to the transport layer's database replica list. Used for the spinlock that grants access to it., ') , ('HADR_TRANSPORT_FLOW_CONTROL', 'Waiting when the number of outstanding unacknowledged Always On messages is over the out flow control threshold. This is on an availability replica-to-replica basis (not on a database-to-database basis)., ') , ('HADR_TRANSPORT_SESSION', 'Always On Availability Groups is waiting while changing or accessing the underlying transport state., ') , ('HADR_WORK_POOL', 'Concurrency control wait on the Always On Availability Groups background work task object., ') , ('HADR_WORK_QUEUE', 'Always On Availability Groups background worker thread waiting for new work to be assigned. This is an expected wait when there are ready workers waiting for new work, which is the normal state., ') , ('HADR_XRF_STACK_ACCESS', 'Accessing (look up, add, and delete) the extended recovery fork stack for an Always On availability database., ') , ('HCCO_CACHE', '') , ('HK_RESTORE_FILEMAP', '') , ('HKCS_PARALLEL_MIGRATION', '') , ('HKCS_PARALLEL_RECOVERY', '') , ('HTBUILD', '') , ('HTDELETE', '') , ('HTMEMO', '') , ('HTREINIT', '') , ('HTREPARTITION', '') , ('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.') , ('HTTP_STORAGE_CONNECTION', '') , ('IMPPROV_IOWAIT', 'Occurs when SQL Server waits for a bulkload I/O to finish.') , ('INSTANCE_LOG_RATE_GOVERNOR', '') , ('INTERNAL_TESTING', '') , ('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_QUEUE_LIMIT', '') , ('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', '') , ('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', '') , ('KTM_RECOVERY_MANAGER', '') , ('KTM_RECOVERY_RESOLUTION', '') , ('LATCH_DT', 'Occurs when waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LATCH_EX', 'Occurs when waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LATCH_KP', 'Occurs when waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LATCH_NL', '') , ('LATCH_SH', 'Occurs when waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LATCH_UP', 'Occurs when waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.') , ('LAZYWRITER_SLEEP', 'Occurs when lazywriter tasks are suspended. This is a measure of the time spent by background tasks that are waiting. Do not consider this state when you are looking for user stalls.') , ('LCK_M_BU', 'Occurs when a task is waiting to acquire a Bulk Update (BU) lock.') , ('LCK_M_BU_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_BU_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_IS', 'Occurs when a task is waiting to acquire an Intent Shared (IS) lock.') , ('LCK_M_IS_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_IS_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_IU', 'Occurs when a task is waiting to acquire an Intent Update (IU) lock.') , ('LCK_M_IU_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Intent Update (IU) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_IU_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Intent Update (IU) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_IX', 'Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.') , ('LCK_M_IX_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_IX_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RIn_NL', 'Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock.') , ('LCK_M_RIn_NL_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a NULL lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RIn_NL_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a NULL lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RIn_S', 'Occurs when a task is waiting to acquire a shared lock on the current key value, and an Insert Range lock between the current and previous key.') , ('LCK_M_RIn_S_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a shared lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RIn_S_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a shared lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RIn_U', 'Task is waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key.') , ('LCK_M_RIn_U_ABORT_BLOCKERS', 'Task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RIn_U_LOW_PRIORITY', 'Task is waiting to acquire an Update lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RIn_X', 'Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key.') , ('LCK_M_RIn_X_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RIn_X_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RS_S', 'Occurs when a task is waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous key.') , ('LCK_M_RS_S_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and a Shared Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RS_S_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and a Shared Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RS_U', 'Occurs when a task is waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key.') , ('LCK_M_RS_U_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Update Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RS_U_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Update Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RX_S', 'Occurs when a task is waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key.') , ('LCK_M_RX_S_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and an Exclusive Range with Abort Blockers lock between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RX_S_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and an Exclusive Range with Low Priority lock between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RX_U', 'Occurs when a task is waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key.') , ('LCK_M_RX_U_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Exclusive range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RX_U_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Exclusive range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RX_X', 'Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key.') , ('LCK_M_RX_X_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Exclusive Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_RX_X_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Exclusive Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_S', 'Occurs when a task is waiting to acquire a Shared lock.') , ('LCK_M_S_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a Shared lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_S_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a Shared lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_SCH_M', 'Occurs when a task is waiting to acquire a Schema Modify lock.') , ('LCK_M_SCH_M_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a Schema Modify lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_SCH_M_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a Schema Modify lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_SCH_S', 'Occurs when a task is waiting to acquire a Schema Share lock.') , ('LCK_M_SCH_S_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a Schema Share lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_SCH_S_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a Schema Share lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_SIU', 'Occurs when a task is waiting to acquire a Shared With Intent Update lock.') , ('LCK_M_SIU_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a Shared With Intent Update lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_SIU_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a Shared With Intent Update lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_SIX', 'Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.') , ('LCK_M_SIX_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_SIX_LOW_PRIORITY', 'Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_U', 'Occurs when a task is waiting to acquire an Update lock.') , ('LCK_M_U_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Update lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_U_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Update lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_UIX', 'Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.') , ('LCK_M_UIX_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_UIX_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_X', 'Occurs when a task is waiting to acquire an Exclusive lock.') , ('LCK_M_X_ABORT_BLOCKERS', 'Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LCK_M_X_LOW_PRIORITY', 'Occurs when a task is waiting to acquire an Exclusive lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.), ') , ('LOG_POOL_SCAN', '') , ('LOG_RATE_GOVERNOR', '') , ('LOGBUFFER', 'Occurs when a task is 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.') , ('LOGCAPTURE_LOGPOOLTRUNCPOINT', '') , ('LOGGENERATION', '') , ('LOGMGR', 'Occurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database.') , ('LOGMGR_FLUSH', '') , ('LOGMGR_PMM_LOG', '') , ('LOGMGR_QUEUE', 'Occurs while the log writer task waits for work requests.') , ('LOGMGR_RESERVE_APPEND', 'Occurs when a task is waiting to see whether log truncation frees up log space to enable the task to write a new log record. Consider increasing the size of the log file(s) for the affected database to reduce this wait.') , ('LOGPOOL_CACHESIZE', '') , ('LOGPOOL_CONSUMER', '') , ('LOGPOOL_CONSUMERSET', '') , ('LOGPOOL_FREEPOOLS', '') , ('LOGPOOL_MGRSET', '') , ('LOGPOOL_REPLACEMENTSET', '') , ('LOGPOOLREFCOUNTEDOBJECT_REFDONE', '') , ('LOWFAIL_MEMMGR_QUEUE', 'Occurs while waiting for memory to be available for use.') , ('MD_AGENT_YIELD', '') , ('MD_LAZYCACHE_RWLOCK', '') , ('MEMORY_ALLOCATION_EXT', 'Occurs while allocating memory from either the internal SQL Server memory pool or the operation system., ') , ('MEMORY_GRANT_UPDATE', '') , ('METADATA_LAZYCACHE_RWLOCK', '') , ('MIGRATIONBUFFER', '') , ('MISCELLANEOUS', '') , ('MISCELLANEOUS', '') , ('MSQL_DQ', 'Occurs when a task is waiting for a distributed query operation to finish. This is used to detect potential Multiple Active Result Set (MARS) application deadlocks. The wait ends when the distributed query call finishes.') , ('MSQL_XACT_MGR_MUTEX', 'Occurs when a task is waiting to obtain ownership of the session transaction manager to perform a session level transaction operation.') , ('MSQL_XACT_MUTEX', 'Occurs during synchronization of transaction usage. A request must acquire the mutex before it can use the transaction.') , ('MSQL_XP', 'Occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends.') , ('MSSEARCH', 'Occurs during Full-Text Search calls. This wait ends when the full-text operation completes. It does not indicate contention, but rather the duration of full-text operations.') , ('NET_WAITFOR_PACKET', 'Occurs when a connection is waiting for a network packet during a network read.') , ('NETWORKSXMLMGRLOAD', '') , ('NODE_CACHE_MUTEX', 'TBD') , ('OLEDB', 'Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.') , ('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.') , ('PAGEIOLATCH_DT', 'Occurs when a task is 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.') , ('PAGEIOLATCH_EX', 'Occurs when a task is 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.') , ('PAGEIOLATCH_KP', 'Occurs when a task is 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.') , ('PAGEIOLATCH_NL', '') , ('PAGEIOLATCH_SH', 'Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.') , ('PAGEIOLATCH_UP', 'Occurs when a task is 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.') , ('PAGELATCH_DT', 'Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode.') , ('PAGELATCH_EX', 'Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.') , ('PAGELATCH_KP', 'Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode.') , ('PAGELATCH_NL', '') , ('PAGELATCH_SH', 'Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.') , ('PAGELATCH_UP', 'Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode.') , ('PARALLEL_BACKUP_QUEUE', 'Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.') , ('PARALLEL_REDO_DRAIN_WORKER', '') , ('PARALLEL_REDO_FLOW_CONTROL', '') , ('PARALLEL_REDO_LOG_CACHE', '') , ('PARALLEL_REDO_TRAN_LIST', '') , ('PARALLEL_REDO_TRAN_TURN', '') , ('PARALLEL_REDO_WORKER_SYNC', '') , ('PARALLEL_REDO_WORKER_WAIT_WORK', '') , ('PERFORMANCE_COUNTERS_RWLOCK', 'TBD') , ('PHYSICAL_SEEDING_DMV', '') , ('POOL_LOG_RATE_GOVERNOR', '') , ('PREEMPTIVE_ABR', '') , ('PREEMPTIVE_AUDIT_ACCESS_EVENTLOG', 'Occurs when the SQL Server Operating System (SQLOS) scheduler switches to preemptive mode to write an audit event to the Windows event log. ') , ('PREEMPTIVE_AUDIT_ACCESS_SECLOG', 'Occurs when the SQLOS scheduler switches to preemptive mode to write an audit event to the Windows Security log. ') , ('PREEMPTIVE_CLOSEBACKUPMEDIA', 'Occurs when the SQLOS scheduler switches to preemptive mode to close backup media.') , ('PREEMPTIVE_CLOSEBACKUPTAPE', 'Occurs when the SQLOS scheduler switches to preemptive mode to close a tape backup device.') , ('PREEMPTIVE_CLOSEBACKUPVDIDEVICE', 'Occurs when the SQLOS scheduler switches to preemptive mode to close a virtual backup device.') , ('PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL', 'Occurs when the SQLOS scheduler switches to preemptive mode to perform Windows failover cluster operations.') , ('PREEMPTIVE_COM_COCREATEINSTANCE', 'Occurs when the SQLOS scheduler switches to preemptive mode to create a COM object.') , ('PREEMPTIVE_COM_COGETCLASSOBJECT', 'TBD') , ('PREEMPTIVE_COM_CREATEACCESSOR', 'TBD') , ('PREEMPTIVE_COM_DELETEROWS', 'TBD') , ('PREEMPTIVE_COM_GETCOMMANDTEXT', 'TBD') , ('PREEMPTIVE_COM_GETDATA', 'TBD') , ('PREEMPTIVE_COM_GETNEXTROWS', 'TBD') , ('PREEMPTIVE_COM_GETRESULT', 'TBD') , ('PREEMPTIVE_COM_GETROWSBYBOOKMARK', 'TBD') , ('PREEMPTIVE_COM_LBFLUSH', 'TBD') , ('PREEMPTIVE_COM_LBLOCKREGION', 'TBD') , ('PREEMPTIVE_COM_LBREADAT', 'TBD') , ('PREEMPTIVE_COM_LBSETSIZE', 'TBD') , ('PREEMPTIVE_COM_LBSTAT', 'TBD') , ('PREEMPTIVE_COM_LBUNLOCKREGION', 'TBD') , ('PREEMPTIVE_COM_LBWRITEAT', 'TBD') , ('PREEMPTIVE_COM_QUERYINTERFACE', 'TBD') , ('PREEMPTIVE_COM_RELEASE', 'TBD') , ('PREEMPTIVE_COM_RELEASEACCESSOR', 'TBD') , ('PREEMPTIVE_COM_RELEASEROWS', 'TBD') , ('PREEMPTIVE_COM_RELEASESESSION', 'TBD') , ('PREEMPTIVE_COM_RESTARTPOSITION', 'TBD') , ('PREEMPTIVE_COM_SEQSTRMREAD', 'TBD') , ('PREEMPTIVE_COM_SEQSTRMREADANDWRITE', 'TBD') , ('PREEMPTIVE_COM_SETDATAFAILURE', 'TBD') , ('PREEMPTIVE_COM_SETPARAMETERINFO', 'TBD') , ('PREEMPTIVE_COM_SETPARAMETERPROPERTIES', 'TBD') , ('PREEMPTIVE_COM_STRMLOCKREGION', 'TBD') , ('PREEMPTIVE_COM_STRMSEEKANDREAD', 'TBD') , ('PREEMPTIVE_COM_STRMSEEKANDWRITE', 'TBD') , ('PREEMPTIVE_COM_STRMSETSIZE', 'TBD') , ('PREEMPTIVE_COM_STRMSTAT', 'TBD') , ('PREEMPTIVE_COM_STRMUNLOCKREGION', 'TBD') , ('PREEMPTIVE_CONSOLEWRITE', 'TBD') , ('PREEMPTIVE_CREATEPARAM', 'TBD') , ('PREEMPTIVE_DEBUG', 'TBD') , ('PREEMPTIVE_DFSADDLINK', 'TBD') , ('PREEMPTIVE_DFSLINKEXISTCHECK', 'TBD') , ('PREEMPTIVE_DFSLINKHEALTHCHECK', 'TBD') , ('PREEMPTIVE_DFSREMOVELINK', 'TBD') , ('PREEMPTIVE_DFSREMOVEROOT', 'TBD') , ('PREEMPTIVE_DFSROOTFOLDERCHECK', 'TBD') , ('PREEMPTIVE_DFSROOTINIT', 'TBD') , ('PREEMPTIVE_DFSROOTSHARECHECK', 'TBD') , ('PREEMPTIVE_DTC_ABORT', 'TBD') , ('PREEMPTIVE_DTC_ABORTREQUESTDONE', 'TBD') , ('PREEMPTIVE_DTC_BEGINTRANSACTION', 'TBD') , ('PREEMPTIVE_DTC_COMMITREQUESTDONE', 'TBD') , ('PREEMPTIVE_DTC_ENLIST', 'TBD') , ('PREEMPTIVE_DTC_PREPAREREQUESTDONE', 'TBD') , ('PREEMPTIVE_FILESIZEGET', 'TBD') , ('PREEMPTIVE_FSAOLEDB_ABORTTRANSACTION', 'TBD') , ('PREEMPTIVE_FSAOLEDB_COMMITTRANSACTION', 'TBD') , ('PREEMPTIVE_FSAOLEDB_STARTTRANSACTION', 'TBD') , ('PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO', 'TBD') , ('PREEMPTIVE_GETRMINFO', 'TBD') , ('PREEMPTIVE_HADR_LEASE_MECHANISM', 'Always On Availability Groups lease manager scheduling for CSS diagnostics., ') , ('PREEMPTIVE_HTTP_EVENT_WAIT', '') , ('PREEMPTIVE_HTTP_REQUEST', '') , ('PREEMPTIVE_LOCKMONITOR', 'TBD') , ('PREEMPTIVE_MSS_RELEASE', 'TBD') , ('PREEMPTIVE_ODBCOPS', 'TBD') , ('PREEMPTIVE_OLE_UNINIT', 'TBD') , ('PREEMPTIVE_OLEDB_ABORTORCOMMITTRAN', 'TBD') , ('PREEMPTIVE_OLEDB_ABORTTRAN', 'TBD') , ('PREEMPTIVE_OLEDB_GETDATASOURCE', 'TBD') , ('PREEMPTIVE_OLEDB_GETLITERALINFO', 'TBD') , ('PREEMPTIVE_OLEDB_GETPROPERTIES', 'TBD') , ('PREEMPTIVE_OLEDB_GETPROPERTYINFO', 'TBD') , ('PREEMPTIVE_OLEDB_GETSCHEMALOCK', 'TBD') , ('PREEMPTIVE_OLEDB_JOINTRANSACTION', 'TBD') , ('PREEMPTIVE_OLEDB_RELEASE', 'TBD') , ('PREEMPTIVE_OLEDB_SETPROPERTIES', 'TBD') , ('PREEMPTIVE_OLEDBOPS', 'TBD') , ('PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT', 'TBD') , ('PREEMPTIVE_OS_ACQUIRECREDENTIALSHANDLE', 'TBD') , ('PREEMPTIVE_OS_AUTHENTICATIONOPS', 'TBD') , ('PREEMPTIVE_OS_AUTHORIZATIONOPS', 'TBD') , ('PREEMPTIVE_OS_AUTHZGETINFORMATIONFROMCONTEXT', 'TBD') , ('PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID', 'TBD') , ('PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER', 'TBD') , ('PREEMPTIVE_OS_BACKUPREAD', 'TBD') , ('PREEMPTIVE_OS_CLOSEHANDLE', 'TBD') , ('PREEMPTIVE_OS_CLUSTEROPS', 'TBD') , ('PREEMPTIVE_OS_COMOPS', 'TBD') , ('PREEMPTIVE_OS_COMPLETEAUTHTOKEN', 'TBD') , ('PREEMPTIVE_OS_COPYFILE', 'TBD') , ('PREEMPTIVE_OS_CREATEDIRECTORY', 'TBD') , ('PREEMPTIVE_OS_CREATEFILE', 'TBD') , ('PREEMPTIVE_OS_CRYPTACQUIRECONTEXT', 'TBD') , ('PREEMPTIVE_OS_CRYPTIMPORTKEY', 'TBD') , ('PREEMPTIVE_OS_CRYPTOPS', 'TBD') , ('PREEMPTIVE_OS_DECRYPTMESSAGE', 'TBD') , ('PREEMPTIVE_OS_DELETEFILE', 'TBD') , ('PREEMPTIVE_OS_DELETESECURITYCONTEXT', 'TBD') , ('PREEMPTIVE_OS_DEVICEIOCONTROL', 'TBD') , ('PREEMPTIVE_OS_DEVICEOPS', 'TBD') , ('PREEMPTIVE_OS_DIRSVC_NETWORKOPS', 'TBD') , ('PREEMPTIVE_OS_DISCONNECTNAMEDPIPE', 'TBD') , ('PREEMPTIVE_OS_DOMAINSERVICESOPS', 'TBD') , ('PREEMPTIVE_OS_DSGETDCNAME', 'TBD') , ('PREEMPTIVE_OS_DTCOPS', 'TBD') , ('PREEMPTIVE_OS_ENCRYPTMESSAGE', 'TBD') , ('PREEMPTIVE_OS_FILEOPS', 'TBD') , ('PREEMPTIVE_OS_FINDFILE', 'TBD') , ('PREEMPTIVE_OS_FLUSHFILEBUFFERS', 'TBD') , ('PREEMPTIVE_OS_FORMATMESSAGE', 'TBD') , ('PREEMPTIVE_OS_FREECREDENTIALSHANDLE', 'TBD') , ('PREEMPTIVE_OS_FREELIBRARY', 'TBD') , ('PREEMPTIVE_OS_GENERICOPS', 'TBD') , ('PREEMPTIVE_OS_GETADDRINFO', 'TBD') , ('PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE', 'TBD') , ('PREEMPTIVE_OS_GETDISKFREESPACE', 'TBD') , ('PREEMPTIVE_OS_GETFILEATTRIBUTES', 'TBD') , ('PREEMPTIVE_OS_GETFILESIZE', 'TBD') , ('PREEMPTIVE_OS_GETFINALFILEPATHBYHANDLE', '') , ('PREEMPTIVE_OS_GETLONGPATHNAME', 'TBD') , ('PREEMPTIVE_OS_GETPROCADDRESS', 'TBD') , ('PREEMPTIVE_OS_GETVOLUMENAMEFORVOLUMEMOUNTPOINT', 'TBD') , ('PREEMPTIVE_OS_GETVOLUMEPATHNAME', 'TBD') , ('PREEMPTIVE_OS_INITIALIZESECURITYCONTEXT', 'TBD') , ('PREEMPTIVE_OS_LIBRARYOPS', 'TBD') , ('PREEMPTIVE_OS_LOADLIBRARY', 'TBD') , ('PREEMPTIVE_OS_LOGONUSER', 'TBD') , ('PREEMPTIVE_OS_LOOKUPACCOUNTSID', 'TBD') , ('PREEMPTIVE_OS_MESSAGEQUEUEOPS', 'TBD') , ('PREEMPTIVE_OS_MOVEFILE', 'TBD') , ('PREEMPTIVE_OS_NETGROUPGETUSERS', 'TBD') , ('PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS', 'TBD') , ('PREEMPTIVE_OS_NETUSERGETGROUPS', 'TBD') , ('PREEMPTIVE_OS_NETUSERGETLOCALGROUPS', 'TBD') , ('PREEMPTIVE_OS_NETUSERMODALSGET', 'TBD') , ('PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY', 'TBD') , ('PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE', 'TBD') , ('PREEMPTIVE_OS_OPENDIRECTORY', 'TBD') , ('PREEMPTIVE_OS_PDH_WMI_INIT', '') , ('PREEMPTIVE_OS_PIPEOPS', 'TBD') , ('PREEMPTIVE_OS_PROCESSOPS', 'TBD') , ('PREEMPTIVE_OS_QUERYCONTEXTATTRIBUTES', '') , ('PREEMPTIVE_OS_QUERYREGISTRY', 'TBD') , ('PREEMPTIVE_OS_QUERYSECURITYCONTEXTTOKEN', 'TBD') , ('PREEMPTIVE_OS_REMOVEDIRECTORY', 'TBD') , ('PREEMPTIVE_OS_REPORTEVENT', 'TBD') , ('PREEMPTIVE_OS_REVERTTOSELF', 'TBD') , ('PREEMPTIVE_OS_RSFXDEVICEOPS', 'TBD') , ('PREEMPTIVE_OS_SECURITYOPS', 'TBD') , ('PREEMPTIVE_OS_SERVICEOPS', 'TBD') , ('PREEMPTIVE_OS_SETENDOFFILE', 'TBD') , ('PREEMPTIVE_OS_SETFILEPOINTER', 'TBD') , ('PREEMPTIVE_OS_SETFILEVALIDDATA', 'TBD') , ('PREEMPTIVE_OS_SETNAMEDSECURITYINFO', 'TBD') , ('PREEMPTIVE_OS_SQLCLROPS', 'TBD') , ('PREEMPTIVE_OS_SQMLAUNCH', 'Applies to: SQL Server 2008 R2 through SQL Server 2016 (13.x).') , ('PREEMPTIVE_OS_VERIFYSIGNATURE', 'TBD') , ('PREEMPTIVE_OS_VERIFYTRUST', '') , ('PREEMPTIVE_OS_VSSOPS', 'TBD') , ('PREEMPTIVE_OS_WAITFORSINGLEOBJECT', 'TBD') , ('PREEMPTIVE_OS_WINSOCKOPS', 'TBD') , ('PREEMPTIVE_OS_WRITEFILE', 'TBD') , ('PREEMPTIVE_OS_WRITEFILEGATHER', 'TBD') , ('PREEMPTIVE_OS_WSASETLASTERROR', 'TBD') , ('PREEMPTIVE_REENLIST', 'TBD') , ('PREEMPTIVE_RESIZELOG', 'TBD') , ('PREEMPTIVE_ROLLFORWARDREDO', 'TBD') , ('PREEMPTIVE_ROLLFORWARDUNDO', 'TBD') , ('PREEMPTIVE_SB_STOPENDPOINT', 'TBD') , ('PREEMPTIVE_SERVER_STARTUP', 'TBD') , ('PREEMPTIVE_SETRMINFO', 'TBD') , ('PREEMPTIVE_SHAREDMEM_GETDATA', 'TBD') , ('PREEMPTIVE_SNIOPEN', 'TBD') , ('PREEMPTIVE_SOSHOST', 'TBD') , ('PREEMPTIVE_SOSTESTING', '') , ('PREEMPTIVE_SP_SERVER_DIAGNOSTICS', '') , ('PREEMPTIVE_STARTRM', 'TBD') , ('PREEMPTIVE_STREAMFCB_CHECKPOINT', 'TBD') , ('PREEMPTIVE_STREAMFCB_RECOVER', 'TBD') , ('PREEMPTIVE_STRESSDRIVER', '') , ('PREEMPTIVE_TESTING', '') , ('PREEMPTIVE_TRANSIMPORT', 'TBD') , ('PREEMPTIVE_UNMARSHALPROPAGATIONTOKEN', 'TBD') , ('PREEMPTIVE_VSS_CREATESNAPSHOT', 'TBD') , ('PREEMPTIVE_VSS_CREATEVOLUMESNAPSHOT', 'TBD') , ('PREEMPTIVE_XE_CALLBACKEXECUTE', 'TBD') , ('PREEMPTIVE_XE_CX_FILE_OPEN', '') , ('PREEMPTIVE_XE_CX_HTTP_CALL', '') , ('PREEMPTIVE_XE_DISPATCHER', 'TBD') , ('PREEMPTIVE_XE_ENGINEINIT', 'TBD') , ('PREEMPTIVE_XE_GETTARGETSTATE', 'TBD') , ('PREEMPTIVE_XE_SESSIONCOMMIT', 'TBD') , ('PREEMPTIVE_XE_TARGETFINALIZE', 'TBD') , ('PREEMPTIVE_XE_TARGETINIT', 'TBD') , ('PREEMPTIVE_XE_TIMERRUN', 'TBD') , ('PREEMPTIVE_XETESTING', '') , ('PRINT_ROLLBACK_PROGRESS', 'Used to wait while user processes are ended in a database that has been transitioned by using the ALTER DATABASE termination clause. For more information, see ALTER DATABASE (Transact-SQL).') , ('PRU_ROLLBACK_DEFERRED', '') , ('PWAIT_ALL_COMPONENTS_INITIALIZED', '') , ('PWAIT_COOP_SCAN', '') , ('PWAIT_DIRECTLOGCONSUMER_GETNEXT', '') , ('PWAIT_EVENT_SESSION_INIT_MUTEX', '') , ('PWAIT_FABRIC_REPLICA_CONTROLLER_DATA_LOSS', '') , ('PWAIT_HADR_ACTION_COMPLETED', '') , ('PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC', 'Occurs when a background task is waiting for the termination of the background task that receives (via polling) Windows Server Failover Clustering notifications., ') , ('PWAIT_HADR_CLUSTER_INTEGRATION', 'An append, replace, and/or remove operation is waiting to grab a write lock on an Always On internal list (such as a list of networks, network addresses, or availability group listeners). Internal use only, ') , ('PWAIT_HADR_FAILOVER_COMPLETED', '') , ('PWAIT_HADR_JOIN', '') , ('PWAIT_HADR_OFFLINE_COMPLETED', 'An Always On drop availability group operation is waiting for the target availability group to go offline before destroying Windows Server Failover Clustering objects., ') , ('PWAIT_HADR_ONLINE_COMPLETED', 'An Always On create or failover availability group operation is waiting for the target availability group to come online., ') , ('PWAIT_HADR_POST_ONLINE_COMPLETED', 'An Always On drop availability group operation is waiting for the termination of any background task that was scheduled as part of a previous command. For example, there may be a background task that is transitioning availability databases to the primary role. The DROP AVAILABILITY GROUP DDL must wait for this background task to terminate in order to avoid race conditions., ') , ('PWAIT_HADR_SERVER_READY_CONNECTIONS', '') , ('PWAIT_HADR_WORKITEM_COMPLETED', 'Internal wait by a thread waiting for an async work task to complete. This is an expected wait and is for CSS use., ') , ('PWAIT_HADRSIM', '') , ('PWAIT_LOG_CONSOLIDATION_IO', '') , ('PWAIT_LOG_CONSOLIDATION_POLL', '') , ('PWAIT_MD_LOGIN_STATS', 'Occurs during internal synchronization in metadata on login stats., ') , ('PWAIT_MD_RELATION_CACHE', 'Occurs during internal synchronization in metadata on table or index., ') , ('PWAIT_MD_SERVER_CACHE', 'Occurs during internal synchronization in metadata on linked servers., ') , ('PWAIT_MD_UPGRADE_CONFIG', 'Occurs during internal synchronization in upgrading server wide configurations., ') , ('PWAIT_PREEMPTIVE_APP_USAGE_TIMER', '') , ('PWAIT_PREEMPTIVE_AUDIT_ACCESS_WINDOWSLOG', '') , ('PWAIT_QRY_BPMEMORY', '') , ('PWAIT_REPLICA_ONLINE_INIT_MUTEX', '') , ('PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC', '') , ('PWAIT_SBS_FILE_OPERATION', '') , ('PWAIT_XTP_FSSTORAGE_MAINTENANCE', '') , ('PWAIT_XTP_HOST_STORAGE_WAIT', '') , ('QDS_ASYNC_CHECK_CONSISTENCY_TASK', '') , ('QDS_ASYNC_PERSIST_TASK', '') , ('QDS_ASYNC_PERSIST_TASK_START', '') , ('QDS_ASYNC_QUEUE', '') , ('QDS_BCKG_TASK', '') , ('QDS_BLOOM_FILTER', '') , ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', '') , ('QDS_CTXS', '') , ('QDS_DB_DISK', '') , ('QDS_DYN_VECTOR', '') , ('QDS_EXCLUSIVE_ACCESS', '') , ('QDS_HOST_INIT', '') , ('QDS_LOADDB', '') , ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', '') , ('QDS_QDS_CAPTURE_INIT', '') , ('QDS_SHUTDOWN_QUEUE', '') , ('QDS_STMT', '') , ('QDS_STMT_DISK', '') , ('QDS_TASK_SHUTDOWN', '') , ('QDS_TASK_START', '') , ('QE_WARN_LIST_SYNC', '') , ('QPJOB_KILL', 'Indicates that an asynchronous automatic statistics update was canceled by a call to KILL as the update was starting to run. The terminating thread is suspended, waiting for it to start listening for KILL commands. A good value is less than one second.') , ('QPJOB_WAITFOR_ABORT', 'Indicates that an asynchronous automatic statistics update was canceled by a call to KILL when it was running. The update has now completed but is suspended until the terminating thread message coordination is complete. This is an ordinary but rare state, and should be very short. A good value is less than one second.') , ('QRY_MEM_GRANT_INFO_MUTEX', 'Occurs when Query Execution memory management tries to control access to static grant information list. This state lists information about the current granted and waiting memory requests. This state is a simple access control state. There should never be a long wait on this state. If this mutex is not released, all new memory-using queries will stop responding.') , ('QRY_PARALLEL_THREAD_MUTEX', '') , ('QRY_PROFILE_LIST_MUTEX', '') , ('QUERY_ERRHDL_SERVICE_DONE', '') , ('QUERY_WAIT_ERRHDL_SERVICE', 'Identified for informational purposes only. Not supported. ') , ('QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN', 'Occurs in certain cases when offline create index build is run in parallel, and the different worker threads that are sorting synchronize access to the sort files.') , ('QUERY_NOTIFICATION_MGR_MUTEX', 'Occurs during synchronization of the garbage collection queue in the Query Notification Manager.') , ('QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX', 'Occurs during state synchronization for transactions in Query Notifications.') , ('QUERY_NOTIFICATION_TABLE_MGR_MUTEX', 'Occurs during internal synchronization within the Query Notification Manager.') , ('QUERY_NOTIFICATION_UNITTEST_MUTEX', '') , ('QUERY_OPTIMIZER_PRINT_MUTEX', 'Occurs during synchronization of query optimizer diagnostic output production. This wait type only occurs if diagnostic settings have been enabled under direction of Microsoft Product Support.') , ('QUERY_TASK_ENQUEUE_MUTEX', '') , ('QUERY_TRACEOUT', '') , ('RBIO_WAIT_VLF', '') , ('RECOVER_CHANGEDB', 'Occurs during synchronization of database status in warm standby database.') , ('RECOVERY_MGR_LOCK', '') , ('REDO_THREAD_PENDING_WORK', '') , ('REDO_THREAD_SYNC', '') , ('REMOTE_BLOCK_IO', '') , ('REMOTE_DATA_ARCHIVE_MIGRATION_DMV', '') , ('REMOTE_DATA_ARCHIVE_SCHEMA_DMV', '') , ('REMOTE_DATA_ARCHIVE_SCHEMA_TASK_QUEUE', '') , ('REPL_CACHE_ACCESS', 'Occurs during synchronization on a replication article cache. During these waits, the replication log reader stalls, and data definition language (DDL) statements on a published table are blocked.') , ('REPL_HISTORYCACHE_ACCESS', 'TBD') , ('REPL_SCHEMA_ACCESS', 'Occurs during synchronization of replication schema version information. This state exists when DDL statements are executed on the replicated object, and when the log reader builds or consumes versioned schema based on DDL occurrence. Contention can be seen on this wait type if you have many published databases on a single publisher with transactional replication and the published databases are very active.') , ('REPL_TRANFSINFO_ACCESS', '') , ('REPL_TRANHASHTABLE_ACCESS', 'TBD') , ('REPL_TRANTEXTINFO_ACCESS', '') , ('REPLICA_WRITES', 'Occurs while a task waits for completion of page writes to database snapshots or DBCC replicas.') , ('REQUEST_DISPENSER_PAUSE', 'Occurs when a task is waiting for all outstanding I/O to complete, so that I/O to a file can be frozen for snapshot backup.') , ('REQUEST_FOR_DEADLOCK_SEARCH', 'Occurs while the deadlock monitor waits to start the next deadlock search. This wait is expected between deadlock detections, and lengthy total waiting time on this resource does not indicate a problem.') , ('RESERVED_MEMORY_ALLOCATION_EXT', '') , ('RESMGR_THROTTLED', 'Occurs when a new request comes in and is throttled based on the GROUP_MAX_REQUESTS setting.') , ('RESOURCE_GOVERNOR_IDLE', '') , ('RESOURCE_QUEUE', 'Occurs during synchronization of various internal resource queues.') , ('RESOURCE_SEMAPHORE', 'Occurs when a 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.') , ('RESOURCE_SEMAPHORE_MUTEX', 'Occurs while a query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests.') , ('RESOURCE_SEMAPHORE_QUERY_COMPILE', 'Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.') , ('RESOURCE_SEMAPHORE_SMALL_QUERY', 'Occurs when 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, because the server transfers the request to the main query memory pool if it fails to grant the requested memory within a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries. ') , ('RESTORE_FILEHANDLECACHE_ENTRYLOCK', '') , ('RESTORE_FILEHANDLECACHE_LOCK', '') , ('RG_RECONFIG', 'TBD') , ('ROWGROUP_OP_STATS', '') , ('ROWGROUP_VERSION', '') , ('RTDATA_LIST', '') , ('SATELLITE_CARGO', '') , ('SATELLITE_SERVICE_SETUP', '') , ('SATELLITE_TASK', '') , ('SBS_DISPATCH', '') , ('SBS_RECEIVE_TRANSPORT', '') , ('SBS_TRANSPORT', '') , ('SCAN_CHAR_HASH_ARRAY_INITIALIZATION', '') , ('SEC_DROP_TEMP_KEY', 'Occurs after a failed attempt to drop a temporary security key before a retry attempt.') , ('SECURITY_CNG_PROVIDER_MUTEX', '') , ('SECURITY_CRYPTO_CONTEXT_MUTEX', '') , ('SECURITY_DBE_STATE_MUTEX', '') , ('SECURITY_KEYRING_RWLOCK', '') , ('SECURITY_MUTEX', 'Occurs when there is a wait for mutexes that control access to the global list of Extensible Key Management (EKM) cryptographic providers and the session-scoped list of EKM sessions.') , ('SECURITY_RULETABLE_MUTEX', '') , ('SEMPLAT_DSI_BUILD', '') , ('SEQUENCE_GENERATION', '') , ('SEQUENTIAL_GUID', 'Occurs while a new sequential GUID is being obtained.') , ('SERVER_IDLE_CHECK', 'Occurs during synchronization of SQL Server instance idle status when a resource monitor is attempting to declare a SQL Server instance as idle or trying to wake up.') , ('SERVER_RECONFIGURE', '') , ('SESSION_WAIT_STATS_CHILDREN', '') , ('SHARED_DELTASTORE_CREATION', '') , ('SHUTDOWN', 'Occurs while a shutdown statement waits for active connections to exit.') , ('SLEEP_BPOOL_FLUSH', 'Occurs when a checkpoint is throttling the issuance of new I/Os in order to avoid flooding the disk subsystem.') , ('SLEEP_BUFFERPOOL_HELPLW', '') , ('SLEEP_DBSTARTUP', 'Occurs during database startup while waiting for all databases to recover.') , ('SLEEP_DCOMSTARTUP', 'Occurs once at most during SQL Server instance startup while waiting for DCOM initialization to complete.') , ('SLEEP_MASTERDBREADY', '') , ('SLEEP_MASTERMDREADY', '') , ('SLEEP_MASTERUPGRADED', '') , ('SLEEP_MEMORYPOOL_ALLOCATEPAGES', '') , ('SLEEP_MSDBSTARTUP', 'Occurs when SQL Trace waits for the msdb database to complete startup.') , ('SLEEP_RETRY_VIRTUALALLOC', '') , ('SLEEP_SYSTEMTASK', 'Occurs during the start of a background task while waiting for tempdb to complete startup.') , ('SLEEP_TASK', 'Occurs when a task sleeps while waiting for a generic event to occur.') , ('SLEEP_TEMPDBSTARTUP', 'Occurs while a task waits for tempdb to complete startup.') , ('SLEEP_WORKSPACE_ALLOCATEPAGE', '') , ('SLO_UPDATE', '') , ('SMSYNC', '') , ('SNI_CONN_DUP', '') , ('SNI_CRITICAL_SECTION', 'Occurs during internal synchronization within SQL Server networking components.') , ('SNI_HTTP_WAITFOR_0_DISCON', 'Occurs during SQL Server shutdown, while waiting for outstanding HTTP connections to exit.') , ('SNI_LISTENER_ACCESS', 'Occurs while waiting for non-uniform memory access (NUMA) nodes to update state change. Access to state change is serialized.') , ('SNI_TASK_COMPLETION', 'Occurs when there is a wait for all tasks to finish during a NUMA node state change.') , ('SNI_WRITE_ASYNC', '') , ('SOAP_READ', 'Occurs while waiting for an HTTP network read to complete.') , ('SOAP_WRITE', 'Occurs while waiting for an HTTP network write to complete.') , ('SOCKETDUPLICATEQUEUE_CLEANUP', '') , ('SOS_CALLBACK_REMOVAL', 'Occurs while performing synchronization on a callback list in order to remove a callback. It is not expected for this counter to change after server initialization is completed.') , ('SOS_DISPATCHER_MUTEX', 'Occurs during internal synchronization of the dispatcher pool. This includes when the pool is being adjusted.') , ('SOS_LOCALALLOCATORLIST', 'Occurs during internal synchronization in the SQL Server memory manager. ') , ('SOS_MEMORY_TOPLEVELBLOCKALLOCATOR', '') , ('SOS_MEMORY_USAGE_ADJUSTMENT', 'Occurs when memory usage is being adjusted among pools.') , ('SOS_OBJECT_STORE_DESTROY_MUTEX', 'Occurs during internal synchronization in memory pools when destroying objects from the pool.') , ('SOS_PHYS_PAGE_CACHE', 'Accounts for the time a thread waits to acquire the mutex it must acquire before it allocates physical pages or before it returns those pages to the operating system. Waits on this type only appear if the instance of SQL Server uses AWE memory., ') , ('SOS_PROCESS_AFFINITY_MUTEX', 'Occurs during synchronizing of access to process affinity settings.') , ('SOS_RESERVEDMEMBLOCKLIST', 'Occurs during internal synchronization in the SQL Server memory manager. ') , ('SOS_SCHEDULER_YIELD', 'Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.') , ('SOS_SMALL_PAGE_ALLOC', 'Occurs during the allocation and freeing of memory that is managed by some memory objects.') , ('SOS_STACKSTORE_INIT_MUTEX', 'Occurs during synchronization of internal store initialization.') , ('SOS_SYNC_TASK_ENQUEUE_EVENT', 'Occurs when a task is started in a synchronous manner. Most tasks in SQL Server are started in an asynchronous manner, in which control returns to the starter immediately after the task request has been placed on the work queue.') , ('SOS_VIRTUALMEMORY_LOW', 'Occurs when a memory allocation waits for a resource manager to free up virtual memory.') , ('SOSHOST_EVENT', 'Occurs when a hosted component, such as CLR, waits on a SQL Server event synchronization object.') , ('SOSHOST_INTERNAL', 'Occurs during synchronization of memory manager callbacks used by hosted components, such as CLR.') , ('SOSHOST_MUTEX', 'Occurs when a hosted component, such as CLR, waits on a SQL Server mutex synchronization object.') , ('SOSHOST_RWLOCK', 'Occurs when a hosted component, such as CLR, waits on a SQL Server reader-writer synchronization object.') , ('SOSHOST_SEMAPHORE', 'Occurs when a hosted component, such as CLR, waits on a SQL Server semaphore synchronization object.') , ('SOSHOST_SLEEP', 'Occurs when a hosted task sleeps while waiting for a generic event to occur. Hosted tasks are used by hosted components such as CLR.') , ('SOSHOST_TRACELOCK', 'Occurs during synchronization of access to trace streams.') , ('SOSHOST_WAITFORDONE', 'Occurs when a hosted component, such as CLR, waits for a task to complete.') , ('SP_PREEMPTIVE_SERVER_DIAGNOSTICS_SLEEP', '') , ('SP_SERVER_DIAGNOSTICS_BUFFER_ACCESS', '') , ('SP_SERVER_DIAGNOSTICS_INIT_MUTEX', '') , ('SP_SERVER_DIAGNOSTICS_SLEEP', '') , ('SQLCLR_APPDOMAIN', 'Occurs while CLR waits for an application domain to complete startup.') , ('SQLCLR_ASSEMBLY', 'Occurs while waiting for access to the loaded assembly list in the appdomain.') , ('SQLCLR_DEADLOCK_DETECTION', 'Occurs while CLR waits for deadlock detection to complete.') , ('SQLCLR_QUANTUM_PUNISHMENT', 'Occurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks.') , ('SQLSORT_NORMMUTEX', 'Occurs during internal synchronization, while initializing internal sorting structures.') , ('SQLSORT_SORTMUTEX', 'Occurs during internal synchronization, while initializing internal sorting structures.') , ('SQLTRACE_BUFFER_FLUSH', 'Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds. ') , ('SQLTRACE_FILE_BUFFER', 'Occurs during synchronization on trace buffers during a file trace., ') , ('SQLTRACE_FILE_READ_IO_COMPLETION', '') , ('SQLTRACE_FILE_WRITE_IO_COMPLETION', '') , ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP', '') , ('SQLTRACE_LOCK', '') , ('SQLTRACE_PENDING_BUFFER_WRITERS', '') , ('SQLTRACE_SHUTDOWN', 'Occurs while trace shutdown waits for outstanding trace events to complete.') , ('SQLTRACE_WAIT_ENTRIES', 'Occurs while a SQL Trace event queue waits for packets to arrive on the queue.') , ('SRVPROC_SHUTDOWN', 'Occurs while the shutdown process waits for internal resources to be released to shutdown cleanly.') , ('STARTUP_DEPENDENCY_MANAGER', '') , ('TDS_BANDWIDTH_STATE', '') , ('TDS_INIT', '') , ('TDS_PROXY_CONTAINER', '') , ('TEMPOBJ', 'Occurs when temporary object drops are synchronized. This wait is rare, and only occurs if a task has requested exclusive access for temp table drops.') , ('TEMPORAL_BACKGROUND_PROCEED_CLEANUP', '') , ('TERMINATE_LISTENER', '') , ('THREADPOOL', 'Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking unusually long, thus reducing the number of workers available to satisfy other batches.') , ('TIMEPRIV_TIMEPERIOD', 'Occurs during internal synchronization of the Extended Events timer.') , ('TRACE_EVTNOTIF', 'TBD') , ('TRACEWRITE', 'Occurs when the SQL Trace rowset trace provider waits for either a free buffer or a buffer with events to process.') , ('TRAN_MARKLATCH_DT', 'Occurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRAN_MARKLATCH_EX', 'Occurs when waiting for an exclusive mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRAN_MARKLATCH_KP', 'Occurs when waiting for a keep mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRAN_MARKLATCH_NL', '') , ('TRAN_MARKLATCH_SH', 'Occurs when waiting for a shared mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRAN_MARKLATCH_UP', 'Occurs when waiting for an update mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.') , ('TRANSACTION_MUTEX', 'Occurs during synchronization of access to a transaction by multiple batches.') , ('UCS_ENDPOINT_CHANGE', '') , ('UCS_MANAGER', '') , ('UCS_MEMORY_NOTIFICATION', '') , ('UCS_SESSION_REGISTRATION', '') , ('UCS_TRANSPORT', '') , ('UCS_TRANSPORT_STREAM_CHANGE', '') , ('UTIL_PAGE_ALLOC', 'Occurs when transaction log scans wait for memory to be available during memory pressure.') , ('VDI_CLIENT_COMPLETECOMMAND', '') , ('VDI_CLIENT_GETCOMMAND', '') , ('VDI_CLIENT_OPERATION', '') , ('VDI_CLIENT_OTHER', '') , ('VERSIONING_COMMITTING', '') , ('VIA_ACCEPT', 'Occurs when a Virtual Interface Adapter (VIA) provider connection is completed during startup.') , ('VIEW_DEFINITION_MUTEX', 'Occurs during synchronization on access to cached view definitions.') , ('WAIT_FOR_RESULTS', 'Occurs when waiting for a query notification to be triggered.') , ('WAIT_SCRIPTDEPLOYMENT_REQUEST', '') , ('WAIT_SCRIPTDEPLOYMENT_WORKER', '') , ('WAIT_XLOGREAD_SIGNAL', '') , ('WAIT_XTP_ASYNC_TX_COMPLETION', '') , ('WAIT_XTP_CKPT_AGENT_WAKEUP', '') , ('WAIT_XTP_CKPT_CLOSE', 'Occurs when waiting for a checkpoint to complete., ') , ('WAIT_XTP_CKPT_ENABLED', 'Occurs when checkpointing is disabled, and waiting for checkpointing to be enabled., ') , ('WAIT_XTP_CKPT_STATE_LOCK', 'Occurs when synchronizing checking of checkpoint state., ') , ('WAIT_XTP_COMPILE_WAIT', '') , ('WAIT_XTP_GUEST', 'Occurs when the database memory allocator needs to stop receiving low-memory notifications., ') , ('WAIT_XTP_HOST_WAIT', 'Occurs when waits are triggered by the database engine and implemented by the host., ') , ('WAIT_XTP_OFFLINE_CKPT_BEFORE_REDO', '') , ('WAIT_XTP_OFFLINE_CKPT_LOG_IO', 'Occurs when offline checkpoint is waiting for a log read IO to complete., ') , ('WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 'Occurs when offline checkpoint is waiting for new log records to scan., ') , ('WAIT_XTP_PROCEDURE_ENTRY', 'Occurs when a drop procedure is waiting for all current executions of that procedure to complete., ') , ('WAIT_XTP_RECOVERY', 'Occurs when database recovery is waiting for recovery of memory-optimized objects to finish., ') , ('WAIT_XTP_SERIAL_RECOVERY', '') , ('WAIT_XTP_SWITCH_TO_INACTIVE', '') , ('WAIT_XTP_TASK_SHUTDOWN', 'Occurs when waiting for an In-Memory OLTP thread to complete., ') , ('WAIT_XTP_TRAN_DEPENDENCY', 'Occurs when waiting for transaction dependencies., ') , ('WAITFOR', 'Occurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait.') , ('WAITFOR_PER_QUEUE', '') , ('WAITFOR_TASKSHUTDOWN', '') , ('WAITSTAT_MUTEX', 'Occurs during synchronization of access to the collection of statistics used to populate sys.dm_os_wait_stats.') , ('WCC', '') , ('WINDOW_AGGREGATES_MULTIPASS', '') , ('WINFAB_API_CALL', '') , ('WINFAB_REPLICA_BUILD_OPERATION', '') , ('WINFAB_REPORT_FAULT', '') , ('WORKTBL_DROP', 'Occurs while pausing before retrying, after a failed worktable drop.') , ('WRITE_COMPLETION', 'Occurs when a write operation is in progress.') , ('WRITELOG', 'Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.') , ('XACT_OWN_TRANSACTION', 'Occurs while waiting to acquire ownership of a transaction.') , ('XACT_RECLAIM_SESSION', 'Occurs while waiting for the current owner of a session to release ownership of the session.') , ('XACTLOCKINFO', 'Occurs during synchronization of access to the list of locks for a transaction. In addition to the transaction itself, the list of locks is accessed by operations such as deadlock detection and lock migration during page splits.') , ('XACTWORKSPACE_MUTEX', 'Occurs during synchronization of defections from a transaction, as well as the number of database locks between enlist members of a transaction.') , ('XDB_CONN_DUP_HASH', '') , ('XDES_HISTORY', '') , ('XDES_OUT_OF_ORDER_LIST', '') , ('XDES_SNAPSHOT', '') , ('XDESTSVERMGR', '') , ('XE_BUFFERMGR_ALLPROCESSED_EVENT', 'Occurs when Extended Events session buffers are flushed to targets. This wait occurs on a background thread.') , ('XE_BUFFERMGR_FREEBUF_EVENT', 'Occurs when either of the following conditions is true:') , ('XE_CALLBACK_LIST', '') , ('XE_CX_FILE_READ', '') , ('XE_DISPATCHER_CONFIG_SESSION_LIST', 'Occurs when an Extended Events session that is using asynchronous targets is started or stopped. This wait indicates either of the following:') , ('XE_DISPATCHER_JOIN', 'Occurs when a background thread that is used for Extended Events sessions is terminating.') , ('XE_DISPATCHER_WAIT', 'Occurs when a background thread that is used for Extended Events sessions is waiting for event buffers to process.') , ('XE_FILE_TARGET_TVF', '') , ('XE_LIVE_TARGET_TVF', '') , ('XE_MODULEMGR_SYNC', '') , ('XE_OLS_LOCK', '') , ('XE_PACKAGE_LOCK_BACKOFF', '') , ('XE_SERVICES_EVENTMANUAL', 'TBD') , ('XE_SERVICES_MUTEX', 'TBD') , ('XE_SERVICES_RWLOCK', 'TBD') , ('XE_SESSION_CREATE_SYNC', 'TBD') , ('XE_SESSION_FLUSH', 'TBD') , ('XE_SESSION_SYNC', 'TBD') , ('XE_STM_CREATE', 'TBD') , ('XE_TIMER_EVENT', 'TBD') , ('XE_TIMER_MUTEX', 'TBD') , ('XE_TIMER_TASK_DONE', 'TBD') , ('XIO_CREDENTIAL_MGR_RWLOCK', '') , ('XIO_CREDENTIAL_RWLOCK', '') , ('XIO_EDS_MGR_RWLOCK', '') , ('XIO_EDS_RWLOCK', '') , ('XIO_IOSTATS_BLOBLIST_RWLOCK', '') , ('XIO_IOSTATS_FCBLIST_RWLOCK', '') , ('XIO_LEASE_RENEW_MGR_RWLOCK', '') , ('XTP_HOST_DB_COLLECTION', '') , ('XTP_HOST_LOG_ACTIVITY', '') , ('XTP_HOST_PARALLEL_RECOVERY', '') , ('XTP_PREEMPTIVE_TASK', '') , ('XTP_TRUNCATION_LSN', '') , ('XTPPROC_CACHE_ACCESS', 'Occurs when for accessing all natively compiled stored procedure cache objects., ') , ('XTPPROC_PARTITIONED_STACK_CREATE', 'Occurs when allocating per-NUMA node natively compiled stored procedure cache structures (must be done single threaded) for a given procedure., ') )v (wait_type, wait_type_description) WHERE NOT EXISTS ( SELECT 1 FROM dbo.WaitDefs wd WHERE wd.WaitType = v.wait_type ) END IF OBJECT_ID(N'dbo.wsIgnore', N'U') IS NULL BEGIN CREATE TABLE dbo.wsIgnore ( wait_type SYSNAME CONSTRAINT wsIgnore_pk PRIMARY KEY CLUSTERED ); INSERT INTO dbo.wsIgnore (wait_type) VALUES (N'BROKER_EVENTHANDLER') , (N'BROKER_RECEIVE_WAITFOR') , (N'BROKER_TASK_STOP') , (N'BROKER_TO_FLUSH') , (N'BROKER_TRANSMITTER') , (N'CHECKPOINT_QUEUE') , (N'CHKPT') , (N'CLR_AUTO_EVENT') , (N'CLR_MANUAL_EVENT') , (N'CLR_SEMAPHORE') , (N'DBMIRROR_DBM_EVENT') , (N'DBMIRROR_EVENTS_QUEUE') , (N'DBMIRROR_WORKER_QUEUE') , (N'DBMIRRORING_CMD') , (N'DIRTY_PAGE_POLL') , (N'DISPATCHER_QUEUE_SEMAPHORE') , (N'EXECSYNC') , (N'FSAGENT') , (N'FT_IFTS_SCHEDULER_IDLE_WAIT') , (N'FT_IFTSHC_MUTEX') , (N'HADR_CLUSAPI_CALL') , (N'HADR_FILESTREAM_IOMGR_IOCOMPLETION') , (N'HADR_LOGCAPTURE_WAIT') , (N'HADR_NOTIFICATION_DEQUEUE') , (N'HADR_TIMER_TASK') , (N'HADR_WORK_QUEUE') , (N'KSOURCE_WAKEUP') , (N'LAZYWRITER_SLEEP') , (N'LOGMGR_QUEUE') , (N'ONDEMAND_TASK_QUEUE') /* , (N'PREEMPTIVE_OS_AUTHENTICATIONOPS') , (N'PREEMPTIVE_OS_AUTHORIZATIONOPS') , (N'PREEMPTIVE_OS_CRYPTIMPORTKEY') , (N'PREEMPTIVE_OS_CRYPTACQUIRECONTEXT') , (N'PREEMPTIVE_OS_DECRYPTMESSAGE') , (N'PREEMPTIVE_OS_DELETESECURITYCONTEXT') , (N'PREEMPTIVE_OS_DISCONNECTNAMEDPIPE') , (N'PREEMPTIVE_OS_GETPROCADDRESS') , (N'PREEMPTIVE_OS_LOOKUPACCOUNTSID') , (N'PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY') , (N'PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE') , (N'PREEMPTIVE_OS_PIPEOPS') , (N'PREEMPTIVE_OS_QUERYREGISTRY') , (N'PREEMPTIVE_OS_REVERTTOSELF') , (N'PREEMPTIVE_OS_WAITFORSINGLEOBJECT') , (N'PREEMPTIVE_XE_CALLBACKEXECUTE') , (N'PREEMPTIVE_XE_GETTARGETSTATE') , (N'PREEMPTIVE_XE_SESSIONCOMMIT') , (N'PREEMPTIVE_XE_TARGETFINALIZE') , (N'PREEMPTIVE_XE_TARGETINIT') */ , (N'PWAIT_ALL_COMPONENTS_INITIALIZED') , (N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP') , (N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP') , (N'REQUEST_FOR_DEADLOCK_SEARCH') , (N'RESOURCE_QUEUE') , (N'SERVER_IDLE_CHECK') --, (N'SLEEP_BPOOL_FLUSH') , (N'SLEEP_DBSTARTUP') , (N'SLEEP_DCOMSTARTUP') , (N'SLEEP_MASTERDBREADY') , (N'SLEEP_MASTERMDREADY') , (N'SLEEP_MASTERUPGRADED') , (N'SLEEP_MSDBSTARTUP') , (N'SLEEP_SYSTEMTASK') , (N'SLEEP_TASK') , (N'SLEEP_TEMPDBSTARTUP') , (N'SNI_HTTP_ACCEPT') , (N'SP_SERVER_DIAGNOSTICS_SLEEP') , (N'SQLTRACE_BUFFER_FLUSH') , (N'SQLTRACE_FILE_WRITE_IO_COMPLETION') , (N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP') , (N'SQLTRACE_WAIT_ENTRIES') , (N'WAIT_FOR_RESULTS') , (N'WAITFOR') , (N'WAITFOR_TASKSHUTDOWN') , (N'WAIT_XTP_HOST_WAIT') , (N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG') , (N'WAIT_XTP_CKPT_CLOSE') , (N'XE_DISPATCHER_JOIN') , (N'XE_DISPATCHER_WAIT') , (N'XE_TIMER_EVENT'); END SELECT @BatchReqSec = dopc.cntr_value FROM sys.dm_os_performance_counters dopc WHERE dopc.counter_name LIKE 'Batch Requests/sec%'; SET @RunNum = NEXT VALUE FOR dbo.ws_sequence; INSERT INTO dbo.ws_header (RunNum, datestamp, batch_requests_per_sec) VALUES (@RunNum, GETDATE(), @BatchReqSec); INSERT INTO dbo.ws (RunNum, wait_type_id, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms) SELECT RunNum = @RunNum , wd.wait_type_id , src.waiting_tasks_count , src.wait_time_ms , src.max_wait_time_ms , src.signal_wait_time_ms FROM ( SELECT dows.* FROM sys.dm_os_wait_stats dows EXCEPT SELECT dows.* FROM sys.dm_os_wait_stats dows INNER JOIN dbo.wsIgnore wsi ON dows.wait_type = wsi.wait_type ) src INNER JOIN dbo.WaitDefs wd ON src.wait_type = wd.WaitType ORDER BY src.waiting_tasks_count DESC; |
The dbo.wsIgnore table contains a list of wait stats that you might decide to ignore from analysis – add or remove items from that list as appropriate to your environment. The dbo.ws_header table lists a single row per execution with the RowNum, date stamp, and the number of batch requests per second. The below query displays a list of waits that occurred during the most recent two executions of the script above:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DECLARE @CurrentRun int; SELECT @CurrentRun = CONVERT(int, s.current_value) FROM sys.sequences s WHERE s.name = 'ws_sequence'; IF @CurrentRun IS NULL SET @CurrentRun = 0; ;WITH t AS ( SELECT wd.WaitType , ws.RunNum , ws.wait_type_id , wd.WaitDesc , wd.SQLSkillsURL , interval_waiting_task_count = ws.waiting_tasks_count - LEAD(ws.waiting_tasks_count, 1, ws.waiting_tasks_count) OVER (PARTITION BY ws.wait_type_id ORDER BY ws.RunNum DESC) , interval_wait_time_ms = ws.wait_time_ms - LEAD(ws.wait_time_ms, 1, ws.wait_time_ms) OVER (PARTITION BY ws.wait_type_id ORDER BY ws.RunNum DESC) , interval = DATEDIFF(SECOND, LEAD(wsh.datestamp, 1, wsh.datestamp) OVER (PARTITION BY ws.wait_type_id ORDER BY ws.RunNum DESC), wsh.datestamp) , wsh.datestamp FROM dbo.ws ws INNER JOIN dbo.WaitDefs wd ON ws.wait_type_id = wd.wait_type_id INNER JOIN dbo.ws_header wsh ON ws.RunNum = wsh.RunNum ) SELECT t.WaitType , t.WaitDesc , t.SQLSkillsURL , t.interval_waiting_task_count , t.interval_wait_time_ms , t.interval , t.datestamp FROM t WHERE t.RunNum = @CurrentRun AND t.interval_waiting_task_count > 0 ORDER BY t.wait_type_id, t.RunNum DESC; |
The output includes a column pointing directly to the SQL Skills wait-type library for the specific wait type, making it easy to get more in-depth knowledge about the more frequently seen waits. Some of the most frequently seen waits include:
Thansk for the post, awesome content.
The description list for most of the wait stats killed it.
Best regards.