parallel execution

dear all,

I read this :

https://www.mssqltips.com/sqlservertip/2027/a-closer-look-at-cxpacket-wait-type-in-sql-server/

can't see why we need to "This goal of this tip is to increase query performance, decrease high CXPACKET waits without reducing MAXDOP.  "
!

MAXDOP should be a server configuration setting and it should be not change while we see CXPACKET, which is good, right?

"The CXPACKET wait time should be approximately 4 times the elapsed time and CPU time should be approximately 4 times the elapsed time. The reason is only 4 threads are processing the rows, 100,000 rows each (4 threads X elapsed time = CPU time), the other 5 threads (4 child threads and the coordinator thread) wait with CXPACKET wait time (5 threads X elapsed time = CXPACKET wait time)."

I don't understand this, elapsed time  = elapsed time  of each threads, and thread is not going to execute at the same times but one by one, so 4 threads X elapsed time of each threads?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
it depends...

CXPACKET is in most case "not good"
MaxDop can be set in code (it will overwrite Server setting)

Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server
https://support.microsoft.com/en-us/kb/2806535


Query Hints (Transact-SQL) (MAXDOP hint)
https://msdn.microsoft.com/en-us/library/ms181714.aspx
http://blogs.msdn.com/b/jimmymay/archive/2008/11/28/case-study-part-1-cxpacket-wait-stats-max-degree-of-parallelism-option-introduction-to-using-wait-stats-to-identify-remediate-query-parallelism-bottlenecks.aspx


Case Study: Part 1: CXPACKET Wait Stats & 'max degree of parallelism' Option: Introduction to Using Wait Stats to Identify & Remediate Query Parallelism Bottlenecks
http://blogs.msdn.com/b/jimmymay/archive/2008/11/28/case-study-part-1-cxpacket-wait-stats-max-degree-of-parallelism-option-introduction-to-using-wait-stats-to-identify-remediate-query-parallelism-bottlenecks.aspx

How to reduce high CXPACKET
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/694b396d-9c66-4160-93ae-052a1e6bd5f5/how-to-reduce-high-cxpacket?forum=sqldatabaseengine
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
from your point of view, what is the best way to do parallel execution ? from query level (maxdrop, partition by) or change the large table to use table partition ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
this one https://msdn.microsoft.com/en-us/library/ms181714.aspx only about hints, not about parallel execution, right?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
from my link:

"This goal of this tip is to increase query performance, decrease high CXPACKET waits without reducing MAXDOP."

high CXPACKET means the query waiting for parallel execution SHOULD not keep waiting for a long time on parallel execution and so this bad ? and therefore the query hints MAXDROP also stay the same during the time CXPACKET time reduce ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"CXPACKET is in most case "not good""

I think if we have parallel execution, it is good ! so why it is bad ?

"Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server
https://support.microsoft.com/en-us/kb/2806535
"

"Also, the maximum value of 8 that is mentioned in these guidelines is applicable for typical SQL Server activity and the overhead for the exchange operators that are used in parallel query plans. You can vary this maximum value, depending on your specific application patterns and the concurrent activity on the instance of SQL Server. For example, consider the following situations:
If you have very small number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a larger value. For example, you can set the MAXDOP value to 16.  
If you a have very large number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value. For example, you can set the MAXDOP value to 4. "

why smaller number of queries prefer to have higher MAXDROP ? and large number of queries need less MAXDOP ? less CPU allow to work in parallel for large number of queries is better?
0
Eugene ZCommented:
as I said it depends: e.g .you see t-sql slowness
start with default  Parallelism ON (use all CPUs unless you have a lot e.g. 48 than start from MAXDOP 2 or 4 ),
monitor sql server code execution (sql profiler for example)

Degree of Parallelism (7.0 Insert) Event Class
https://msdn.microsoft.com/en-us/library/ms187943.aspx
-------------------------------------------------------------
in some cases it is required to switch off  server Parallelism :


"Best practices for SQL Server in a SharePoint 2013  Server farm"https://technet.microsoft.com/en-us/library/Hh292622.aspx
Set max degree of parallelism (MAXDOP) to 1 for instances of SQL Server that host SharePoint databases to make sure that a single SQL Server process serves each request

more
Understanding and Using Parallelism in SQL Server
https://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Degree of Parallelism (7.0 Insert) Event Class
https://msdn.microsoft.com/en-us/library/ms187943.aspx"

oh you mean read from event log of that SQL windows server ?

"start with default  Parallelism ON"

you mean MAXDOP is 0 (let SQL server user all CPU for parallelism) ?  or what ?

"https://technet.microsoft.com/en-us/library/Hh292622.aspx"

by this, "Setting the max degree of parallelism to any other number can cause a less optimal query plan to be used that will decrease SharePoint Server 2013 performance."

but setting MAXDOP  =1 means only use 1 CPU for paralelism, right?  can't see why only use one then.
0
marrowyungSenior Technical architecture (Data)Author Commented:
one thing, is it normal to check which table which has a lot of query access it and we try to change it to partitiioned table?

so any script to find out how many concurrent access of a table and database, order by the most busy accessing table by select/insert/update?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.