InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 13:44:51 GMT20http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxPlease post new questions in a new thread. ThanksFri, 19 Apr 2013 01:29:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxGood Day,our environment runs on SQL Server 2008R2 64 bit Ent with 32 CPUs and 200 gig of memory . When I run diagnostics queries to see which processor is busy , there are only 4 processors at a time with readings lower than 10 at a time the others have readings of 0 . We did add the required indexes and tuned the code , but we still get CXPacket SOS_SCHEDULEr_Yield waits . Any ideas ?Fri, 19 Apr 2013 01:21:19 GMTlianvh 89542http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]scogeb (12/3/2012)[/b][hr][quote][b]Ninja's_RGR'us (8/7/2011)[/b][hr]I know this is an old thread but since it seems to be found often on google, I find it important to tell anybody reading this that our collective understanding of cxpackets was upgraded over the years. And by quickly skimming this thread I see some info that could use adjusting.Paul White has spent a genormous amount of time writing this amazing article debunking that myth once and for all.Have a nice read : [url]http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/[/url][/quote]Google search brought me here, LOL. That link isn't working for me. Is it correct?[/quote]Yes. (Working on part 2 now).Tue, 04 Dec 2012 03:02:15 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxWhilst reducing the number of Processors used will reduce parallelism waits it may not be the best way to 'fix' the query ie. there are probably other underlying issuesA good link on the 'barking dog analogy'http://www.sqlsoldier.com/wp/sqlserver/thebarkingdoganalogyTue, 04 Dec 2012 02:55:18 GMTChris Burton-434978http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxStill works for mehttp://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/Mon, 03 Dec 2012 10:53:59 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]Ninja's_RGR'us (8/7/2011)[/b][hr]I know this is an old thread but since it seems to be found often on google, I find it important to tell anybody reading this that our collective understanding of cxpackets was upgraded over the years. And by quickly skimming this thread I see some info that could use adjusting.Paul White has spent a genormous amount of time writing this amazing article debunking that myth once and for all.Have a nice read : [url]http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/[/url][/quote]Google search brought me here, LOL. That link isn't working for me. Is it correct?Mon, 03 Dec 2012 10:00:26 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxI know this is an old thread but since it seems to be found often on google, I find it important to tell anybody reading this that our collective understanding of cxpackets was upgraded over the years. And by quickly skimming this thread I see some info that could use adjusting.Paul White has spent a genormous amount of time writing this amazing article debunking that myth once and for all.Have a nice read : [url]http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/[/url]Sun, 07 Aug 2011 20:15:38 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]SKYBVI (7/27/2011)[/b][hr]aNY SOLUTION?[/quote]Please start a new thread. This one has been revived 4 times already and I don't think there's much more to add to it at this point.Wed, 27 Jul 2011 08:14:39 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxaNY SOLUTION?Wed, 27 Jul 2011 08:07:30 GMTSKYBVIhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxJaimie, please don't post to threads that were started 2.5 years ago . . . :-)Tue, 01 Mar 2011 07:32:11 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxFor me, CXPACKET was fixed by not allowing SQL to have all the processors and all the memory. I removed one of the processors (Properties | Processor of the server - do not "Automatically set processor affinity mask for all processors") and then allocating some of the memory for the system. We had reporting services eating up the memory and gave it 8 Gig of the 32 Gig available and this straightened up most of the difficulty.Mon, 28 Feb 2011 13:56:08 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx(I know the last post on this thread is over 2 months old but no one answered the last question posted.)You will most likely find the enabling or disabling of hyperthreading to be in your server's BIOS.LCMon, 01 Nov 2010 14:58:52 GMTGail Wanabeehttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote]NOTE: if you have hyperthreading enabled it gets MUCH worse. Consider disabling HT if you are seeing logs of CXPACKET waits first, then reevaluating and if necessary adjust MAXDOP.[/quote]where to verify whether hyperthreading is enabled or not?We have Windows 2003 R2 enterprise edition x64 and CPU is PIII Xeon with speed 2834 MHZThanksTue, 20 Jul 2010 16:12:49 GMTMani-584606http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxYes, you are right. I have not come in here for months, just reading as new. I did not see his comments that he resolved the problem.My point is people tends to post "my system does not work" missing the precise description or what thye have tried to resolve the problem. Is he using SP2 (no mentioned)? That is why skyview answers were provided to him.Fri, 09 Apr 2010 08:20:56 GMTjswong05http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]flyme4ual (10/14/2008)[/b][hr]Hi guys,I'm seeing CXPACKET wait types in one of my database running SQL2005 which causing the CPU hit 100%. The server is 64 bit Windows 2003 with 8 CPU. Several tables contain over 10 million rows. Does anyone encounter this issue before and what was done to fix the problem?Thanx.[/quote]When you started this question, you didn't provide what you have looked at, such as is your disk fragmented? index fragmented?Did you query DMV for the top CPU sessions like so? (What do you see?) Once picked up the CPU culpit, did you interrigate the query efficiency with index scheme? I can go on and on ..... You have to check into a specific culpit, we can then advise you a solution.WITH QPLAN AS(SELECT TOP 10 SUM(QS.TOTAL_WORKER_TIME) ASTOTAL_CPU_TIME, SUM(QS.EXECUTION_COUNT) ASTOTAL_EXECUTION_COUNT, SUM(QS.TOTAL_WORKER_TIME)/SUM(QS.EXECUTION_COUNT) AS EACHEXECUTION, COUNT(*) ASNUMBER_OF_STATEMENTS, SQL_TEXT.TEXT, QS.PLAN_HANDLE FROM SYS.DM_EXEC_QUERY_STATSQS CROSS APPLYSYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXTGROUP BY SQL_TEXT.TEXT,QS.PLAN_HANDLE ORDER BY SUM(QS.TOTAL_WORKER_TIME) DESC)SELECT * from QPLAN CROSS APPLY sys.dm_exec_query_plan(QPLAN.PLAN_HANDLE)Fri, 09 Apr 2010 08:16:01 GMTjswong05http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]jswong05 (4/9/2010)[/b][hr][quote][b]chileu17 (10/21/2008)[/b][hr]Sorry SqlGuru :hehe: I think I couldn't explain to you my question. I do understand your point, BUT how do I implement what you are talking about?More indexes?new transactions?I know that the most probable answer is that it depends on every case right lol well I have a dedicated server with no other apps running on it. It runs on Windows server 2003 with a HD of 150 gb with a redundant SAN. We have installed a RAID 5 on it :( please any suggestions are welcome. Thanks in advance again.[/quote]Did you check the IO of this query? using set statistics io on, or any DBA tools, or just DMV -- use Hilary Cotter's blog he published a bunch I used all the time that I don't have to repeat here.You can break the query into smaller transactions, better indexes to get better plan (you will see IO reduced). Increase RAM, replace with faster logical or physical drives, such as change RAID 5 to RAID 10, it depends how much overhaul you want? how much money you want to spend?[/quote]jswong05, please note the last post date on threads when you are replying to them. this one hasn't been touched in months. :)Fri, 09 Apr 2010 08:12:41 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]chileu17 (10/21/2008)[/b][hr]Sorry SqlGuru :hehe: I think I couldn't explain to you my question. I do understand your point, BUT how do I implement what you are talking about?More indexes?new transactions?I know that the most probable answer is that it depends on every case right lol well I have a dedicated server with no other apps running on it. It runs on Windows server 2003 with a HD of 150 gb with a redundant SAN. We have installed a RAID 5 on it :( please any suggestions are welcome. Thanks in advance again.[/quote]Did you check the IO of this query? using set statistics io on, or any DBA tools, or just DMV -- use Hilary Cotter's blog he published a bunch I used all the time that I don't have to repeat here.You can break the query into smaller transactions, better indexes to get better plan (you will see IO reduced). Increase RAM, replace with faster logical or physical drives, such as change RAID 5 to RAID 10, it depends how much overhaul you want? how much money you want to spend?Fri, 09 Apr 2010 07:52:06 GMTjswong05http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]GilaMonster (1/4/2010)[/b][hr]Please post new questions in a new thread in future.If you look while the query is running, there will be at least one thread that doesn't have a cxpacket, that has a different wait type. Whatever that is is what's holding the query up. What is it?I usually recommend generic performance tuning (tune the query, tune the indexes) before panicking over CXPackets. It's quit common to see them on non-optimised queries.[/quote]A perfect example of this is this statement: " but in some cases I could greatly reduce query execution time (and CXPACKET waits) by using the OPTION LOOP JOIN"that is a clear indication of a suboptimal query.Wed, 06 Jan 2010 09:00:01 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxI wrote a blog regarding issues i was having with that:http://www.sqlservercentral.com/blogs/christaylor/archive/2009/05/04/sql-server-cpu-s-at-100-anyone-checked-the-vas-virtual-address-space.aspxI have however just realised i've negated to include in the blog the fact that we got a lot of CXPacket wait types when we hit this issue.Wed, 06 Jan 2010 08:12:15 GMTChrisTaylorhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxSwirl, please explain that?Wed, 06 Jan 2010 08:06:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxthey're also common when your server has ran out of VAS (Virtual Address Space)Wed, 06 Jan 2010 07:43:56 GMTChrisTaylorhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxPlease post new questions in a new thread in future.If you look while the query is running, there will be at least one thread that doesn't have a cxpacket, that has a different wait type. Whatever that is is what's holding the query up. What is it?I usually recommend generic performance tuning (tune the query, tune the indexes) before panicking over CXPackets. It's quit common to see them on non-optimised queries.Mon, 04 Jan 2010 23:53:11 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxI'm having similar issues on a report i'm working on which does a lot of select into temorary tables before it does the final select that returns the results. I tried changing the MAXPOD to 2 and then 4 (out of a total of 8) for every single select in my stored procedure but have had no luck yet. Could be because of my use of temporary tables to hold results? Any other suggestions for getting rid of CXPACKET wait type?Mon, 04 Jan 2010 18:10:36 GMTAbe Miesslerhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxI've seen this problem more often on SQL 2005 than on SQL 2000 instances, but I must agree servers tend to get more cores everyday. On some instances used mainly for OLTP I set the maximum number of processors per query to one, thereby effectively disableing parallel queries. This sometimes improves both query performance and throughput but it is not an options if you want to perform some serious reporting on a server.But there is no general solution. I/O seems to be the bottleneck but in some cases I could greatly reduce query execution time (and CXPACKET waits) by using the OPTION LOOP JOIN. No two cases are the same and if a SAN is involved, things are getting more complicated because most SAN experts are not used to the demands of SQL server regarding caching, write order, and many other aspects of disk I/O. Getting a SQL server up to speed on a SAN is far from simple, and requires a specialist with knowledge and experience on both subjects.As mentioned by others, up-to-date statistics won't ever harm your queries. More memory does not always improve performance; a very large table might still not fit into memory, and thus a table scan on this table might still require massive disk I/O. In this case profiling will offer you some insight in missing indexes that could increase performance, but as always, it depends ...Mon, 20 Jul 2009 07:21:06 GMTvliethttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxWE had an IO problem on RAID 5. Change to RAID 1 0 if possible. It will improve IO alot. Add more physical drives to the SAN drive. Put the log files on a different SAN drive with different physical drives.Also get your SAN provider to diagnose the SAN. We have a cache problem that fills up on the controller and then slows the SAN performance drastically.This will all improve your IO capabilities.Tue, 30 Jun 2009 06:35:26 GMTOomBoomhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxAlso, gather some of your largest tables, those most actively queried in your system.Do "sp_helpindex tblName" to get a list of indexes for each table.Run "DBCC SHOW_STATISTICS ('tblName', 'idxName')" on the tables and select indexes.Is the Rows Sampled equal to the Total Rows in the index?If not, you may want to run "UPDATE STATISTICS tblName WITH FULLSCAN" to ensure stats is maintained on the table on the basis of the full data distribution (as opposed to a subset sample). I have often found this to solve my performance problems.Wed, 22 Oct 2008 18:11:00 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxWell-tuned queries - in an OLTP system - genarally should not parallellize. This is almost certainly a case of a suboptimally tuned system.Is auto-updatestats, auto-createstats turned on for your databases?Do you regularly carry out index defragmentation?Are your queries properly indexed (as was already mentioned)?Is your tempdb properly configured?These are some of the questions to consider.Wed, 22 Oct 2008 18:03:31 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]chileu17 (10/21/2008)[/b][hr]Sorry SqlGuru :hehe: I think I couldn't explain to you my question. I do understand your point, BUT how do I implement what you are talking about?More indexes?new transactions?I know that the most probable answer is that it depends on every case right lol well I have a dedicated server with no other apps running on it. It runs on Windows server 2003 with a HD of 150 gb with a redundant SAN. We have installed a RAID 5 on it :( please any suggestions are welcome. Thanks in advance again.[/quote]Hmm, I thought that "add more RAM" and "add more physical spindles" was pretty self-explanatory. :DIt really is that simple though. Get data through the CPUs more quickly (which RAM and IO perf boost will do) and CXPACKETwaits drop down. Other things: cut read requirements by a) better indexing b) refactoring query to reduce data pulled/accessed, lower maxdop (already covered in depth), ensure all IO config options are set right between sql server and SAN (HBA [queue depth?, cache ratios and size?), SAN cache, drivers up to date?, etc). There are other things as well.Best is to get a pro to come in and give your system a performance review. We have hardly touched the surface here.Tue, 21 Oct 2008 17:54:03 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxSorry SqlGuru :hehe: I think I couldn't explain to you my question. I do understand your point, BUT how do I implement what you are talking about?More indexes?new transactions?I know that the most probable answer is that it depends on every case right lol well I have a dedicated server with no other apps running on it. It runs on Windows server 2003 with a HD of 150 gb with a redundant SAN. We have installed a RAID 5 on it :( please any suggestions are welcome. Thanks in advance again.Tue, 21 Oct 2008 15:23:41 GMTchileu17http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]chileu17 (10/20/2008)[/b][hr]yeah we are thinking on calling some MS performance expert on this matter, because we don't seem to get a better performance in any of our attempts. But just to try something new, what did you mean with more spindles to the IO mix to improve IO performance?Thanks for the help guys :)[/quote]CXPACKET waits are due to parallelism where the query optimizer decides to break up the IO and processing for a query into multiple threads that run concurrently then it gathers the data back together to provide the necessary output. In pretty much every case, the CPUs wait for IO streams because the IO subsystem is slow as molasses in February, relatively speaking. :D So by providing better IO capabilities you can feed those GHz-speed CPUs with data with fewer waits --> much faster parallel query performance. This is the 10000 foot view, btw. There is just a weeeee bit more to it than this. :cool:Mon, 20 Oct 2008 16:03:29 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxyeah we are thinking on calling some MS performance expert on this matter, because we don't seem to get a better performance in any of our attempts. But just to try something new, what did you mean with more spindles to the IO mix to improve IO performance?Thanks for the help guys :)Mon, 20 Oct 2008 13:49:49 GMTchileu17http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxProbably the BEST thing you can do (if able) is to a) max out the servers RAM (which is hopefully many, many GBs) and b) add more spindles to the IO mix to improve IO performance.I also recommend (regularly on performance forums such as this) that you get a professional to come in (onsite or remote) and give your systems a performance review. There are a pleathora of things that can be done suboptimally that will hurt performance. :)Mon, 20 Oct 2008 11:16:22 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]TheSQLGuru (10/17/2008)[/b][hr]At least 75% of my clients have multi-cpu servers with insufficient RAM or IO capabilities (or both!), and thus suffer from significant CXPACKET waits on largish queries. It is often optimal in general in situations like that to simply set MAXDOP at the server level to some fraction of the total number of CPUs (1/4 to 1/2 usually). In other cases you can specify the maxdop as an OPTION for an individual query. NOTE: if you have hyperthreading enabled it gets MUCH worse. Consider disabling HT if you are seeing logs of CXPACKET waits first, then reevaluating and if necessary adjust MAXDOP.[/quote]Thanks we are gonna try that out because this CXPACKET wait is just killing our performanceMon, 20 Oct 2008 08:42:31 GMTchileu17http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspx[quote][b]chileu17 (10/16/2008)[/b][hr]Hi everyone I am having a similar problem here, well on my dev server I have 4 cpu, I dunno why I get so much CXPACKET wait time, well my maxdop is 0, should I set it to 2? so I can get a better performance?[/quote]I would at least try it to see if it helps.Fri, 17 Oct 2008 08:46:53 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxAt least 75% of my clients have multi-cpu servers with insufficient RAM or IO capabilities (or both!), and thus suffer from significant CXPACKET waits on largish queries. It is often optimal in general in situations like that to simply set MAXDOP at the server level to some fraction of the total number of CPUs (1/4 to 1/2 usually). In other cases you can specify the maxdop as an OPTION for an individual query. NOTE: if you have hyperthreading enabled it gets MUCH worse. Consider disabling HT if you are seeing logs of CXPACKET waits first, then reevaluating and if necessary adjust MAXDOP.Fri, 17 Oct 2008 08:03:46 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxHi everyone I am having a similar problem here, well on my dev server I have 4 cpu, I dunno why I get so much CXPACKET wait time, well my maxdop is 0, should I set it to 2? so I can get a better performance?Thanks for your help,Thu, 16 Oct 2008 14:15:03 GMTchileu17http://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxCXPacket is wait caused by a parallel skew. What happens is that SQL runs a query in parallel and, for whatever reason, one of more of the threads lags behind. The ones that finish have to wait for the slower ones to catch up. That wait is the CXpacket waitYou can either reduce the max degree of parallelism for the entire server or, if it's a specific query that's giving the problem, you can add the maxdop hint to that query.Be careful if you do the former, as it will affect the entire server. If you decide to go that route, I would suggest setting the server's max degree of parallelism to 4 (half the number of cores that you have) and see how it goes from there.Wed, 15 Oct 2008 01:24:55 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic585961-360-1.aspxHi guys,I'm seeing CXPACKET wait types in one of my database running SQL2005 which causing the CPU hit 100%. The server is 64 bit Windows 2003 with 8 CPU. Several tables contain over 10 million rows. Does anyone encounter this issue before and what was done to fix the problem?Thanx.Tue, 14 Oct 2008 23:34:18 GMTflyme4ual