SQLSERVER：PREEMPTIVE

WBOY

發布： 2016-06-07 15:45:25

原創

1167 人瀏覽過

SQLSERVER：PREEMPTIVE_OS_GETPROCADDRESS等待类型的困惑翻译自： http://troubleshootingsql.com/2011/07/20/preemptive_os_getprocaddress-are-you-confusing-me/ PREEMPTIVE_OS_GETPROCADDRESS 等待类型在SQLSERVER2008里是一个新的等待类型这个等待类

SQLSERVER：PREEMPTIVE_OS_GETPROCADDRESS等待类型的困惑

翻译自：http://troubleshootingsql.com/2011/07/20/preemptive_os_getprocaddress-are-you-confusing-me/

PREEMPTIVE_OS_GETPROCADDRESS等待类型在SQLSERVER2008里是一个新的等待类型

这个等待类型的作用是当GetProcAddress跟踪SQLSERVER实例调用扩展存储过程的时候加载DLL的入口点所花费的时间

然而，这个等待类型跟踪这个等待时间的方式有一些问题，这个问题在Rob Dorr的博客里有所记载

根据Twitter上面一个帖子的讨论，我发现了可以证明这个问题的一个例子

我使用下面的命令去列出我的C盘下面的子目录：

<span>1</span> <span>exec</span> master..xp_dirtree <span>'</span><span>C:\</span><span>'</span>

登入後複製

SQLSERVER：PREEMPTIVE

然后，我使用下面的TSQL脚本去监视系统中执行这个扩展存储过程的那个会话的等待信息，这个TSQL脚本每隔一秒执行一次

<span> 1</span> <span>SET</span> NOCOUNT <span>ON</span>
<span> 2</span> <span>WHILE</span> (<span>1</span><span>=</span><span>1</span><span>)
</span><span> 3</span> <span>BEGIN</span>
<span> 4</span> <span>SELECT</span> <span>[</span><span>session_id</span><span>]</span>, <span>[</span><span>status</span><span>]</span>, <span>[</span><span>command</span><span>]</span>, <span>[</span><span>start_time</span><span>]</span>, <span>[</span><span>cpu_time</span><span>]</span>, <span>[</span><span>reads</span><span>]</span>, <span>[</span><span>writes</span><span>]</span>, <span>[</span><span>wait_resource</span><span>]</span>, <span>[</span><span>wait_time</span><span>]</span>, <span>[</span><span>last_wait_type</span><span>]</span>, <span>[</span><span>wait_type</span><span>]</span>
<span> 5</span> <span>FROM</span><span> sys.dm_exec_requests
</span><span> 6</span> <span>WHERE</span> <span>[</span><span>session_id</span><span>]</span> <span>=</span> <span>53</span> <span>--</span><span> Session ID that is executing the Extended Stored Procedure</span>
<span> 7</span>  
<span> 8</span> <span>WAITFOR</span> DELAY <span>'</span><span>00:00:01</span><span>'</span>
<span> 9</span> <span>RAISERROR</span> (<span>''</span>, <span>10</span>, <span>1</span>, N<span>'</span><span>Waits</span><span>'</span><span>);
</span><span>10</span>  
<span>11</span> <span>END</span>

登入後複製

按CTRL+T然后执行上面的SQL脚本，把结果粘贴出来

SQLSERVER：PREEMPTIVE