November 7, 2012 at 9:26 am
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
November 7, 2012 at 9:28 am
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
November 8, 2012 at 1:32 am
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].
') AND type in (N'U'))
DROP TABLE [dba].
November 8, 2012 at 1:33 am
Got the above waittypes but CPU never reached more than 70%.
The server is a vmware machine with 36 Core and 9GB RAM.
November 8, 2012 at 1:51 am
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
November 8, 2012 at 4:11 am
Checked for processes having huge wait time and they are jobs calling SSIS packages with waittype RESOURCE_SEMAPHORE.
November 8, 2012 at 7:45 am
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"
November 8, 2012 at 9:10 am
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
November 14, 2012 at 3:00 am
Dev team is running around 37 .dtsx files. In job they are calling the main .dtsx file which in turn calls the other files.
November 15, 2012 at 8:13 am
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 on googles mail service
November 16, 2012 at 1:09 am
Thanks Kevin,
Ours is a small organization and cant afford consultants at this point.
November 16, 2012 at 9:34 am
Ratheesh.K.Nair (11/16/2012)
Thanks Kevin,Ours is a small organization and cant afford consultants at this point.
Hopefully you can afford to have database servers that take hours to return answers to queries. I would be willing to bet there are any number of other suboptimal/unfortunate things in place, such as maintenance practices (LOTS here), integrity checks, server/sqlserver/database settings, ability to recover in an outage, etc. Many of the clients I come across (especially the small ones) truly could not afford to NOT get a person on board for at least a short time to fix critical items. I have also seen many clients spend WAY more on hardware than was necessary because their systems were not properly tuned/maintained.
Anyway - best of luck resolving your performance issues!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply