October 1, 2009 at 5:03 am
Hi all
As the title states I have an issue that occurs maxing out hte CPU. If I look in the DMV sys.dm_os_waiting_tasks I can see the SPID in question with a wait_type of CXPACKET (Parallelism issue?) and a blocking_task_address but no blocking_session_id.
Also in the resource description is the following:
exchangeEvent id=port42227a0 nodeId=3
Is there anyway to free up this process to complete? or do I need to kill of the original SPID? Also where to look to avoid this from happening in the future?
Thanks in advance.
October 1, 2009 at 6:39 am
Kwisatz78 (10/1/2009)
Hi allAs the title states I have an issue that occurs maxing out hte CPU. If I look in the DMV sys.dm_os_waiting_tasks I can see the SPID in question with a wait_type of CXPACKET (Parallelism issue?) and a blocking_task_address but no blocking_session_id.
Also in the resource description is the following:
exchangeEvent id=port42227a0 nodeId=3
Is there anyway to free up this process to complete? or do I need to kill of the original SPID? Also where to look to avoid this from happening in the future?
Thanks in advance.
Well CXPACKET is usually to do with parallelism, i would check to see if this is still an issue and see if any other spids have this wait state. find out exactly what you are killing, before you kill off any spid/process
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 1, 2009 at 7:15 am
If you are not able to fix the parallelism issue, take a look at MAXDOP hint. Or if you are using SQL 2008, take a look at Resource governor.
But before you go there, please follow Silverfox advice. Any HINTs given to the SQL query engine is the last resort. Please keep that in mind.
-Roy
October 1, 2009 at 7:23 am
Well the SPID related to a motnthly job that is used to start a procedure to populate some "snapshot" tables. I had to stop the job.
October 1, 2009 at 7:30 am
Kwisatz78 (10/1/2009)
Well the SPID related to a motnthly job that is used to start a procedure to populate some "snapshot" tables. I had to stop the job.
So if you have stopped the job, has that fixed the problem... if it has, maybe you should have a look at this job and see if it can be changed/monitored to avoid this problem reoccurring.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 1, 2009 at 4:14 pm
using query hints is not a good idea!
today I was doing some optimization on one of my clients env and (this is medium DW app - about 1TB of data) - there are a lot of queris - one of them was perfoming like shown below (all values in miliseconds)
MAXDOP 1
CPU time: ~21k, Duration: ~92k
MAXDOP 4
CPU time: ~130k, Duration: ~85k
MAXDOP 8
CPU time: ~198k, Duration: ~159k
MAXDOP 0
CPU time: ~222k, Duration: ~151k
in this situation there was some AMD's quads, we use MAXDOP 4 as a staring point for further analysis.
so - if you are expirience a lot of CXPACKET waits - you should change default MAXDOP for this server!
October 2, 2009 at 6:16 am
I do agree that using query hints is not a good idea, but why would you want to set the whole SQL Server to use MAXDOP?
-Roy
October 4, 2009 at 6:04 am
it is related to server architecture(NUMA problems) and parallel processing.
NUMA problems - each node (in this case physical processor) is a separete logical NODE - transfering data between cores inside it is much faster then between physical chips. So there is a performance issue with sending data between physical processors in NUMA architecture (Non Uniforma Memory Access - http://en.wikipedia.org/wiki/Non-Uniform_Memory_Access)
parallel processing works similar to this example:
I have to add 8 numbers: - lets say that every operation costs 1s
1,2,3,4,5,6,7,8
total time for 1 CPU: 1+2+3+4+5+6+7+8 = 7s
total time for 2 CPUs: 4s
1+2+3+4 = 3s
5+6+7+8 = 3s + 1s (for adding subresults)
total time for 4 CPUs: 4s
1+2 = 1s
3+4 = 1s
5+6 = 1s
7+8 = 1s + 1s (CPU1+CPU2) + 1s (sub+CPU3) +1s (sub + cpu4)
conclusion: there are some borders in parallel processing - after this border is achieved you shouldnt add more processors because there no speedup ;/// (http://en.wikipedia.org/wiki/Parallel_computing)
you can read about Amdhal's law.
when you check your wait stats and you will see that wait type CXPACKET has a large value - you should set MAXDOP value properly (CXPACKET is a time when query results are geting together after parallel execution).
October 5, 2009 at 7:37 am
I know what CXPACKET is and I know what NUMA is. But I still do not understand why you want to penalize rest of the queries by restricting it. Restrict the query that has the issue of parallelism, that I can understand.
-Roy
October 5, 2009 at 7:48 am
Seems to be diverting from the op's question. the op hasnt said that the server has NUMA architecture and secondly it is easier in some circumstances to find out what is causing the parallelism issue and restrict it at that level rather than restrict everything at server level. it is like using a sledgehammer to crack a peanut.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 5, 2009 at 8:25 am
It has long been noted that SQL Server often has issues with over-parallelization, and in fact, Microsoft often recommends setting the server-wide MAXDOP to about half the number of cores on your server, rather than leaving it unbound (0, the default). AFAIK, you can still override this in individual queries, if you need to.
Perhaps a more fine-grained approach would be to raise the 'cost threshold for parallelism' setting for the server instance instead. I have heard suggestions ranging from '10' to '30' for this ('5' is the default). And most people who try it report good results from this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 6, 2009 at 3:34 am
I broadly agree with Barry and Marcin. It is very common in (predominantly) OLTP applications to set MAXDOP 1 server-wide. Parallelism can then be enabled per query using the MAXDOP hint, after proper analysis and tuning. Setting the parallelism threshold can work too (though I personally would set in an order of magnitude higher than Barry - but that's just personal choice). The difficulty can be that the threshold is based on estimated cost, and the optimizer cannot be expected to get that even ballpark-correct 100% of the time. Plans with loop joins are frequently over-costed for example.
I too tend to start hints at MAXDOP = 4, partly for (hard or soft) NUMA reasons, but mostly because most of the gains achievable from query parallelism have been achieved by that stage. Returns tend to diminish quickly for MAXDOP >> 4, though of course specific tasks like index rebuilding and other trivially parallelizable tasks can, and often do, benefit from a higher MAXDOP. As I say, MAXDOP 4 is a fine starting point. Some queries that benefit from the hash-bitmap optimization (which is only available in parallel plans) will be quite happy at MAXDOP 2.
CXPACKET waits are often unfairly considered to be indicative of a 'parallelism problem'. In many cases, the underlying cause is skewed data (resulting in uneven thread loading), inappropriate ordered exchanges, or (on 2005) related to partitioning. To give a good answer to the original problem, we would probably need to see an actual execution plan.
Paul
October 6, 2009 at 4:09 pm
Another thing to consider is that Interactive/OLTP sessions are not usually the principal beneficiaries of parallelism: there's not much to be gained by parallelizing requests that should execute in say less than 2 seconds. The sessions that benefit the most from parallelism are things like Agent Jobs, ETL, OLAP/SSAS requests, etc.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply