(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
Description:
This wait type is when a thread is calling the Windows CreateFile function. Note that this API is also used to open files for reading, not just for creating files.
(Books Online description: N/A)
Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.
Added in SQL Server version:
2008
Removed in SQL Server version:
N/A
Extended Events wait_type value:
The map_key value in sys.dm_xe_map_values is 446 in 2008 and 2008 R2, 494 in 2012, and 510 in 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.
Other information:
This wait type is usually not a problem unless the workload involves creating many files, for instance, from using FILESTREAM storage. There are many posts on the Internet about performance issues that can happen in Windows that could make calls to CreateFile slow, including SMB, authentication failures, slow I/O subsystem, NTFS having to flush its caches, and none of these are SQL Server issues. Note that this API is also used to open files for reading, not just for creating files.
In the case of FILESTREAM, if this wait type is prevalent, and the average wait time is increasing, that indicates that the NTFS volume where the FILESTREAM data container is placed has not been prepared correctly for use with FILESTREAM. More specifically, it indicates that the NTFS algorithm to generate 8.3 Win16-compatible names has not been disabled. This is explained in more detail in the whitepaper I wrote: FILESTREAM Storage in SQL Server 2008.
8/10/19: A client found another case where this happens: using custom code in a DLL that gets unloaded and reloaded excessively because of memory pressure, plus a slow I/O subsystem, resulting in this wait being the top one on the instance.
Note that when a thread calls out to Windows, the thread changes from non-preemptive (SQL Server controls the thread) to preemptive (Windows controls the thread) mode. The thread’s state will be listed as RUNNING, as SQL Server doesn’t know what Windows is doing with the thread.
Known occurrences in SQL Server (list number matches call stack list):
- Creating a FILESTREAM file
- Creating a backup file
- Creating an Extended Events target file
- Creating a new database file
And other similar stacks.
Abbreviated call stacks (list number matches known occurrences list):
- SOS_Task::PopWait+0xb1
AutoSwitchPreemptive::~AutoSwitchPreemptive+0x95
FsDoHandler::NtCreateFileWrapper+0x1fb
FsDoHandler::CreateNewFile+0x8d4
FsDoHandler::CreateFileDownLevel+0x72
FsDoHandler::CreateFileW+0x8b
FilestreamManager::DoDelayedOpenOrCreate+0x14e
FilestreamManager::OpenOrCreateInternal+0x1b0
FilestreamManager::OpenOrCreateFilestream+0x236
FilestreamManager::Copy+0x6a
PrepareIlbForSetData+0x1e0
LongRecord::PrepareNewColumns+0x9e4
LongRecord::SetDataInternal+0x28e
LongRecord::SetData+0x2ba
HeapDataSetSession::InsertRowInternal+0x264d
DatasetSession::InsertRow+0x163
RowsetNewSS::InsertRow+0x26
CValRowMult::SetDataX+0x194
CQScanScalarInsertNew::GetRow+0x94
CQueryScan::GetRow+0x81
CXStmtQuery::ErsqExecuteQuery+0x36d</value> - SOS_Task::PopWait+b1
AutoSwitchPreemptive::~AutoSwitchPreemptive+95
DBCreateFileW+2d3
BackupDiskFile::OpenMedia+1ff
BackupMediaIo::Open+3f
BackupMedium::Open+32
BackupStream::OpenForBackup+27
BackupStream::ThreadMainRoutine+160
BackupThread::ThreadBase+51
SubprocEntrypoint+a7f
SOS_Task::Param::Execute+21e
SOS_Scheduler::RunTask+ab - SOS_Task::PopWait+b1
AutoSwitchPreemptive::~AutoSwitchPreemptive+95
DBCreateFileW+2d3
XE_Engine::FileCreate+35
XE_FileMemoryMap::Create+c7
XE_FileSetMemoryMap::Create+e6
XE_FileReader<XE_FileReaderDefaultPolicy>::Initialize+4e
XE_LogDefaultMetadataDecoder::Initialize+3a1
XE_FileProcessor::Create+82
XE_FileProcessor::CreateDefault<XE_LogDefaultMetadataDecoder>+10a
XE_FileTargetTVF::InternalInit+246
TTableBase<XE_FileTargetTVFInfo>::Init+81
IStreamTVF::Init+2d
CQScanTVFStreamNew::Open+58d
CQueryScan::StartupQuery+23c
CXStmtQuery::SetupQueryScanAndExpression+2e5
CXStmtQuery::InitForExecute+34
CXStmtQuery::ErsqExecuteQuery+36d
CXStmtSelect::XretExecute+2e7
CMsqlExecContext::ExecuteStmts<1,1>+427 - SOS_Task::PopWait+b1
AutoSwitchPreemptive::~AutoSwitchPreemptive+95
DBCreateFileW+2d3
FCB::CreatePhysicalFile+446
FileMgr::CreateNewFile+d9
AsynchronousDiskAction::ExecuteDeferredAction+8f
AsynchronousDiskWorker::ThreadRoutine+15c
SubprocEntrypoint+a7f
SOS_Task::Param::Execute+21e
SOS_Scheduler::RunTask+ab