Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.
Questions tagged [parallelism]
181 questions
65
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,526
- 10
- 34
- 41
31
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 Darling
- 32,622
- 13
- 110
- 249
24
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,059
- 8
- 29
- 41
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
- 9,958
- 3
- 40
- 52
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,330
- 7
- 58
- 86
14
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
- 181
- 1
- 1
- 4
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
- 2,961
- 4
- 24
- 39
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
- 8,613
- 6
- 43
- 51
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
- 12
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,229
- 20
- 46
- 74
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
- 77,689
- 15
- 224
- 316
11
votes
2 answers
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 go parallel?
I haven’t been able to dig up this…
Martin Bansey
- 113
- 1
- 5
11
votes
2 answers
Using multiple cores for single MySQL queries on Debian
I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four.
I am doing expensive joins on large tables, which can take several minutes, but I see on the…
Thomas
- 307
- 2
- 3
- 9
10
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
- 1,956
- 3
- 21
- 32
10
votes
3 answers
Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode
One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the application.
So i converted those SCALAR functions to…
user9516827
- 1,265
- 1
- 8
- 27