PREEMPTIVE_OS_GETPROCADDRESS问题 [英] PREEMPTIVE_OS_GETPROCADDRESS issue

问题描述

我们的SQL SERVER 2008 R2上有大量的PREEMPTIVE等待（PREEMPTIVE_OS_GETPROCADDRESS）。这是服务器上最主要的等待（除了MSQL_XP等待），根本原因是因为我们运行MDSTC，因为我们的大多数应用程序使用（和滥用）
的分布式事务。它甚至可以，但主要问题是几乎每周（每7-8天）我们需要重新启动Sql Server，因为突然，PREEMPTIVE等待服务器淹没。当发生这种情况时，我注意到在开始状态下有大量的XA事务
，例如这个查询：

We have plenty of PREEMPTIVE waits (PREEMPTIVE_OS_GETPROCADDRESS exactly) on our SQL SERVER 2008 R2. This is the most predominant wait on server (besides MSQL_XP wait), the root cause is because we have MDSTC running as most of our applications use (and abuse) of distributed transactions. It would be even ok, but the main problem is that almost every week (every 7-8 days) we need to restart Sql Server as, suddenly, PREEMPTIVE waits overwhelm the server. When this happens I noticed an abnormous number of XA transaction in starting status, for example this query:

SELECT sqltext.TEXT，COUNT（* ）[Tot] FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text（sql_handle）AS sqltext WHERE sqltext.TEXT喜欢'xp_sqljdbc_xa_s％'group by sqltext.TEXT - with汇总顺序[Tot] desc go

SELECT sqltext.TEXT, COUNT(*) [Tot] FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext WHERE sqltext.TEXT like 'xp_sqljdbc_xa_s%' group by sqltext.TEXT --with rollup order by [Tot] desc go

通常它会返回1-50行，但是当我们需要重新启动SQL时，它会返回300-350行甚至更多行。除了当前的线程，还有恐慌系统：

normally it returns 1-50 rows but when we need to restart SQL it returns 300-350 rows and more. Besides current Threads, while the panic system:

从sys.dm_os_Schedulers中选择sum（active_Workers_count）为'CurrentThreads'，其中status ='VISIBLE ONLINE'

select sum(active_Workers_count) as 'CurrentThreads' from sys.dm_os_Schedulers where status='VISIBLE ONLINE'

在sql server start上由系统分配的832总计最多700/750。通常，当前线程是< = 150。

up to 700/750 on 832 total assigned by system on sql server start. Normally, current threads is <=150.

我再说一遍，所有这些情况都会发生  突然，绝对没有预警，只需
或多或少七天我们找到的唯一解决方案是（唉）重启Sql Server。

I repeat, all this scenario happens suddenly, there are absolutely no forewarnings, just more or less seven days the only solution we found is to (alas) restart Sql Server.

这是我们目前的配置：

This is our currently configuration:

• 
  逻辑CPU数量：24
• 
  超线程比率：12
• 
  物理CPU数量：2
• 
  物理内存（MB）：131004
• 
  affinity_type_desc：AUTO

到目前为止，系统已经启动并运行了6天，但我预计在2-3天内我必须重新启动，唉（（

Just now the system is up & running fine by 6 days by now but I expect that in 2-3 days I have to restart, alas ((

任何建议都会非常受欢迎。

Any suggestion would be really appreciated guys.

祝你好运

推荐答案

select @@ version的输出是什么。你使用了很多扩展存储过程吗？

What is output of select @@version. Are you using lot of extended stored procs ?

这篇关于PREEMPTIVE_OS_GETPROCADDRESS问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！