Thursday, February 20, 2014

Using SERVERERROR Trigger to troubleshooting application error like ORA-00001

oracle@solaris:~$ sqlplus /

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 20 22:57:52 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on
SQL> set echo on
SQL> @trigger.sql

SQL> CREATE TABLE servererror_log (
  2      error_datetime  TIMESTAMP,
  3      error_user      VARCHAR2(30),
  4      db_name         VARCHAR2(9),
  5          l_server_error  number,
  6      error_stack     VARCHAR2(2000),
  7      captured_sql    VARCHAR2(1000))
  8  /

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER log_server_errors
  2  AFTER SERVERERROR
  3  ON DATABASE
  4  DECLARE
  5  sql_text ora_name_list_t;
  6  stmt clob;
  7  n number;
  8  l_server_error number;
  9  BEGIN
 10    n := ora_sql_txt(sql_text);
 11    if n > 1000 then n:= 1000; end if ;
 12    FOR i IN 1..n LOOP
 13      stmt := stmt || sql_text(i);
 14    END LOOP;
 15
 16    l_server_error := server_error(1);
 17
 18    INSERT INTO servererror_log
 19    (error_datetime, error_user, db_name,l_server_error,
 20     error_stack, captured_sql)
 21    VALUES
 22    (systimestamp, sys.login_user, sys.database_name,l_server_error,
 23    dbms_utility.format_error_stack, stmt);
 24    commit;
 25  END log_server_errors;
 26  /

Trigger created.

SQL>
SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 (id number primary key);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$ORACLE.SYS_C0011730) violated


SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

Session altered.

SQL> select * from servererror_log;

ERROR_DATETIME
---------------------------------------------------------------------------
ERROR_USER                     DB_NAME   L_SERVER_ERROR
------------------------------ --------- --------------
ERROR_STACK
--------------------------------------------------------------------------------
CAPTURED_SQL
--------------------------------------------------------------------------------
20-FEB-14 10.58.14.724928 PM
OPS$ORACLE                     ORCL                   1
ORA-00001: unique constraint (OPS$ORACLE.SYS_C0011730) violated
insert into t1 values (1)

Wednesday, February 12, 2014

Oracle Database 12.1 Upgrade New Features and Changes

New DBUA Interface

  • DBUA is now divided into 2 panels (initially introduced with 11.2 OUI), the left panel lists all the steps executed by DBUA and the right panel executes and shows the progress of all operations listed in the left panel.

New Pre-Upgrade Information Tool

  • Oracle Database 12c introduces the preupgrd.sql Pre-Upgrade Information Tool which replaces the utlu121i.sql script and earlier versions
  • With the new Pre-Upgrade Information Tool, the default behavior of the prerequisite upgrade checks has been enhanced in the following ways:
    • A log file, preupgrade.log, is created containing the output of the Pre-Upgrade Information Tool.
    • The preupgrade_fixups.sql script is created
    • The postupgrade_fixups.sql script is created
    • These files are created under $ORACLE_BASE/cfgtoollogs/<SID>/preupgrade

Enhanced Upgrade Automation

  • New parallel upgrade script which is a default feature in Oracle Database
  • SERVER ( catalog , catproc ) component upgrade scripts get executed in parallel, for independent sub-components
  • Parallel upgrade is driven by a PERL script:
    $ catctl.pl

Command Line Upgrade

  • In this release, the new Parallel Upgrade Utility, catctl.pl, provides parallel upgrade options that reduce downtime.
    cd $ORACLE_HOME%rdbms\admin
    $ORACLE_HOME\perl\bin\perl catctl.pl catupgrd.sql

Sunday, February 9, 2014

what kinds of statistics oracle optimizer relying on


Table statistics

Number of rows
Number of blocks
Average row length

Column statistics

Number of distinct values (NDV) in a column
Number of nulls in a column
Data distribution (histogram)
Extended statistics

Index statistics

Number of leaf blocks
Number of levels
Index clustering factor

System statistics

I/O performance and utilization
CPU performance and utilization

Sql server post for this week

(SFTW) SQL Server Links 07/02/14 http://www.johnsansom.com/sftw-sql-server-links-070214/

Saturday, February 8, 2014

Log mining SQL server Transaction Log

use AdventureWorks2012
go
dbcc sqlperf(logspace)

/*-----------------------------
Database Name    Log Size (MB)    Log Space Used (%)    Status
master    2.242188    26.02352    0
tempdb    0.7421875    59.01316    0
model    0.4921875    90.77381    0
msdb    0.7421875    49.21053    0
AdventureWorks2012    0.9921875    55.56102    0
ReportServer    1.054688    44.02778    0
ReportServerTempDB    1.054688    43.84259    0
---------------------------*/s
go
select  * from sys.dm_db_log_space_usage
/*-----------------------------
database_id    total_log_size_in_bytes    used_log_space_in_bytes    used_log_space_in_percent    log_space_in_bytes_since_last_backup
5    1040384    578048    55.56102    101888
---------------------------*/
go


select * into dbo.emplyee_new from HumanResources.Employee

go
select o.name, o.type,o.create_date,o.modify_date,m.definition from sys.all_objects o inner join sys.all_sql_modules m
on o.object_id=m.object_id
where name like '%dblog%'

/*---------------------------
name    type    create_date    modify_date
fn_dblog    IF    2011-11-04 21:26:15.210    2011-11-04 21:27:08.297
fn_dblog_xtp    IF    2013-10-03 18:03:58.533    2013-10-03 18:03:58.567
fn_dump_dblog    IF    2011-11-04 21:26:16.583    2011-11-04 21:27:08.300
fn_dump_dblog_xtp    IF    2013-10-03 18:03:58.580    2013-10-03 18:03:58.597
---------------------------*/
GO

select [Current LSN], Context, [Operation],AllocUnitName from sys.fn_dblog(null,null)
-- where [Current LSN]> '0000002d:0000017b:0002'
go

select [Current LSN],[Begin Time],[Transaction Name]  from sys.fn_dblog(null,null) where [Transaction Name] is not null
go

select convert(char(30),AllocUnitName) as AllocUnitName,
sum(convert(numeric, [Log Record Length])) as log_record_bytes,
sum(convert(numeric, [Log Reserve])) as log_reserve_bytes  from sys.fn_dblog('45:379:1',null)
group by AllocUnitName order by log_reserve_bytes desc

/*---------------------------
AllocUnitName                  log_record_bytes                        log_reserve_bytes
------------------------------ --------------------------------------- ---------------------------------------
dbo.emplyee_new                75956                                   136732
NULL                           1360                                    36966
sys.sysobjvalues.clst          1540                                    297
sys.sysprufiles.clst           244                                     209
sys.sysiscols.clst             128                                     74
sys.sysiscols.nc1              116                                     74
sys.sysidxstats.nc             164                                     74
sys.sysidxstats.clst           196                                     74
Unknown Alloc Unit             4216                                    0
sys.sysschobjs.clst            420                                     0
---------------------------*/
go

image

select * from fn_dblog_xtp(null,null)
go
select * from sys.database_files where type_desc='LOG'
GO

delete from dbo.emplyee_new where SickLeaveHours>20
go

BACKUP LOG [AdventureWorks2012] TO  DISK = N'C:\SampleDB\AdventureWorks2012_log.bak'
WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.ldf',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)

select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)
go

select * from fn_dump_dblog_xtp(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
    NULL,NULL,NULL)
go

How to clean SQL Buffer and Plan Cache

checkpoint DBCC FreeProcCache DBCC FreeSystemCache DBCC FlushProcInDB() DBCC DropCleanBuffers

Thursday, February 6, 2014

T-SQL Index Internal & Statistics


use AdventureWorks2012   
GO

select object_id   
,name     
,index_id    
,type    
,type_desc    
,is_unique    
,data_space_id    
,ignore_dup_key    
,is_primary_key    
,is_unique_constraint    
,fill_factor    
,is_padded    
,is_disabled    
,is_hypothetical    
,allow_row_locks     
,allow_page_locks     
,has_filter     
,filter_definition    
from sys.indexes where object_id=object_id('HumanResources.Employee')    
GO

select object_name=object_name(i.object_id),    
    index_name=id.name,    
    index_type=id.type_desc,    
    id.index_id,    
    column_name=c.name,    
    c.column_id     
from sys.index_columns i join sys.columns c    
  on i.object_id=c.object_id    
  and i.column_id=c.column_id    
  join sys.indexes id    
  on i.object_id=id.object_id    
  and i.index_id=id.index_id    
    where i.object_id=object_id('HumanResources.Employee')    
    order by index_id,column_id    
GO

select database_id   
,object_id   
,index_id    
,partition_number     
,index_type_desc       
,alloc_unit_type_desc         
,index_depth     
,index_level     
,avg_fragmentation_in_percent     
,fragment_count       
,avg_fragment_size_in_pages     
,page_count           
,avg_page_space_used_in_percent     
,record_count         
,ghost_record_count   
,version_ghost_record_count     
,min_record_size_in_bytes     
,max_record_size_in_bytes     
,avg_record_size_in_bytes     
,forwarded_record_count     
,compressed_page_count    
from sys.dm_db_index_physical_stats(db_id(),object_id('HumanResources.Employee'),1,DEFAULT,'DETAILED' )    
GO

select database_id   
--,object_id    
--,index_id    
--,partition_id    
--,rowset_id    
--,allocation_unit_id    
--,allocation_unit_type    
,allocation_unit_type_desc    
--,data_clone_id    
--,clone_state    
--,clone_state_desc    
,extent_file_id    
,extent_page_id    
,allocated_page_iam_file_id    
,allocated_page_iam_page_id    
,allocated_page_file_id    
,allocated_page_page_id    
,is_allocated    
,is_iam_page    
,is_mixed_page_allocation    
,page_free_space_percent    
,page_type    
,page_type_desc    
,page_level    
,next_page_file_id    
,next_page_page_id    
--,previous_page_file_id    
--,previous_page_page_id    
--,is_page_compressed    
--,has_ghost_records     
from sys.dm_db_database_page_allocations(db_id(),object_id('HumanResources.Employee'),1,DEFAULT,'DETAILED' )    
GO


DBCC IND('AdventureWorks2012','HumanResources.Employee',1)    
GO

DBCC TRACEON(3604,-1)   
GO    
DBCC PAGE(AdventureWorks2012,1,1052,3) with tableresults

sp_helptext fn_PhysLocCracker   
GO

select [NationalIDNumber], 'Location(File:Page:Slot)'=sys.fn_physLocFormatter(%%physloc%%),   
KeyHashValue=%%lockres%%    
from [AdventureWorks2012].[HumanResources].[Employee]    
GO

DBCC SHOW_STATISTICS('HumanResources.Employee','PK_Employee_BusinessEntityID')   
WITH STAT_HEADER, HISTOGRAM    
GO

SELECT OBJECT_NAME(s.object_id) AS object_name,   
    COL_NAME(sc.object_id, sc.column_id) AS column_name,    
    s.name AS statistics_name,    
    STATS_DATE=STATS_DATE(s.object_id,s.stats_id),    
    auto_created,    
    user_created    
FROM sys.stats AS s JOIN sys.stats_columns AS sc    
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id    
WHERE --s.name like '_WA%'    
sc.object_id=object_id('HumanResources.Employee')    
ORDER BY OBJECT_NAME(s.object_id),stats_date    
GO

UPDATE STATISTICS HumanResources.Employee   
-- WITH FULLSCAN    
WITH SAMPLE 50 PERCENT     
GO

Sunday, February 2, 2014

How to Install Tomcat 7

oracle@solaris:~$ sudo mkdir /opt/tomcat7
oracle@solaris:~$ sudo chown oracle:dba /opt/tomcat7
oracle@solaris:~$ unzip apache-tomcat-7.0.50.zip -d /opt/tomcat7

oracle@solaris:/opt/tomcat7/apache-tomcat-7.0.50/bin$ ./startup.sh
Using CATALINA_BASE:   /opt/tomcat7/apache-tomcat-7.0.50
Using CATALINA_HOME:   /opt/tomcat7/apache-tomcat-7.0.50
Using CATALINA_TMPDIR: /opt/tomcat7/apache-tomcat-7.0.50/temp
Using JRE_HOME:        /usr
Using CLASSPATH:       /opt/tomcat7/apache-tomcat-7.0.50/bin/bootstrap.jar:/opt/tomcat7/apache-tomcat-7.0.50/bin/tomcat-juli.jar

oracle@solaris/opt/tomcat7/apache-tomcat-7.0.50/bin$ ps -ef|grep tomcat7
  oracle  4360     1   0 23:39:06 pts/1       0:03 /usr/bin/java -Djava.util.logging.config.file=/opt/tomcat7/apache-tomcat-7.0.50
 

JDK6 and above is required to run Tomcat7. Refer to here below on installation of JDK7.

http://www.dbaglobe.com/2014/02/install-jdk-in-solaris-11-using-pkg.html

image

Install JDK in Solaris 11 using PKG command

root@solaris:/opt/jdk7# pkg search -p jdk
PACKAGE                                                          PUBLISHER
pkg:/developer/java/jdk-6@1.6.0.35-0.175.1.0.0.24.1              solaris
pkg:/developer/java/jdk-7@1.7.0.7-0.175.1.0.0.24.0               solaris
pkg:/developer/java/jdk64@0.5.11-0.151.0.1                       solaris
pkg:/developer/java/jdk@0.5.11-0.151.0.1                         solaris
pkg:/developer/java/jdk@0.5.11-0.173                             solaris
pkg:/developer/java/jdk@1.6.0.35-0.175.1.0.0.24.1                solaris
pkg:/developer/java/jdk@1.7.0.21-0.175.1.7.0.4.0                 solaris
pkg:/developer/java/jdk@1.7.0.25-0.175.1.9.0.3.0                 solaris
pkg:/developer/java/jdk@1.7.0.45-0.175.1.12.0.5.0                solaris
pkg:/developer/java/jdk@1.7.0.5-0.175.0.10.1.0.0                 solaris
pkg:/developer/java/jdk@1.7.0.51.13                              solaris
pkg:/developer/java/jdk@1.7.0.7-0.175.1.0.0.24.0                 solaris
pkg:/gnome/accessibility/gnome-a11y-libs@2.30.0-0.175.1.0.0.21.0 solaris
pkg:/library/java/commons-collections@3.2.1-0.175.0.0.0.0.0      solaris
pkg:/library/java/demo64@0.5.11-0.151.0.1                        solaris
pkg:/library/java/demo@0.5.11-0.151.0.1                          solaris
pkg:/library/java/host-config@0.5.11-0.151.0.1                   solaris
pkg:/library/java/java-demo-6@1.6.0.35-0.175.1.0.0.24.1          solaris
pkg:/library/java/java-demo-7@1.7.0.7-0.175.1.0.0.24.0           solaris
pkg:/library/java/java-demo@1.6.0.35-0.175.1.0.0.24.1            solaris
pkg:/library/java/java-demo@1.7.0.21-0.175.1.7.0.4.0             solaris
pkg:/library/java/java-demo@1.7.0.25-0.175.1.9.0.3.0             solaris
pkg:/library/java/java-demo@1.7.0.45-0.175.1.12.0.5.0            solaris
pkg:/library/java/java-demo@1.7.0.5-0.175.0.10.1.0.0             solaris
pkg:/library/java/java-demo@1.7.0.51.13                          solaris
pkg:/library/java/java-demo@1.7.0.7-0.175.1.0.0.24.0             solaris
pkg:/library/java/javahelp@0.5.11-0.151.0.1                      solaris
pkg:/library/java/javahelp@0.5.11-0.175.0.0.0.2.0                solaris
pkg:/library/java/javahelp@0.5.11-0.175.1.0.0.11.0               solaris
pkg:/library/java/manual/locale/ja@0.5.11-0.151.0.1              solaris
pkg:/library/java/manual@0.5.11-0.151.0.1                        solaris
pkg:/runtime/java/jre-6@1.6.0.35-0.175.1.0.0.24.1                solaris
pkg:/runtime/java/jre-7@1.7.0.7-0.175.1.0.0.24.0                 solaris
pkg:/runtime/java/runtime64@0.5.11-0.151.0.1                     solaris

root@solaris:/opt/jdk7# pkg install  --accept pkg:/developer/java/jdk-7
           Packages to install:  3
       Create boot environment: No
Create backup boot environment: No

Planning linked: 0/1 done; 1 working: zone:testzone
Planning linked: 1/1 done
DOWNLOAD                                PKGS         FILES    XFER (MB)   SPEED
Completed                                3/3       699/699    42.8/42.8  172k/s

Downloading linked: 0/1 done; 1 working: zone:testzone
Downloading linked: 1/1 done
PHASE                                          ITEMS
Installing new actions                       856/856
Updating package state database                 Done
Updating image state                            Done
Creating fast lookup database                   Done
Executing linked: 0/1 done; 1 working: zone:testzone
Executing linked: 1/1 done

Quick way to configure Statis IP in Virtual-box Solaris 11 Guest

root@solaris:~# ipadm show-if
IFNAME     CLASS    STATE    ACTIVE OVER
lo0        loopback ok       yes    --
net0       ip       ok       yes    --

root@solaris:~# ipadm delete-ip net0

root@solaris:~# ipadm show-if
IFNAME     CLASS    STATE    ACTIVE OVER
lo0        loopback ok       yes    --


root@solaris:~# ipadm create-ip net0

root@solaris:~# ipadm create-addr -T static -a 192.168.6.101/24 net0

root@solaris:~# ipadm show-if
IFNAME     CLASS    STATE    ACTIVE OVER
lo0        loopback ok       yes    --
net0       ip       ok       yes    --

root@solaris:~# ipadm show-addr
ADDROBJ           TYPE     STATE        ADDR
lo0/v4            static   ok           127.0.0.1/8
net0/v4           static   ok           192.168.6.101/24
lo0/v6            static   ok           ::1/128



Configure default gateway

root@solaris112:~# route -p show
No persistent routes are defined
root@solaris112:~# route -p add default 192.168.6.2
add net default: gateway 192.168.6.2: entry exists
add persistent net default: gateway 192.168.6.2
root@solaris112:~# route -p add -net 192.168.6.0 -gateway 192.168.6.2
add net 192.168.6.0: gateway 192.168.6.2: entry exists
add persistent net 192.168.6.0: gateway 192.168.6.2
root@solaris112:~# route -p show
persistent: route add default 192.168.6.2
persistent: route add -net 192.168.6.0 -gateway 192.168.6.2

Configure DNS Client

root@solaris112:~# svccfg  -s "dns/client" setprop 'config/nameserver = net_address: ( 192.168.6.2 )'
root@solaris112:~# svccfg  -s "dns/client" setprop 'config/domain = astring: ("dbaglobe.com")'
root@solaris112:~# svccfg -s "name-service/switch" setprop 'config/host = astring: "files dns"'
root@solaris112:~# svccfg  -s "dns/client" listprop 'config/nameserver'
config/nameserver net_address 192.168.6.2
root@solaris112:~# svccfg  -s "dns/client" listprop 'config/domain'
config/domain astring     dbaglobe.com
root@solaris112:~# svccfg  -s "name-service/switch" listprop 'config/nameserver'
root@solaris112:~# svccfg  -s "name-service/switch" listprop 'config/host'
config/host astring     "files dns"
root@solaris112:~# svcadm refresh name-service/switch
root@solaris112:~# svcadm enable dns/client


Saturday, February 1, 2014

Use sys.dm_io_pending_io_requests MV to check IO contention

use
go

select vfs.database_id,
  df.name,
  df.physical_name,
  vfs.file_id,
  ior.io_pending,
  ior.io_handle,
  ior.io_type
from sys.dm_io_pending_io_requests ior
right outer join sys.dm_io_virtual_file_stats(db_id(),null) vfs
on (vfs.file_handle=ior.io_handle)
inner join sys.database_files df on (df.file_id=vfs.file_id)
go

Retrieve SQL Server Wait Status based on sys.dm_os_wait_stats

use master
go

-- create begin snapshot
select * into #dm_os_wait_stats_b from sys.dm_os_wait_stats
go

-- wait for 300 seconds
WAITFOR DELAY '00:05:00'
go

-- create end snapshot
select * into #dm_os_wait_stats_e from sys.dm_os_wait_stats
go

-- retrieve top 50 wait events
select top 50 wait_type,
  delta_waiting_tasks_count,
  delta_wait_time_ms,
  max_wait_time_ms,
  delta_signal_wait_time_ms
from
(
select e.wait_type,
 e.waiting_tasks_count-b.waiting_tasks_count delta_waiting_tasks_count,
 e.wait_time_ms-b.wait_time_ms delta_wait_time_ms,
 e.max_wait_time_ms,
 e.signal_wait_time_ms-b.signal_wait_time_ms delta_signal_wait_time_ms
from #dm_os_wait_stats_b b join #dm_os_wait_stats_e e
 on b.wait_type=e.wait_type
) delta
    where wait_type not in
(
 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
      'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
      'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','XE_TIMER_EVENT'
) -- filter out additional irrelevant waits
and delta_wait_time_ms>0
order by delta_wait_time_ms desc;


-- clean up
drop table #dm_os_wait_stats_b
go

drop table #dm_os_wait_stats_e
go

SQL Server Wait Type

Wait type

Description

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:

  • Disable garbage collection (used by backup and restore).
  • 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:

  • Temporarily disable FILESTREAM log (FSLOG) truncation (used by backup and restore).
  • 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_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.

clip_image001Note

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.

clip_image001[1]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 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 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 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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_IS

Occurs when a task is waiting to acquire an Intent Shared (IS) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_IU

Occurs when a task is waiting to acquire an Intent Update (IU) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_IX

Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_S

Occurs when a task is waiting to acquire a Shared lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_SCH_M

Occurs when a task is waiting to acquire a Schema Modify lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_SCH_S

Occurs when a task is waiting to acquire a Schema Share lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_SIU

Occurs when a task is waiting to acquire a Shared With Intent Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_SIX

Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_U

Occurs when a task is waiting to acquire an Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_UIX

Occurs when a task is waiting to acquire an Update With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

LCK_M_X

Occurs when a task is waiting to acquire an Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).

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. Internal use only.

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.

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.

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.

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.