PREEMPTIVE_OS_GETPROCADDRESS is a new wait type that was introduced in SQL Server 2008 to track time taken by GetProcAddress to load the entrypoint in the DLL when Extended Stored Procedure calls are made by the SQL Server instance. However, the way the wait time is tracked by this wait type has a little quirk which Rob Dorr had documented in a blog post.

Based on a discussion on #sqlhelp thread on Twitter, I found the need to demonstrate this unique quirk with the help of an example. I am using the following command to list you the sub-directories on my C: drive using the command:

exec master..xp_dirtree ‘C:\’

Next I monitored the wait statistics for the session executing this Extended Stored procedure using the T-SQL script below with a 1 second delay:

SET NOCOUNT ON
WHILE (1=1)
BEGIN
SELECT [session_id], [status], [command], [start_time], [cpu_time], [reads], [writes], [wait_resource], [wait_time], [last_wait_type], [wait_type]
FROM sys.dm_exec_requests
WHERE [session_id] = 53 -- Session ID that is executing the Extended Stored Procedure

WAITFOR DELAY '00:00:01'
RAISERROR ('', 10, 1, N'Waits');

END

The output of this script was as follows:

session_id status        command          start_time              cpu_time    reads    writes      wait_time   last_wait_type                        wait_type

53         runnable      EXECUTE          2011-07-20 00:59:44.330 62          0        0           712         PREEMPTIVE_OS_GETPROCADDRESS          PREEMPTIVE_OS_GETPROCADDRESS

53         running       EXECUTE          2011-07-20 00:59:44.330 234         0        0           1713        PREEMPTIVE_OS_GETPROCADDRESS          PREEMPTIVE_OS_GETPROCADDRESS

53         runnable      EXECUTE          2011-07-20 00:59:44.330 452         0        0           2714        PREEMPTIVE_OS_GETPROCADDRESS          PREEMPTIVE_OS_GETPROCADDRESS

As you can see above the wait time keeps increasing even though I can see that the session is performing work due to the following results:
a. The results window in SSMS for the session executing the extended stored procedure was churning out results
b. Using a Process Monitor, I could trace the filesystem activity on the C: drive for the SQL Server process

You could run into this issue while using any Extended Stored Procedure (system or user) for a SQL Server 2008 or SQL Server 2008 R2 instance. Since I know that the session is performing work, the only two other things that I would check if there are resource bottlenecks w.r.t. memory or I/O if some filesystem related activity is being performed by the extended stored procedure. Most of the issues where I have see this wait type causing confusion is when the extended stored procedure is performing disk related activity. At that time, Process Monitor will put your doubts to rest along with the information that I mentioned above to confirm that that the procedure is actually doing some work and not stuck or perceived as “hung”.