(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

PREEMPTIVE_OS_CREATEFILE

(Back to main page…)

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.

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):

  1. Creating a FILESTREAM file
  2. Creating a backup file
  3. Creating an Extended Events target file
  4. Creating a new database file

And other similar stacks.

Abbreviated call stacks (list number matches known occurrences list):

  1. 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>
  2. 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
  3. 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
  4. 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