Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.
Questions tagged [parallelism]
216 questions
71
votes
4 answers
Can a single PostgreSQL query use multiple cores?
In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
Alireza
- 3,676
- 10
- 38
- 44
32
votes
2 answers
Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?
Much has been written about the perils of Scalar UDFs in SQL Server. A casual search will return oodles of results.
There are some places where a Scalar UDF is the only option, though.
As an example: when dealing with XML: XQuery can't be used as…
Erik Reasonable Rates Darling
- 45,549
- 14
- 145
- 532
26
votes
2 answers
Need to understand parallel query execution error
Today we experienced a degradation in performance on our production sql server. Durring the time this occurred we logged several "The query processor could not start the necessary thread resources for parallel query execution" errors. The reading…
Lumpy
- 2,129
- 9
- 34
- 43
15
votes
3 answers
High CXPACKET and LATCH_EX waits
I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait events.
The system consists of 3 processing SQL…
stumct
- 191
- 1
- 1
- 4
15
votes
2 answers
If a Parallelism Exchange Event deadlock is victim-less, is it a problem?
We're seeing a lot of these Intra-Query Parallel Thread Deadlocks in our Production environment (SQL Server 2012 SP2 - yes...I know...), however when looking at the Deadlock XML that has been captured via Extended Events, the victim-list is…
Mark Sinkinson
- 10,657
- 4
- 47
- 54
15
votes
1 answer
sp_cursoropen and parallelism
I'm running into a performance problem with a query that I can't seem to get my head around.
I pulled the query out of a cursor definition.
This query takes seconds to execute
SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND…
Tom V
- 15,752
- 7
- 66
- 87
14
votes
3 answers
Parallel Statistics Update
In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don't see an option specifying MAXDOP with update…
SQL Learner
- 3,001
- 4
- 29
- 39
13
votes
6 answers
Run stored procedures in parallel
I'm looking to try and run the same stored procedure multiple times with different parameters but at the same time.
I'm using SQL 2014
The reason for this is that the procedure takes around 7 hours to complete. It actually does the same process…
user124569
- 137
- 1
- 1
- 5
12
votes
1 answer
Why is a parallel top N sort apparently much more CPU efficient than a serial top N sort?
I'm testing against SQL Server 2019 CU14. I have a pure row-mode query that selects the top 50 rows from a complicated view. The full query takes 25426 ms of CPU time at MAXDOP 1 and 19068 ms of CPU time at MAXDOP 2. I'm not surprised that the…
Joe Obbish
- 32,976
- 4
- 74
- 153
12
votes
1 answer
How can I disable parallel queries in PostgreSQL?
I want to benchmark some things in PostgreSQL. When I do this I normally turn off some of the features the database is currently using, just for my session, to see what the second-best plan is.
Though setting max_parallel_works = 0 , I still get a…
Evan Carroll
- 65,432
- 50
- 254
- 507
12
votes
4 answers
Dealing with CXPACKET waits - setting cost threshold for parallelism
As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits.
I know the knee-jerk solution is to turn off all parallelism by setting MAXDOP to 1 - sounds like a…
marc_s
- 9,052
- 6
- 46
- 52
12
votes
2 answers
Can I refactor this query to get it to run in parallel?
I have a query that takes about 3 hours to run on our server--and it doesn't take advantage of parallel processing. (about 1.15 million records in dbo.Deidentified, 300 records in dbo.NamesMultiWord). The server has access to 8 cores.
UPDATE…
rsjaffe
- 383
- 2
- 14
12
votes
3 answers
Why is an aggregate query significantly faster with a GROUP BY clause than without one?
I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one.
For example, this query takes almost 10 seconds to run
SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1
While this one takes less…
Rachel
- 8,547
- 20
- 51
- 74
11
votes
2 answers
Is support for Parallel Scalar UDF a reasonable feature request?
It is fairly well documented that scalar UDF's force an overall serial plan.
Running functions in parallel
Given a large number of rows coming into a point in the pipeline where a UDF must be calculated, why can't the engine just distribute them…
crokusek
- 2,110
- 4
- 25
- 34
11
votes
2 answers
Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?
Setup
DROP TABLE IF EXISTS #EmptyTable, #BigTable
CREATE TABLE #EmptyTable(A int);
CREATE TABLE #BigTable(A int);
INSERT INTO #BigTable
SELECT TOP 10000000 CRYPT_GEN_RANDOM(3)
FROM sys.all_objects o1,
sys.all_objects o2,
…
Martin Smith
- 87,941
- 15
- 255
- 354