on 06-09-2014 10:23 AM
Hi experts,
I experienced a poor performance issue recently. Some queries ran more than 5X slower than before. I checked wait events and I found RESOURCE_SEMEPHORE occupies 35.142% in 6/1. Please see the screenshot 1 below. It is abnormal.
I drilled down wait events info and found it started at 6/1 6:00am. end of part 1 because some forum allows only two images per post.
Description for the each row showed above:
Row 1:wait type
Row 2:the wait time in milli seconds
Row 3:the wait time as a percentage of the total wait time
Row 4:the average wait time per request
I checked SQL statement history like screenshot 3 below. Now here is my question. I usually troubleshoot SQL server performance issue by finding the highest Phys Reads and highest % Phy Rd because disk is the slowest part in the system and full table scan a huge table always causes significant performance degrade. However, RESOURCE_SEMAPHORE means memory access, not Phys Reads (
http://msdn.microsoft.com/en-us/library/ms179984.aspx ). Should I start with high percentage of SQL calls or (logical) Reads? Moreover, how do I make sure they consumes most RESOURCE_SEMAPHORE waits? Even so, the developers always say those are the data they need, ex:
SELECT USNAM AS c ,CPUDT AS c ,CPUTM AS c ,RCLNT AS c ,GL_SIRID AS c
FROM GLPCA
WHERE RCLNT = '218' AND CPUDT = '20140529'
/* R3:ZCHY0021:1232 T:GLPCA */
This statement returns more than 2 millions records. Any opinions will be appreciated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.