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里面的表做关联,就解决了这个问题。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25175503/viewspace-704933/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25175503/viewspace-704933/