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.



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.



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.


Related Links