Why does PREEMPTIVE_OS_GETPROCADDRESS Show a Large Accumulation?

Published Jan 15 2019 01:09 PM 618 Views
Microsoft
First published on MSDN on Jul 21, 2010

There is a bug in SQL Server 2008 that causes PREEMPTIVE_OS_GETPROCADDRESS to include and accumulate the execution time of the extended stored procedure (XPROC). The following is an example showing the increase in the GetProcAddress wait time.

select * from sys.dm_os_wait_stats where wait_type = 'PREEMPTIVE_OS_GETPROCADDRESS' or wait_type = 'MSQL_XP'
exec master..xp_dirtree 'f:'
select * from sys.dm_os_wait_stats where wait_type = 'PREEMPTIVE_OS_GETPROCADDRESS' or wait_type = 'MSQL_XP'

GetProcAddress is used to load the entrypoint in the DLL (XPROC) and should complete quickly but due to the accumulation bug the wait time is inflated.   To get a better idea (ballpark) of how long GetProcAddress really takes you can using the following query.

declare @WaitTime bigint
select @WaitTime = wait_time_ms from sys.dm_os_wait_stats where wait_type = 'MSQL_XP'
select @WaitTime - wait_time_ms from sys.dm_os_wait_stats where wait_type = 'PREEMPTIVE_OS_GETPROCADDRESS'

Bob Dorr - Principal SQL Server Escalation Engineer


%3CLINGO-SUB%20id%3D%22lingo-sub-316175%22%20slang%3D%22en-US%22%3EWhy%20does%20PREEMPTIVE_OS_GETPROCADDRESS%20Show%20a%20Large%20Accumulation%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-316175%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jul%2021%2C%202010%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EThere%20is%20a%20bug%20in%20SQL%20Server%202008%20that%20causes%20PREEMPTIVE_OS_GETPROCADDRESS%20to%20include%20and%20accumulate%20the%20execution%20time%20of%20the%20extended%20stored%20procedure%20(XPROC).%20The%20following%20is%20an%20example%20showing%20the%20increase%20in%20the%20GetProcAddress%20wait%20time.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3Eselect%20*%20from%20sys.dm_os_wait_stats%20where%20wait_type%20%3D%20'PREEMPTIVE_OS_GETPROCADDRESS'%20or%20wait_type%20%3D%20'MSQL_XP'%20%3CBR%20%2F%3E%20exec%20master..xp_dirtree%20'f%3A'%20%3CBR%20%2F%3E%20select%20*%20from%20sys.dm_os_wait_stats%20where%20wait_type%20%3D%20'PREEMPTIVE_OS_GETPROCADDRESS'%20or%20wait_type%20%3D%20'MSQL_XP'%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EGetProcAddress%20is%20used%20to%20load%20the%20entrypoint%20in%20the%20DLL%20(XPROC)%20and%20should%20complete%20quickly%20but%20due%20to%20the%20accumulation%20bug%20the%20wait%20time%20is%20inflated.%26nbsp%3B%26nbsp%3B%20To%20get%20a%20better%20idea%20(ballpark)%20of%20how%20long%20GetProcAddress%20really%20takes%20you%20can%20using%20the%20following%20query.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3Edeclare%20%40WaitTime%20bigint%20%3CBR%20%2F%3E%20select%20%40WaitTime%20%3D%20wait_time_ms%20from%20sys.dm_os_wait_stats%20where%20wait_type%20%3D%20'MSQL_XP'%20%3CBR%20%2F%3E%20select%20%40WaitTime%20-%20wait_time_ms%20from%20sys.dm_os_wait_stats%20where%20wait_type%20%3D%20'PREEMPTIVE_OS_GETPROCADDRESS'%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EBob%20Dorr%20-%20Principal%20SQL%20Server%20Escalation%20Engineer%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-316175%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jul%2021%2C%202010%20There%20is%20a%20bug%20in%20SQL%20Server%202008%20that%20causes%20PREEMPTIVE_OS_GETPROCADDRESS%20to%20include%20and%20accumulate%20the%20execution%20time%20of%20the%20extended%20stored%20procedure%20(XPROC).%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-316175%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQL%20OS%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 01:09 PM
Updated by: