ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL Server 性能问题—等待RESOURCE_SEMAPHORE

SQL Server 性能问题—等待RESOURCE_SEMAPHORE

原创 Linux操作系统 作者:apgcdsd 时间:2011-08-15 22:38:03 0 删除 编辑
Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONESQL Server 性能问题等待RESOURCE_SEMAPHORE

SQL Server 两类RESOURCE_SEMAPHORE 等待事件: RESOURCE_SEMAPHORE RESOURCE_SEMAHPORE_QUERY_COMPILE.

Resource_SEMAPHORE是指一条或多条语句在执行的时候等待memory的分配。RESOURCE_SEMAHPORE_QUERY_COMPILE是指一条语句在编译的时候等待memory的情况。

问题描述,用户反应所有SQL Server有关的操作都很慢。


第一次收集性能日志的方式跟通常的性能问题是没有什么区别的:

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE

1.     手工收集系统性能日志,包含system, physical disk, process, processor, memor以及所有的SQL Server相关的计数器。

2.   收集sys.dm_exec_connections, sys.dm_exec_sessions sys.dm_exec_requests的信息(5-10秒打印一次)

3.   如果允许的话,收集profiler trace


这三个步骤要同时收集。如果有PSSDIAG 工具,可以用PSSDIAG工具代替以上的三个步骤。


接下来我们首先从步骤2收集的信息入手检查,看看数据库连接在当时的运行状况和等待事件,动态视图sys.dm_exec_requests显示的结果如下:

session_id start_time              status       command    blocking_session_id wait_type             wait_time

---------- ----------------------- ------------ ---------- ------------------- --------------------- ---------

51         2011-04-06 18:00:00.220 suspended    INSERT     0                   RESOURCE_SEMAPHORE    6156     

58         2011-04-06 18:00:06.973 suspended    INSERT     0                   RESOURCE_SEMAPHORE    265      

66         2011-04-06 18:00:03.763 running      SELECT     0                   NULL                  0        

76         2011-04-06 17:59:54.533 runnable     INSERT     0                   NULL                  0        

78         2011-04-06 18:00:01.217 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5046     

81         2011-04-06 17:59:54.523 runnable     INSERT     0                   NULL                  0        

82         2011-04-06 17:59:54.553 runnable     INSERT     0                   NULL                  0        

83         2011-04-06 17:59:56.410 suspended    SELECT     0                   RESOURCE_SEMAPHORE    15       

87         2011-04-06 17:59:56.343 runnable     INSERT     0                   NULL                  0        

89         2011-04-06 17:59:58.403 suspended    INSERT     0                   RESOURCE_SEMAPHORE    7984     

90         2011-04-06 18:00:00.530 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5640     

91         2011-04-06 18:00:00.567 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5562     

92         2011-04-06 17:59:59.300 suspended    INSERT     0                   RESOURCE_SEMAPHORE    6937     

93         2011-04-06 17:59:58.517 suspended    INSERT     0                   RESOURCE_SEMAPHORE    7906     

94         2011-04-06 18:00:00.567 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5453     

95         2011-04-06 18:00:00.550 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5562     

96         2011-04-06 17:59:58.550 suspended    INSERT     0                   RESOURCE_SEMAPHORE    7953     

97         2011-04-06 18:00:00.577 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5375     

99         2011-04-06 18:00:00.610 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5468     

102        2011-04-06 18:00:00.640 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5453     

103        2011-04-06 18:00:02.473 suspended    INSERT     0                   RESOURCE_SEMAPHORE    4046     

104        2011-04-06 18:00:00.780 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5437     

106        2011-04-06 18:00:00.847 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5359     

107        2011-04-06 18:00:00.877 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5390     

108        2011-04-06 18:00:00.880 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5359     

109        2011-04-06 18:00:06.450 suspended    INSERT     0                   RESOURCE_SEMAPHORE    781      

110        2011-04-06 18:00:00.920 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5125     

111        2011-04-06 17:59:59.733 suspended    INSERT     0                   RESOURCE_SEMAPHORE    6093     

112        2011-04-06 18:00:00.910 suspended    INSERT     0                   RESOURCE_SEMAPHORE    5312     

113        2011-04-06 18:00:01.247 suspended    INSERT     0                   RESOURCE_SEMAPHORE    3906     

 

在这里我们发现所有的连接等待的资源都是RESOURCE_SEMAPHORE  状态都是suspended,等待的事件大部分超过了5秒钟。

既然确定了所有的连接都在等待RESOURCE_SEMAPHORE,那么以上收集的这些信息就不足够来检查和定位具体的问题了。我们使用如下脚本收集更多的信息:

write the script. to collect DMV info:

while (1=1)

begin

print '****************'

print getdate()

 

print '*********sys.dm_exec_query_resource_semaphores info**********'

select * from sys.dm_exec_query_resource_semaphores

 

print '*********sys.dm_exec_query_memory_grants info**********'

select * from sys.dm_exec_query_memory_grants

 

print '*********sys.dm_exec_requests info**********'

select * from sys.dm_exec_requests where session_id>50

 

DBCC MEMORYSTATUS

print '****************' 

waitfor delay '00:00:10'

end

 

从再次收集到的DMV的结果我们可以看到:


这些数据库连接都在等待分配数据库内存,每个连接都要求80688KB的内存,绝大部分连接都没有获得内存。从系统性能日志也可以看到这样的现象:


从上面DMV的信息除了发现这些数据库连接每个都要求分配80MB左右的内存,我们还可以看到这个连接的plan_handle 都是一样的。这说明所有的数据库连接都在调用同样的语句或者存储过程。那么我们就可以很容易从profiler trace里面定位到具体的语句,然后检查语句的执行计划,看看为什么这个语句在执行的时候要求这么多内存。


在这个案例中,我们最终发现了导致问题的存储过程中包含了这样的语句:

INSERT INTO #Trustees

       SELECT Trustee.TrusteeIdentity FROM OPENXML(@hDoc, '/ROOT/SID') WITH (SID [EVMoniker] '.') XMLDATA

       INNER JOIN Trustee ON Trustee.SID = XMLDATA.SID collate database_default

这个语句读取了一个XML文件并和SQL Server里面的一个表做关联查询,如果xml文件很大,这条语句会需要分配较多内存来执行。修改这条语句,直接将XML文件的数据写入临时表,然后再来和SQL Server里面的表做关联,就解决了这个问题。

11_1.jpg

11_2.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25175503/viewspace-704933/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-21

  • 博文量
    69
  • 访问量
    75800