cancel
Showing results for 
Search instead for 
Did you mean: 

How to identify which SQL statement contributes the largest percentage of wait time?

former_member211576
Contributor
0 Kudos

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member211576
Contributor
0 Kudos

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.

Sriram2009
Active Contributor
0 Kudos

Hi Lee

1. Could you refer the SCN link

2. Which version of MS Sql ? have you applied the latest service pack ?

BR

SS

former_member211576
Contributor
0 Kudos

Hi SS,

  I already read the note and this system goes live for more than 6 months without any problems.

SQL server 2012 SP1 CU8. I think it is up to date, not the latest though.