August 3, 2007 at 9:08 am
I have inherited several jobs (>15) that seem to take forever to complete, even though they do not appear to be very complex. When these jobs run I've noticed that there can be up to 16 different entries for the same spid with different context id's. I am assuming, mistakenly perhaps, that these are parallel processes spawned by the job. Usually there are only one or two that are runnable, the others are sleeping.
Is this in fact parallel processing? Is there a way to restrict how much any one job can do? Do multiple jobs like this, running at the same time, cause performance issues?
August 6, 2007 at 3:55 am
SQL is most likely giving up with a decent plan and using a lot of parrallelism due to poor indexing.
What does the query plan look like? Some table or index scans? Hash joins?
As a note, you can force SQL to not use parrallelism by using the query hint Option (Maxdop 1).
I would warn against this because if SQL is using it, there is a reason.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 6, 2007 at 9:31 am
there can be issues with parallel plans, however, it may be the parallel plan is right, you'll really only know if you view the execution plan for the query.
In some cases if your have HT enabled on yoru server you may get problems, if the issue is severe I'd suggest disabling HT in the bios and test to see what happens. You can add the maxdop hint to the query, I usually start at half the number of processors/cores and work my way down, so on an 8 way box I'd start with 8 on dual core, 4 single core, then 4 & 2 , then 2 and 1.
You have to view the io and execution time to see which si best, it's probably better to use profiler for this.
If you monitor waits and get a high time for CXPacket then you probably have a problem - don't always assume parallel plans are bad, they're not.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 6, 2007 at 11:34 am
If you really want to get a specific answer to your question, you need to post the query plan to the thread. That will give people some information that will help to determine what is happening. I have found few cases where queries that are not designed for olap processesing should use parallelism. In most cases it's how sql server handles inefficient queries in the fastest possible manner. The real problem with this though is that high levels of parallelism (16 is very high) are going to cause other processes to wait for processor time, and if a query is taking a long time to run, it may be holding locks that can cause some serious performance issues.
To get the xml plan put
Before your query and then click the INCLUDE ACTUAL QUERY PLAN button and then execute the query.
This will give you the xml which you can include here.
August 6, 2007 at 1:16 pm
Thanks all. I'll get the plan just as soon as possible. You've all been of great help. Thanks again.
August 7, 2007 at 9:00 am
I would also recommend having a professional give your system a performance review if you are now the dba but are not trained as such.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply