Parallel Execution Plan - Or not.

Question

Post reply

Parallel Execution Plan - Or not.

SteveOC

Ten Centuries

Points: 1257
More actions
December 18, 2022 at 12:23 pm

#4127107

Hello.
SQL Server 2014 - 3 Numa Nodes - 6 processors in each Numa Node (not that is not the issue) - MAXDOP = 6.
I have been running some testing with Substring Queries (LIKE) vs FTS Catalog (CONTAINS) Search and experimenting with CHARINDEX and COLLATION (Binary).
As part of the testing with CHARINDEX and COLLATION I have seen something that I do not understand in relation to parallelism - and this is not the first time I have seen this (but I cannot recall what I was doing - it was unrelated).
My basic SUBSTRING query gets a parallel execution plan - unsuprisngly since the table it is referencing has over 170 million rows. It is relatively slow (today 22 seconds - can take over a minute). I basically get an NCI scan and 32K key lookups - and parallelism.
Very Simple (real world in my world) Query :
select * from rsitem where alternatecode like('%b3%')
If I use a Binary Collation Sequence it is faster - 7 seconds - I get a CI scan - and parellelism
declare @s-2 nvarchar(30) = 'B3' -- random alphanumeric string
select * from rsitem
where alternatecode collate Latin1_General_BIN like '%'+@s+'%' collate Latin1_General_BIN
However when I add the CHARINDEX to the mix (with or without Binary Collation) I have to use a MAXDOP hint AND set the value to 8 or above to get a parallel execution plan and without it is slow - today 2min 18 secs - CI Scan - no parellelism (dop=1).
declare @s-2 nvarchar(30) = 'B3' -- random alphanumeric string
select * from rsitem
where charindex(@s , alternatecode)>0
If I add MAXDOP of 8 I get the same plan - CI Scan but parallel - takes 16s.
declare @s-2 nvarchar(30) = 'B3' -- random alphanumeric string
select * from rsitem
where charindex(@s , alternatecode)>0
option(maxdop 8)
If I specify MAXDOP 7 I get the same plan - CI Scan - but DOP =1 - no parallelism and takes @2 mins.
I have tried forcing both the 2014 and previous CE and get the exact same results - not surprisingly as it is a very simple execution plan.
So 2 questions :
1.) What is it about CHARINDEX that causes a parallel plan to become non-parallel?
2) What is the significance of MAXDOP 8 or above (given the NUMA and MAXDOP instance settings) that causes a parellel execution - that is not triggered by a value below 8?
TIA
Steve O,

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

what is the CTFP on that server? that will also affect it greatly.

SteveOC Ten Centuries Points: 1257 More actions · Answer 2

Hello Frederico.

Thanks for the response - COTP is set to 45.

Why would COTP affect the same query differently, whether it has CHARINDEX or not, and why would a MAXDOP of 7 or 6 or less not trigger a parallel plan but a MAXDOP of 8 (or anything higher) does? An Index Scan (CI) sounds to me like an expensive query that would be eligible for parallelism if it is reading 170Million rows or am I missing something?

SteveOC Ten Centuries Points: 1257 More actions · Answer 3

Interestingly I found that a setting of 8 (vs 6) does speed up the original slow query significantly it testing.

Perhaps it is time to increase the value due to increased data volumes since last changed.

I don't want to set it too high due to convoluted queries with sub-queries that end up with significant numbers of worker threads (not suprisingly), shared workloads (set at instance level in 2014) and the whole near vs far memory in NUMA.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

SteveOC wrote:

Interestingly I found that a setting of 8 (vs 6) does speed up the original slow query significantly it testing.
Perhaps it is time to increase the value due to increased data volumes since last changed.
I don't want to set it too high due to convoluted queries with sub-queries that end up with significant numbers of worker threads (not suprisingly), shared workloads (set at instance level in 2014) and the whole near vs far memory in NUMA.

Just to be sure, I wouldn't make such a change to the settings based on one example whether I knew precisely the reason or not. Splitting and regathering steams can be incredibly expensive by themselves and, if you have even one "long leg" due to data skew, you can really put the whammy on your server.

A long time ago when we went from 16 to 32 processors, everyone thought it would be a great idea to double the max-dop from 4 to 8. Back then, it didn't make much difference due to the small size of the data. We now have individual tables between 1/2 and 1 TB. We had an issue with our CPUs and had to temporarily drop to 16 processors and we dropped the max dop back to 4. Some of our 6 hour jobs suddenly started running in only 2 to 3 hours.

I'd also be considering a redesign to avoid such non-SARGable queries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EdVassie SSC Guru Points: 60472 More actions · Answer 5

You started by saying you were experimenting with full text search and some alternatives. You seem to have ended in a rabbit hole wondering why some queries run in parallel.

Jeff has given some good advice about parallelism, I will focus on FTS.

If your real world business problem is to find arbitrary characters in a string, then using LIKE may be your best choice solution. If your business problem is to find arbitrary delimeter-separated words then FTS has a lot to offer, particularly if you want to includes FORMSOF the search criteria (plurals, synonyms etc) or multiple words in your search criteria. FTS can use its catalogues to identify result rows against multiple criteria in a single pass through the data.

If you are looking at these things as a learning exercise then carry on as you are. If you have a business problem to solve then maybe let us know what it is so we can give business-problem focused advice.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

EdVassie SSC Guru Points: 60472 More actions · Answer 6