measure the need of T-SQL query parallism

Dear all,

right now read these:


but all these DO NOT give you a hints on how to detect the CURRENT SQL server query's situtation and what signal will tells us we need to change the MAXDROP and degree of parallelism so that overal perfomance is better as we are making use of all available CPY threading?

1) are saying we don't need all query to execute in parallel, is that right? all run in parallel will make the whole SQL server slower?

I don't agree with this as we buy bought the hardware anyway and why just fully utilize that?
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

ste5anSenior DeveloperCommented:
Not every query can be executed in parallel. Some can, but require then lot of synchronization, for example resulting in lots of CXPACKET waits. Some of these parallel, waiting queries run faster when executed with MAXDOP 1 without parallelization.

So, there is no rule thumb.

Only to test and try different options on long running queries to see the outcome.

btw, "CPY threading"?
marrowyungSenior Technical architecture (Data)Author Commented:
"for example resulting in lots of CXPACKET waits'

wait, this is one of the concept I tried to verify, if we keep seeing CXPACKET from sp_who2, is that mean a lot of query is EXECUTING by parallelism ?  or they just want to be ?

"btw, "CPY threading"?"

CPU threading ! I am sorry for the typo.

"So, there is no rule thumb."

tks for that but from Computer science point of view, we should see parallelism most of time ?

and so from infrastructure point of view, in order to see parallel execution from a large table, I should partition that table so that when selecting from that partitioned table, each partition of the partitioned table will be executed by one single CPU treading and already better ?
ste5anSenior DeveloperCommented:
1. When you see those waits, then you see already a parallel query processing data, but each thread with CXPACKET is currently waiting for some other threads.

2 From the CS viewpoint: In general there are not so much parallel algorithms.
From the database view: Having enough data, with supporting structures like indices and partitioned tables we can see them often. But this depends on the used operators. Some force the entire plan into serial mode, some require only a serial area in the plan (Parallel Query Execution Presentation, Craig Freedman).

3. Partitions are good for two things:
a) The partition function matches the query condition in such a way, that only one partition must be read.
b) When more partitions are read, then there is already on this level parallel execution possible. For performance reasons with large data and cold buffers these should reside on their own spindles.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

marrowyungSenior Technical architecture (Data)Author Commented:
1. so is all CXPACKET belongs to the same SPID ? this kind of query should have the same SPID, right? then all CXPACKET expected to belongs to the same query?

"but each thread with CXPACKET is currently waiting for some other threads."

waiting for some other threads, which means other query executing in parallel ?

2. one example is, if the data size is small and good enough to execute in serial mode and serial mode is better than parallel you mean ?

3. "cold buffers these should reside on their own spindles."

what is that mean ?
ste5anSenior DeveloperCommented:
1. While have no parallel query at hand to test, affair yes.

Take a look at the presentation. A CXPACKET happens normally in the scenario on page 13.
A CXPACKET wait per se is not bad, just an high number may indicate a problem with query plan.

2. Yes.

3. When you need to process data from disk from different partitions at the same time, then you get better IO, when each partition resides on there own disk(s).

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:
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.