Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Wait Types concern Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 9:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
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
Post #1382053
Posted Wednesday, November 7, 2012 9:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 42,468, Visits: 35,538
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 2008, MVP
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

Post #1382055
Posted Thursday, November 8, 2012 1:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
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]
Post #1382317
Posted Thursday, November 8, 2012 1:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
Got the above waittypes but CPU never reached more than 70%.

The server is a vmware machine with 36 Core and 9GB RAM.
Post #1382318
Posted Thursday, November 8, 2012 1:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 42,468, Visits: 35,538
No where near enough information. Identify the queries with excessive wait times and investigate the cause.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1382326
Posted Thursday, November 8, 2012 4:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
Checked for processes having huge wait time and they are jobs calling SSIS packages with waittype RESOURCE_SEMAPHORE.
Post #1382395
Posted Thursday, November 8, 2012 7:45 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 709, Visits: 1,412
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"
Post #1382501
Posted Thursday, November 8, 2012 9:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 42,468, Visits: 35,538
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 2008, MVP
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

Post #1382560
Posted Wednesday, November 14, 2012 3:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
Dev team is running around 37 .dtsx files. In job they are calling the main .dtsx file which in turn calls the other files.
Post #1384496
Posted Thursday, November 15, 2012 8:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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
Post #1385186
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse