Great Articles from WWW by SQLTop

SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : http://sqlblogcasts.com/blogs/christian/archive/2008/06/06/new-sql-server-2008-wait-types-preemptive-and-ft.aspx

New SQL Server 2008 wait types - PREEMPTIVE_* and FT_*

Published 06 June 08 01:05 PM | Christian

I’m a big fan of using SQL Server waits to troubleshoot performance problems so I was pleasantly surprised to see that there are 475 possible wait types in SQL Server 2008 as opposed to 230 in SQL Server 2005. 

The first interesting ones I’ve looked at are the new PREEMPTIVE wait types.  Any code that needs to execute outside SQL Server has to go outside the control of SQL Server’s co-operative (or non-preemptive) scheduler and will use the preemptive scheduling model used by the OS.

Typically these external executions would be very difficult to troubleshoot using wait types because they would either come under a single wait like OLEDB for example or wouldn’t be tracked at all like OS level functions.  If you’ve ever tried to troubleshoot a SQL Server issue that turned out to be latency talking to the domain controller you’ll be presently surprised with the PREEMPTIVE_OS wait types that will show authentication waits.

I was looking to see if there were any PREEMPTIVE waits for Full-Text Search as they just show up as OLDEDB waits in previous versions but now that FTS is “integrated” with SQL Server (iFTS) there is no dependency on mssearch so we can have native SQL Server waits.  There are 7 new FTS related wait types:

FT_COMPROWSET_RWLOCK
FT_IFTS_RWLOCK
FT_IFTSHC_MUTEX
FT_IFTSISM_MUTEX
FT_MASTER_MERGE
FT_METADATA_MUTEX
FT_RESTART_CRAWL

They haven’t made it into BOL yet (in Feb CTP anyway) so here is the rather long list of PREEMPTIVE wait types:

PREEMPTIVE_ABR
PREEMPTIVE_AUDIT_ACCESS_EVENTLOG
PREEMPTIVE_AUDIT_ACCESS_SECLOG
PREEMPTIVE_CLOSEBACKUPMEDIA
PREEMPTIVE_CLOSEBACKUPTAPE
PREEMPTIVE_CLOSEBACKUPVDIDEVICE
PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL
PREEMPTIVE_COM_COCREATEINSTANCE
PREEMPTIVE_COM_COGETCLASSOBJECT
PREEMPTIVE_COM_CREATEACCESSOR
PREEMPTIVE_COM_DELETEROWS
PREEMPTIVE_COM_GETCOMMANDTEXT
PREEMPTIVE_COM_GETDATA
PREEMPTIVE_COM_GETNEXTROWS
PREEMPTIVE_COM_GETRESULT
PREEMPTIVE_COM_GETROWSBYBOOKMARK
PREEMPTIVE_COM_LBFLUSH
PREEMPTIVE_COM_LBLOCKREGION
PREEMPTIVE_COM_LBREADAT
PREEMPTIVE_COM_LBSETSIZE
PREEMPTIVE_COM_LBSTAT
PREEMPTIVE_COM_LBUNLOCKREGION
PREEMPTIVE_COM_LBWRITEAT
PREEMPTIVE_COM_QUERYINTERFACE
PREEMPTIVE_COM_RELEASE
PREEMPTIVE_COM_RELEASEACCESSOR
PREEMPTIVE_COM_RELEASEROWS
PREEMPTIVE_COM_RELEASESESSION
PREEMPTIVE_COM_RESTARTPOSITION
PREEMPTIVE_COM_SEQSTRMREAD
PREEMPTIVE_COM_SEQSTRMREADANDWRITE
PREEMPTIVE_COM_SETDATAFAILURE
PREEMPTIVE_COM_SETPARAMETERINFO
PREEMPTIVE_COM_SETPARAMETERPROPERTIES
PREEMPTIVE_COM_STRMLOCKREGION
PREEMPTIVE_COM_STRMSEEKANDREAD
PREEMPTIVE_COM_STRMSEEKANDWRITE
PREEMPTIVE_COM_STRMSETSIZE
PREEMPTIVE_COM_STRMSTAT
PREEMPTIVE_COM_STRMUNLOCKREGION
PREEMPTIVE_CONSOLEWRITE
PREEMPTIVE_CREATEPARAM
PREEMPTIVE_DEBUG
PREEMPTIVE_DFSADDLINK
PREEMPTIVE_DFSLINKEXISTCHECK
PREEMPTIVE_DFSLINKHEALTHCHECK
PREEMPTIVE_DFSREMOVELINK
PREEMPTIVE_DFSREMOVEROOT
PREEMPTIVE_DFSROOTFOLDERCHECK
PREEMPTIVE_DFSROOTINIT
PREEMPTIVE_DFSROOTSHARECHECK
PREEMPTIVE_DTC_ABORT
PREEMPTIVE_DTC_ABORTREQUESTDONE
PREEMPTIVE_DTC_BEGINTRANSACTION
PREEMPTIVE_DTC_COMMITREQUESTDONE
PREEMPTIVE_DTC_ENLIST
PREEMPTIVE_DTC_PREPAREREQUESTDONE
PREEMPTIVE_FILESIZEGET
PREEMPTIVE_FSAOLEDB_ABORTTRANSACTION
PREEMPTIVE_FSAOLEDB_COMMITTRANSACTION
PREEMPTIVE_FSAOLEDB_STARTTRANSACTION
PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO
PREEMPTIVE_GETRMINFO
PREEMPTIVE_LOCKMONITOR
PREEMPTIVE_MSS_RELEASE
PREEMPTIVE_ODBCOPS
PREEMPTIVE_OLE_UNINIT
PREEMPTIVE_OLEDB_ABORTORCOMMITTRAN
PREEMPTIVE_OLEDB_ABORTTRAN
PREEMPTIVE_OLEDB_GETDATASOURCE
PREEMPTIVE_OLEDB_GETLITERALINFO
PREEMPTIVE_OLEDB_GETPROPERTIES
PREEMPTIVE_OLEDB_GETPROPERTYINFO
PREEMPTIVE_OLEDB_GETSCHEMALOCK
PREEMPTIVE_OLEDB_JOINTRANSACTION
PREEMPTIVE_OLEDB_RELEASE
PREEMPTIVE_OLEDB_SETPROPERTIES
PREEMPTIVE_OLEDBOPS
PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT
PREEMPTIVE_OS_ACQUIRECREDENTIALSHANDLE
PREEMPTIVE_OS_AUTHENTICATIONOPS
PREEMPTIVE_OS_AUTHORIZATIONOPS
PREEMPTIVE_OS_AUTHZGETINFORMATIONFROMCONTEXT
PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID
PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER
PREEMPTIVE_OS_BACKUPREAD
PREEMPTIVE_OS_CLOSEHANDLE
PREEMPTIVE_OS_CLUSTEROPS
PREEMPTIVE_OS_COMOPS
PREEMPTIVE_OS_COMPLETEAUTHTOKEN
PREEMPTIVE_OS_COPYFILE
PREEMPTIVE_OS_CREATEDIRECTORY
PREEMPTIVE_OS_CREATEFILE
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT
PREEMPTIVE_OS_CRYPTIMPORTKEY
PREEMPTIVE_OS_CRYPTOPS
PREEMPTIVE_OS_DECRYPTMESSAGE
PREEMPTIVE_OS_DELETEFILE
PREEMPTIVE_OS_DELETESECURITYCONTEXT
PREEMPTIVE_OS_DEVICEIOCONTROL
PREEMPTIVE_OS_DEVICEOPS
PREEMPTIVE_OS_DIRSVC_NETWORKOPS
PREEMPTIVE_OS_DISCONNECTNAMEDPIPE
PREEMPTIVE_OS_DOMAINSERVICESOPS
PREEMPTIVE_OS_DSGETDCNAME
PREEMPTIVE_OS_DTCOPS
PREEMPTIVE_OS_ENCRYPTMESSAGE
PREEMPTIVE_OS_FILEOPS
PREEMPTIVE_OS_FINDFILE
PREEMPTIVE_OS_FLUSHFILEBUFFERS
PREEMPTIVE_OS_FORMATMESSAGE
PREEMPTIVE_OS_FREECREDENTIALSHANDLE
PREEMPTIVE_OS_FREELIBRARY
PREEMPTIVE_OS_GENERICOPS
PREEMPTIVE_OS_GETADDRINFO
PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE
PREEMPTIVE_OS_GETDISKFREESPACE
PREEMPTIVE_OS_GETFILEATTRIBUTES
PREEMPTIVE_OS_GETFILESIZE
PREEMPTIVE_OS_GETLONGPATHNAME
PREEMPTIVE_OS_GETPROCADDRESS
PREEMPTIVE_OS_GETVOLUMENAMEFORVOLUMEMOUNTPOINT
PREEMPTIVE_OS_GETVOLUMEPATHNAME
PREEMPTIVE_OS_INITIALIZESECURITYCONTEXT
PREEMPTIVE_OS_LIBRARYOPS
PREEMPTIVE_OS_LOADLIBRARY
PREEMPTIVE_OS_LOGONUSER
PREEMPTIVE_OS_LOOKUPACCOUNTSID
PREEMPTIVE_OS_MESSAGEQUEUEOPS
PREEMPTIVE_OS_MOVEFILE
PREEMPTIVE_OS_NETGROUPGETUSERS
PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS
PREEMPTIVE_OS_NETUSERGETGROUPS
PREEMPTIVE_OS_NETUSERGETLOCALGROUPS
PREEMPTIVE_OS_NETUSERMODALSGET
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE
PREEMPTIVE_OS_OPENDIRECTORY
PREEMPTIVE_OS_PIPEOPS
PREEMPTIVE_OS_PROCESSOPS
PREEMPTIVE_OS_QUERYREGISTRY
PREEMPTIVE_OS_QUERYSECURITYCONTEXTTOKEN
PREEMPTIVE_OS_REMOVEDIRECTORY
PREEMPTIVE_OS_REPORTEVENT
PREEMPTIVE_OS_REVERTTOSELF
PREEMPTIVE_OS_RSFXDEVICEOPS
PREEMPTIVE_OS_SECURITYOPS
PREEMPTIVE_OS_SERVICEOPS
PREEMPTIVE_OS_SETENDOFFILE
PREEMPTIVE_OS_SETFILEPOINTER
PREEMPTIVE_OS_SETFILEVALIDDATA
PREEMPTIVE_OS_SETNAMEDSECURITYINFO
PREEMPTIVE_OS_SQLCLROPS
PREEMPTIVE_OS_SQMLAUNCH
PREEMPTIVE_OS_VERIFYSIGNATURE
PREEMPTIVE_OS_VSSOPS
PREEMPTIVE_OS_WAITFORSINGLEOBJECT
PREEMPTIVE_OS_WINSOCKOPS
PREEMPTIVE_OS_WRITEFILE
PREEMPTIVE_OS_WRITEFILEGATHER
PREEMPTIVE_OS_WSASETLASTERROR
PREEMPTIVE_REENLIST
PREEMPTIVE_RESIZELOG
PREEMPTIVE_ROLLFORWARDREDO
PREEMPTIVE_ROLLFORWARDUNDO
PREEMPTIVE_SB_STOPENDPOINT
PREEMPTIVE_SERVER_STARTUP
PREEMPTIVE_SETRMINFO
PREEMPTIVE_SHAREDMEM_GETDATA
PREEMPTIVE_SNIOPEN
PREEMPTIVE_SOSHOST
PREEMPTIVE_SOSTESTING
PREEMPTIVE_STARTRM
PREEMPTIVE_STREAMFCB_CHECKPOINT
PREEMPTIVE_STREAMFCB_RECOVER
PREEMPTIVE_STRESSDRIVER
PREEMPTIVE_TESTING
PREEMPTIVE_TRANSIMPORT
PREEMPTIVE_UNMARSHALPROPAGATIONTOKEN
PREEMPTIVE_VSS_CREATESNAPSHOT
PREEMPTIVE_VSS_CREATEVOLUMESNAPSHOT
PREEMPTIVE_XE_CALLBACKEXECUTE
PREEMPTIVE_XE_DISPATCHER
PREEMPTIVE_XE_ENGINEINIT
PREEMPTIVE_XE_GETTARGETSTATE
PREEMPTIVE_XE_SESSIONCOMMIT
PREEMPTIVE_XE_TARGETFINALIZE
PREEMPTIVE_XE_TARGETINIT
PREEMPTIVE_XE_TIMERRUN
PREEMPTIVE_XETESTING

Regards, 

Christian Bolton  MCA: Database
Database Architect
http://coeo.com - The SQL Server Experts