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 ...
- 90.3k
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 ...
- 90.3k
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 ...
- 90.3k
17
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 ...
- 64.7k
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 ...
- 90.3k
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 ...
- 10k
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 ...
- 43.2k
14
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 ...
- 90.3k
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 ...
- 69.5k
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 ...
12
votes
Accepted
Tale of two servers. What could be limiting queries to running on two cores? MDOP set to 16
👨🎓👨🎓👨🎓
An educated guess would be that Microsoft hates you for using Standard Edition, and your batch mode queries are being limited to a DOP of 2.
See this post for details on that, and ...
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 ...
- 86.5k
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
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 ...
- 70.5k
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 ...
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,179
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 ...
- 90.3k
10
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 ...
- 182k
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 ...
- 90.3k
10
votes
Accepted
How should MAXDOP be configured on stacked instances?
you're asking the wrong question
Stacked instances are a terrible mistake if you truly care about the performance of both servers, because they're going to both be fighting over the same resources ...
10
votes
Accepted
Why is the tipping point between a serial and parallel plan not exactly the point where the serial plan is costed less?
The tipping point is as you expect. The showplan information doesn't accurately reflect optimizer costing.
The first two queries (parallel natural and serial hinted) both have a cost of 62.6855 at ...
- 90.3k
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
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'...
- 69.5k
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 ...
- 43.2k
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 ...
- 90.3k
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 ...
- 32.6k
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 ...
- 90.3k
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 ...
- 29.8k
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 ...
- 29.8k
8
votes
Accepted
SQL Server Scalar UDF Parallelism Mystery!
You need a non-trivial plan if you want parallelism, plus the optimizer must be able to find a valid parallel plan for the query.
For example, modifying your first query (which did not use parallelism)...
- 90.3k
Only top scored, non community-wiki answers of a minimum length are eligible