Parallelism (Repartition Streams) 55% in exec Plan, can I fight it ?

Question

Parallelism (Repartition Streams) 55% in exec Plan, can I fight it ?

mario17

SSCertifiable

Points: 6078
More actions
October 31, 2014 at 10:26 am

#291629

Hi,
I just saw that symbol in my plan for complicated sp and don't get it if I can improve anything to make it faster.
Parallelism
(Repartition Streams)
55%
I assume that we have parallelism running in this case, is there any way to look inside this box and see how to improve it?
Thanks
Mario

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

Yes you have parallelism running in this case. Here are some questions:

1. What is the max degree of parallelism setting for the SQL Instance? If it is left at the default of 0 and you have a large multi-core server with hyperthreading on, then you are using too many processors (SQL Server always uses them all when parallelizing) so going parallel may actually slow the query down. See http://support.microsoft.com/kb/2806535

2. What is the Cost Threshold for Parallelism setting on the SQL Instance. If it is left at the default of 5 it should be increased as 5 it too low for today's servers.

3. What is the Cost of the plan? On the Left Most operator

4. Are there missing indexes being reported?

The best way to eliminate parallelism (not always necessary) is to tune the queries and database so that it is processing as few rows as possible to satisfy the requirements and doesn't need to parallelize. This is usually done with proper SQL Server configuration and indexing.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mario17 SSCertifiable Points: 6078 More actions · Answer 2

Thanks, Jack

As an app developer, I really don't have this info, and can't measure any load on server.

I"ll talk to our server guy, for now I'm trying to tune my sql to make it faster, this is ultimate task.

For now I think I'll try to get rid of parallelism to actually see which operation is most costly, or probably I can see it inside the single parallel stream too ?

Best

Mario

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Since you can see the plan you should be able to see 3 & 4 from my first post which can get you started on tuning.

You can force the query to execute with parallelism by applying the Query hint OPTION (MAXDOP 1) at the end of the query like this:

Select * from table Where column = value OPTION (MAXDOP 1);

For the most part the query shape will stay the same so you should be able to tune from the existing plan. You should start by looking at SCAN operations and the FAT lines that signify more rows being processed as those operations are what usually cause parallelism.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

roshithkooleri SSC Rookie Points: 35 More actions · Answer 4

I had the same issue. Repartition Streams showing 82%!!!.
After some analysis, I could find an issue in Query which I am using.
Here adding query before and after:

Before:

SELECT
     field1
    ,field2
    ,field3
FROM [T1] a WITH (NOLOCK)
        LEFT OUTER JOIN [T2] b ON
            a.[id] = b.[id]
        LEFT OUTER JOIN [T3] c ON
            c.[id] = b.[id]
        LEFT OUTER JOIN [T4] d ON
            d.[id] = c.[id]
   LEFT OUTER JOIN [T5] e ON
            e.[id] = d.[id]
AND b.text = 'test'

After:

SELECT
     field1
    ,field2
    ,field3
FROM [T1] a WITH (NOLOCK)
        LEFT OUTER JOIN [T2] b ON
            a.[id] = b.[id]
  AND b.text = 'test'
        LEFT OUTER JOIN [T3] c ON
            c.[id] = b.[id]
        LEFT OUTER JOIN [T4] d ON
            d.[id] = c.[id]
   LEFT OUTER JOIN [T5] e ON
            e.[id] = d.[id]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

roshithkooleri - Monday, June 26, 2017 3:48 AM
I had the same issue. Repartition Streams showing 82%!!!.
After some analysis, I could find an issue in Query which I am using.
Here adding query before and after:
Before:
SELECT
     field1
    ,field2
    ,field3
FROM [T1] a WITH (NOLOCK)
        LEFT OUTER JOIN [T2] b ON
            a.[id] = b.[id]
        LEFT OUTER JOIN [T3] c ON
            c.[id] = b.[id]
        LEFT OUTER JOIN [T4] d ON
            d.[id] = c.[id]
   LEFT OUTER JOIN [T5] e ON
            e.[id] = d.[id]
AND b.text = 'test'
After:
SELECT
     field1
    ,field2
    ,field3
FROM [T1] a WITH (NOLOCK)
        LEFT OUTER JOIN [T2] b ON
            a.[id] = b.[id]
  AND b.text = 'test'
        LEFT OUTER JOIN [T3] c ON
            c.[id] = b.[id]
        LEFT OUTER JOIN [T4] d ON
            d.[id] = c.[id]
   LEFT OUTER JOIN [T5] e ON
            e.[id] = d.[id]

The two queries yield different results:

DROP TABLE #T1;CREATE TABLE #T1 (ID INT, field1 VARCHAR(20))
INSERT INTO #T1 (ID, field1) VALUES 
(1,'#T1'), (2,'#T1'), (3,'#T1'), (4,'#T1'), (5,'#T1'), (6,'#T1')DROP TABLE #T2;CREATE TABLE #T2 (ID INT, field2 VARCHAR(20), [text] VARCHAR(10))
INSERT INTO #T2 (ID, field2, [text]) VALUES (1,'#T2','test'), (2,'#T2',''), (3,'#T2','test'), (4,'#T2',''), (5,'#T2','test'), (6,'#T2','')

DROP TABLE #T3;CREATE TABLE #T3 (ID INT)
INSERT INTO #T3 VALUES (1), (2), (3), (4), (5), (6)DROP TABLE #T4;CREATE TABLE #T4 (ID INT)
INSERT INTO #T4 VALUES (1), (2), (3), (4), (5), (6)

DROP TABLE #T5;CREATE TABLE #T5 (ID INT, field3 VARCHAR(20))
INSERT INTO #T5 (ID, field3) VALUES (1,'#T5'), (2,'#T5'), (3,'#T5'), (4,'#T5'), (5,'#T5'), (6,'#T5')

SELECT
a.field1,
b.field2,
e.field3
FROM #T1 a WITH (NOLOCK)
LEFT OUTER JOIN #T2 b ON a.[id] = b.[id]
LEFT OUTER JOIN #T3 c ON c.[id] = b.[id]
LEFT OUTER JOIN #T4 d ON d.[id] = c.[id]
LEFT OUTER JOIN #T5 e ON e.[id] = d.[id] AND b.text = 'test'

SELECT
a.field1,
b.field2,
e.field3
FROM #T1 a WITH (NOLOCK)
LEFT OUTER JOIN #T2 b ON a.[id] = b.[id] AND b.text = 'test'
LEFT OUTER JOIN #T3 c ON c.[id] = b.[id]
LEFT OUTER JOIN #T4 d ON d.[id] = c.[id]
LEFT OUTER JOIN #T5 e ON e.[id] = d.[id]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden