InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 01:49:33 GMT20http://www.sqlservercentral.com/Forums/Topic1412181-146-1.aspx[quote][b]sqldba_newbie (1/29/2013)[/b][hr][quote][b]GilaMonster (1/29/2013)[/b][hr]If you want all queries to only use one processor, why not pull the other 23 out? That's essentially what you've done by setting maxdop to 1. It is NOT a good thing to do.[/quote]If there are 10 processes running wouldnt they use 10 different core's if MAXDOP is set to 1?[/quote]Probably, but you're still throttling your server, especially a data warehouse-type server that will typically be running smaller numbers of large queries.[quote]I do see heavy waits on PAGEIOLATCH_SH. Do you have query to find total wait on waitype PAGEIOLATCH_SH for a particular spid?[/quote]sys.dm_os_waiting_tasksTue, 29 Jan 2013 12:51:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1412181-146-1.aspx[quote][b]GilaMonster (1/29/2013)[/b][hr]If you want all queries to only use one processor, why not pull the other 23 out? That's essentially what you've done by setting maxdop to 1. It is NOT a good thing to do.[/quote]If there are 10 processes running wouldnt they use 10 different core's if MAXDOP is set to 1?[quote]Of course you won't see any CXPacket waits with maxdop at 1. Maxdop of 1 means never parallel.Maxdop should probably be changed from the default, but not to 1. See the last section here: [url]https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/[/url]CXPacket waits are not an indication of poor performance. They're an indication that queries are running in parallel. That is all. If you have lots and lots and lots of CXPacket, then, to reduce them and improve performance, you need to look for the other waits. In any query that's got huge CXPacket waits, there will be one or more threads that have some other wait type. That's the wait type that you need to investigate and resolve.Look for the other wait typesOptimiser your queriesIncrease cost threshold for parallelismSet maxdop back to something > 1[/quote]I do see heavy waits on PAGEIOLATCH_SH. Do you have query to find total wait on waitype PAGEIOLATCH_SH for a particular spid?Tue, 29 Jan 2013 12:07:02 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic1412181-146-1.aspxIf you want all queries to only use one processor, why not pull the other 23 out? That's essentially what you've done by setting maxdop to 1. It is NOT a good thing to do.Of course you won't see any CXPacket waits with maxdop at 1. Maxdop of 1 means never parallel.Maxdop should probably be changed from the default, but not to 1. See the last section here: [url]https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/[/url]CXPacket waits are not an indication of poor performance. They're an indication that queries are running in parallel. That is all. If you have lots and lots and lots of CXPacket, then, to reduce them and improve performance, you need to look for the other waits. In any query that's got huge CXPacket waits, there will be one or more threads that have some other wait type. That's the wait type that you need to investigate and resolve.Look for the other wait typesOptimiser your queriesIncrease cost threshold for parallelismSet maxdop back to something > 1Tue, 29 Jan 2013 01:45:25 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1412181-146-1.aspx[quote][b]sqldba_newbie (1/28/2013)[/b][hr]You still didnt answer my question.[/quote] i cant , without seeing the actual query exec plan (before changes and after changes), post the plans then based on that i will try.Mon, 28 Jan 2013 23:25:54 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1412181-146-1.aspx[quote][b]Bhuvnesh (1/28/2013)[/b][hr]Another thing which we need to consider is ,look for queries that run under parallelism and test them manually using different levels of DOP using the OPTION(MAXDOP n) query hint to see if reducing parallelism actually improves or harms performance.[i]Tune the query before tuining the server [/i][/quote]Thanks. You still didnt answer my question. As mentioned in my post i am completely aware that i shouldn't be tweaking with MAXDOP settings, my question was why is my server performing better after changing it to 1 and any recommendations on my query.Mon, 28 Jan 2013 09:27:40 GMTcurious_sqldbahttp://www.sqlservercentral.com/Forums/Topic1412181-146-1.aspxAnother thing which we need to consider is ,look for queries that run under parallelism and test them manually using different levels of DOP using the OPTION(MAXDOP n) query hint to see if reducing parallelism actually improves or harms performance.[i]Tune the query before tuining the server [/i]Mon, 28 Jan 2013 04:06:09 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1412181-146-1.aspxWhen a parallel operation is created for SQL Query, there are multiple threads for a single query. Each query deals with a different set of the data (or rows). Due to some reasons, one or more of the threads lag behind, creating the CXPACKET Wait Stat. where the transactions are smaller and queries are not long but very quick usually, [b]set the “Maximum Degree of Parallelism” to 1 (one)[/b]. This way it makes sure that the query never goes for parallelism and does not incur more engine overheadMon, 28 Jan 2013 03:58:42 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1412181-146-1.aspxi have noticed very high CXPacket waits on one of our dataware house server. All these queries are just select with bunch of joins. I couldn't find any missing indices. At the server level CTP was set to 5 and maxdop to 0. I changed the CTP value to 32 and maxdop to 1 (server has 24 logical cores), since then i dont see any CXPacket waits. I know tweaking maxdop is not the best way, any recommendations on how to avoid this . Since i changed the MAXDOP values queries started running much faster.Sun, 27 Jan 2013 22:28:43 GMTcurious_sqldba