PREEMPTIVE_OS_GETPROCADDRESS and xp_create_subdir
Here is a discovery that I made using the Database Health Monitor historic wait monitoring, on a server with slow storage where the backups were being written.
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.
When this occurs it clogs things up temporarily and causes other parts of your database to block waiting for this wait to clear.
Why are you using USB attached storage for your backups? Well it was a creative solution, by someone who didn’t understand the need for I/O.
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.
For the PREEMPTIVE_OS_ wait types, if you remove the PREEMPTIVE_OS_, you are then left with the Windows API function call that is causing the wait.
Fixing PREEMPTIVE_OS_GETPROCADDRESS and xp_create_subdir
Solution – don’t use USB attached storage for your backups (or any part of your SQL Server), if you are concerned about performance.
Alternate solution, first write your backups to faster storage, then have another job that copies the backups to the slower USB based storage if that is your only option.
To track PREEMPTIVE_OS_GETPROCADDRESS and other wait types on SQL Server, try out the Database Health Monitor, a free monitoring solution that I have created for the SQL Server community.
Related Links
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply