PREEMPTIVE_OS_GETPROCADDRESS wait type

The PREEMPTIVE_OS_GETPROCADDRESS wait type occurs when SQL Server is making a call to the operating system for something like xp_create_subdir, or other xp_ type procedures.

When this occurs it clogs things up temporarily and causes other parts of your database to block waiting for this wait to clear.

PREEMPTIVE_OS_GETPROCADDRESS with xp_create_subdir

If you are seeing excessive waits on the PREEMPTIVE_OS_GETPROCADDRESS wait type and xp_create_subdir is the command with the wait, and this is occurring at the time your backups are being run, it is a symptom that the storage location for your backups is having I/O difficulties.

I noticed this on a server with an external USB 2.0 attached hard drive that was being used for backups, and on a second server with a USB 3.0 external hard drive. When the backups run, there was a wait for the process to attempt to see if the backup directory exists, and to create it if it did not.

PREEMPTIVE_OS_GETPROCADDRESS with xp_delete_file

One cause of excessive PREEMPTIVE_OS_GETPROCADDRESS with xp_delete_file is a huge amount of files in your backup directory. Lets say you have transaction log backups every minute, with 30 databases, and you keep the log files around for 5 days. Do the math, and that comes out to about 216,000 files in a single directory. If you use the “create a subdirectory for each database” option on the backup, you can reduce the number of files that exist in a single directory.

Windows file access get really slow when there are more than tens of thousands of files in a single directory. This wait is caused by the OS waiting to find the file to perform the delete.

PREEMPTIVE_OS_GETPROCADDRESS with xp_create_subdir

PREEMPTIVE_OS_GETPROCADDRESS with xp_delete_file

Related Links