(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
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:
Removed in SQL Server version:
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.
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):