Skip to main content
Share Your Experience: Take the 2024 Developer Survey
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 ...
Robins Tharakan's user avatar
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 ...
Paul White's user avatar
  • 85.5k
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 ...
Paul White's user avatar
  • 85.5k
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 ...
Paul White's user avatar
  • 85.5k
17 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 ...
Paul White's user avatar
  • 85.5k
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 ...
Paul White's user avatar
  • 85.5k
15 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 ...
Evan Carroll's user avatar
  • 63.5k
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 ...
Paul White's user avatar
  • 85.5k
14 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 ...
BradC's user avatar
  • 9,964
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 ...
Brent Ozar's user avatar
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 ...
Solomon Rutzky's user avatar
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 ...
Erik Darling's user avatar
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 ...
Martin Smith's user avatar
  • 84.8k
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 ...
Karthik's user avatar
  • 131
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 ...
Erik Darling's user avatar
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 ...
Paul White's user avatar
  • 85.5k
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 ...
Hannah Vernon's user avatar
  • 70.1k
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.
Forrest's user avatar
  • 4,169
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 ...
Paul White's user avatar
  • 85.5k
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 ...
Paul White's user avatar
  • 85.5k
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 ...
Gustav's user avatar
  • 111
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 ...
Kenneth Fisher's user avatar
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'...
Solomon Rutzky's user avatar
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 ...
Brent Ozar's user avatar
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 ...
Erwin Brandstetter's user avatar
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 ...
Paul White's user avatar
  • 85.5k
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 ...
Joe Obbish's user avatar
  • 32.2k
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 ...
Paul White's user avatar
  • 85.5k
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 ...
Josh Darnell's user avatar
  • 29.3k
8 votes
Accepted

No parallelism in Express Edition of SQL Server

The limitation is mentioned in the Query Processing Architecture Guide under the "Parallel Query Processing" section: Parallel plans not supported for Desktop and Express edition. The ...
Josh Darnell's user avatar
  • 29.3k

Only top scored, non community-wiki answers of a minimum length are eligible