43
votes
Can a single PostgreSQL query use multiple cores?
PostgreSQL 9.6+ onwards, would start to see Parallel-Query finally coming to PostgreSQL.
For e.g. Concepts like Parallel Scan / Parallel Join / Parallel Aggregates are now already baked in, with ...
- 533
36
votes
Accepted
Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?
Yes if you:
are running SQL Server 2014 or later; and
are able to run the query with trace flag 176 active; and
the computed column is PERSISTED
Specifically, at least the following versions are ...
- 82.1k
25
votes
SQL not engaging parallelism for extremely large query
What's weird is while experimenting, we even dropped the threshold back down to the default value of 5 seconds, and it still won't run parallel. What could be preventing it?
There are many reasons ...
- 82.1k
20
votes
Accepted
MAXDOP = 1, Query Hints and Cost Threshold For Parallelism
If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually ...
- 82.1k
16
votes
Accepted
In SQL Server, is parallelism per operator, or something else?
He says that in a parallel plan, every operator gets DOP threads.
No. This is at best misleading, but closer to being simply wrong.
In a serial plan, every operator 'gets' one thread, but that ...
- 82.1k
15
votes
Accepted
If a Parallelism Exchange Event deadlock is victim-less, is it a problem?
I wouldn't be surprised if this is the way the deadlock graph looks when an intra-query parallel deadlock is resolved by an exchange spill (so there is no victim, except performance).
You could ...
- 82.1k
15
votes
Accepted
Why is a parallel top N sort apparently much more CPU efficient than a serial top N sort?
It's hard to be sure based on an anonymised plan without a repro, but the first mechanism that springs to mind is deferred expression evaluation.
In the serial plan, the Sort may be responsible for ...
- 82.1k
14
votes
Did Microsoft change query optimizer regarding number of files and parallelism
That was an urban legend that Microsoft's Bob Dorr debunked back in 2007:
This information has been taken out of context and propagated
incorrectly as SQL Server has a thread per database file so ...
- 42.6k
13
votes
Run stored procedures in parallel
At one point I answered this question over at StackOverflow, but it seems like it would be useful to have that information at DBA.SE as well, revised and updated.
Just to be totally explicit: TSQL ...
- 9,784
13
votes
Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?
In addition to @Paul's excellent Yes #1, there is actually a Yes #2 that:
works as far back as SQL Server 2005,
does not require setting a trace flag,
does not require that the computed column be ...
- 68.1k
13
votes
Accepted
How can I disable parallel queries in PostgreSQL?
From the docs,
max_parallel_workers_per_gather (integer) Sets the maximum number of workers that can be started by a single Gather or Gather Merge node. Parallel workers are taken from the pool of ...
- 61.4k
12
votes
Accepted
In SQL Server, How Does Parallelism Change Memory Grants?
Sup?
For SQL Server queries that require additional memory, grants are derived for serial plans. If a parallel plan is explored and chosen, memory will be divided evenly among threads.
Memory grant ...
- 38.2k
11
votes
Is support for Parallel Scalar UDF a reasonable feature request?
As Paul has rightly mentioned in his answer, there is no fundamental reason why scalar UDFs could not be executed using parallelism. However, apart from the implementation challenges, there is another ...
- 131
11
votes
Can I refactor this query to get it to run in parallel?
The UDF is preventing parallelism. It also is causing that spool.
You could use CLR and a compiled regex to do your search and replace. It doesn't block parallelism as long as the required attributes ...
- 83.1k
11
votes
Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode
Forrest is mostly right, but the finer details are:
SQL Server can't parallelize modifications to table variables, which your function uses.
Prior to SQL Server 2017's Interleaved Execution, row ...
- 38.2k
10
votes
sp_who returns more rows than the value of "max degree of parallelism"
Shouldn't it be at most 8 rows in this case?
No, because a single execution plan can use more threads than specified by the effective degree of parallelism (DOP).
Only a maximum of DOP threads will ...
- 82.1k
10
votes
Accepted
MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?
Batch statements are only ever executed serially in the order they appear in the batch.
Now, if you have two statements sent to the server by two different batches, they will run independently and ...
- 69.5k
10
votes
Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode
SQL Server cannot parallelize multi-statement TVFs, which is what yours is. Only Inline TVFs can be parallelized.
- 4,089
10
votes
Accepted
Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?
Not running the probe side of the join when the build is empty is an optimization. It is not available for parallel row mode hash join when the probe side has a child branch i.e. when there is an ...
- 82.1k
10
votes
Is SQL Server able to use internal parallelism for an update statement?
No, SQL Server does not support parallel update*. That said, it is unclear if parallelism would assist an I/O-bound operation in your scenario.
SQL Server does support parallel insert to a row store ...
- 82.1k
9
votes
High CXPACKET and LATCH_EX waits
CXPACKET can be accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the case (and I believe it is, based on the question) then the MAXDOP value should ...
- 111
9
votes
Accepted
What's the biggest bottleneck (time) in writing data to a SQL database?
You need to go an read:
The Data Loading Performance Guide
How to analyse SQL Server performance
Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)
bcp.exe
Transferring Data ...
- 51.6k
9
votes
Accepted
Parallelism with temp table but not table variable?
Quote below from this question on the differences between table variables and temp tables. Look at the section on parallelism.
Queries that insert into (or otherwise modify) @table_variables ...
- 24k
9
votes
SQL Parallel Stored Procedure execution using CLR - Performance
Has anyone used CLR for parallelizing Stored Procedures in Critical OLTP Production workloads ?
Parallelizing? Yes. For "critical OLTP Production" systems, not really. This is not to say that you can'...
- 68.1k
9
votes
Accepted
Serial Plan in plan cache
At first glance, this sounds like a classic parameter sniffing problem.
SQL Server builds an execution plan for the first set of parameters that get called when the plan needs to be compiled, and ...
- 42.6k
9
votes
Accepted
How can a cheap function in a SELECT make the whole query slow?
Function inlining is important, and applies here, too. Your PL/pgSQL function cannot be inlined. (Besides being overkill to even call another function for the trivial expression.) But since it's still ...
- 170k
8
votes
Round robin vs Demand Parallelism partitioning
Should I always use round robin instead
Only if you find it produces "better" results for your particular workload, or for particular queries within that workload.
In general, I would say no. My ...
- 82.1k
8
votes
How can I get rid of an unhelpful parallel branch when unpivoting a single row?
I am able to get the desired plan shape with a serial loop join when all of the following are true:
An APPLY or CROSS JOIN is used instead of UNPIVOT
The APPLY contains no outer references
The source ...
- 31.8k
8
votes
How can I get rid of an unhelpful parallel branch when unpivoting a single row?
It's impossible for both parallel branches to execute at the same time.
Execution starts at the left edge of the plan. The nested loops branch is running (opening, waiting for data) when the table ...
- 82.1k
8
votes
Accepted
How many threads being used while query execution?
I am confused and can't determine how many threads were running during query execution.
To understand that, you really need to look at the execution plan. Here's what I get for this query on SQL ...
- 28.9k
Only top scored, non community-wiki answers of a minimum length are eligible