Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wait Types concern


Wait Types concern

Author
Message
VastSQL
VastSQL
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3029 Visits: 5018
Hi Experts,

In one of our DW servers the performance is very slow that a simple select statment is taking hours. When checked i found many lastwaittype
SOS_SCHEDULER_YIELD
RESOURCE_SEMAPHORE
CXPACKET

Please let me know where to start
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47252 Visits: 44380
Start by posting the query, the index definitions and the execution plan, so that we can see what's happening.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


VastSQL
VastSQL
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3029 Visits: 5018
Thanks Gail,

Actually its SSIS packages running behind .

Some queries used are

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dba].[table]') AND type in (N'U'))
DROP TABLE [dba].[table]
VastSQL
VastSQL
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3029 Visits: 5018
Got the above waittypes but CPU never reached more than 70%.

The server is a vmware machine with 36 Core and 9GB RAM.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47252 Visits: 44380
No where near enough information. Identify the queries with excessive wait times and investigate the cause.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


VastSQL
VastSQL
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3029 Visits: 5018
Checked for processes having huge wait time and they are jobs calling SSIS packages with waittype RESOURCE_SEMAPHORE.
Joie Andrew
Joie Andrew
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1904
Not an expert on SSIS, but from BOL:

RESOURCE SEMAPHORE

"Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts."

Do you think the SSIS package(s) are trying too much parallel operations? I wonder what would happen if you scaled back the amount of concurrent operations it was trying to do at once what would happen.

Joie Andrew
"Since 1982"
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47252 Visits: 44380
You are not giving enough information to help solve the problem.

I suggest you get a copy of the book 'troubleshooting SQL Server' and work through chapter 1.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


VastSQL
VastSQL
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3029 Visits: 5018
Dev team is running around 37 .dtsx files. In job they are calling the main .dtsx file which in turn calls the other files.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5963 Visits: 8312
As Gail has said several times you aren't giving us much to go on.

First thing to try is spreading out the SSIS activity so fewer things are running concurrently. You have likely reached what I call the "exponential breakover point", whereby one or more resources is so exhausted that things slow down exponentially due to the overload.

Much more importantly is to bring on a qualified performance tuning expert. You could go back and forth on this issue for days or weeks and never get is solved via forum posts whereas a good consultant could isolate and resolve the issue(s) in a matter of hours or days.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search