Questions tagged [parallelism]
Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.
185
questions
66
votes
4
answers
79k
views
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?
- 3,546
31
votes
2
answers
3k
views
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 ...
- 33.4k
24
votes
2
answers
33k
views
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 ...
- 2,069
15
votes
1
answer
2k
views
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 ...
- 15.4k
15
votes
2
answers
5k
views
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 ...
- 10k
14
votes
3
answers
6k
views
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'...
- 2,961
14
votes
3
answers
37k
views
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 ...
- 181
12
votes
4
answers
3k
views
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 ...
- 8,643
12
votes
2
answers
864
views
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). ...
- 383
12
votes
3
answers
29k
views
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 ...
- 8,259
11
votes
2
answers
3k
views
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 ...
- 1,966
11
votes
2
answers
4k
views
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 ...
- 113
11
votes
2
answers
21k
views
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, ...
- 307
11
votes
2
answers
730
views
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....
- 77.9k
10
votes
3
answers
685
views
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 ...
- 1,285
10
votes
1
answer
8k
views
SQL not engaging parallelism for extremely large query
I have a very large query (~630 lines) that involves a lot of nested SELECT statements and pulls from multiple views. Our SQL server has parallelism set to 2 with a threshold of 95 (set that way ...
- 201
10
votes
6
answers
44k
views
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 ...
- 107
10
votes
1
answer
729
views
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 ...
- 30.3k
9
votes
5
answers
10k
views
Parallelism Best Practices
What are the best practices with setting parallelism in general? I know that SQL Server defaults to 0 to use all available processors, but in what instance would you want to change this default ...
anon
9
votes
2
answers
489
views
How can I get rid of an unhelpful parallel branch when unpivoting a single row?
Consider the following query that unpivots a few handfuls of scalar aggregates:
SELECT A, B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ...
- 30.3k
9
votes
1
answer
352
views
In SQL Server, How Does Parallelism Change Memory Grants?
I've heard conflicting things about memory grants for parallel select queries:
Memory grants get multiplied by DOP
Memory grants get divided by DOP
Which is it?
- 33.4k
8
votes
4
answers
6k
views
Why are so many MPP solutions based on PostgreSQL instead of MySQL?
Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there ...
- 1,145
8
votes
1
answer
706
views
Did Microsoft change query optimizer regarding number of files and parallelism
Did Microsoft change query optimizer regarding number of files and parallelism? Does the optimizer no longer factor in the number of files to determine the degree of parallelism for a query? If so, ...
- 156
8
votes
2
answers
1k
views
Round robin vs Demand Parallelism partitioning
This is related to having a common key driver on top of the query with a nested loops, and the parallelism for the rows coming out of driver being either demand type or round robin type. I would have ...
- 304
7
votes
2
answers
806
views
In SQL Server, is parallelism per operator, or something else?
I work with a really old DBA who says a lot of weird stuff. Dude has an O'Reilly book that only has an amoeba on the cover.
At lunch we were talking about parallelism, because our new server has 24 ...
- 197
7
votes
1
answer
2k
views
MAXDOP not working?
I wanted to test out MAXDOP on my computer. So I set MAXDOP to 2 for a specific query. However, when I looked at my logical processors in Task Manager as I ran the query, it looked like they were all ...
- 651
7
votes
6
answers
11k
views
CXPACKET Waits performance tune for SQL Server 2008
I have a SQL Server 2008 query that is working on millions of records. The query is w/in a proc that is run nightly by a job. The query can take a full day to run when I first put it on the server, ...
- 71
7
votes
2
answers
8k
views
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?
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?
If the first SELECT ...
- 24.2k
7
votes
1
answer
440
views
STATISTICS IO for parallel index scan
Assume that there is a table with clustered index
create table [a_table] ([key] binary(900) unique clustered);
and some data
insert into [a_table] ([key])
select top (1000000) row_number() over (...
- 2,294
7
votes
1
answer
1k
views
How does MS SQL Server decide when it can use "Parallelism" when executing queries?
How does MS SQL Server decide when it can use "Parallelism" when executing queries? I recently posted another question here that, when looking at the actual execution plan used parallelism when I had ...
- 952
7
votes
1
answer
610
views
Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED
Running a processing job in multiple parallel batches which basically reads big chunks of rows to update a few - it's OK here to use a lower transaction level since I know the relevant values won't be ...
- 171
7
votes
2
answers
223
views
Parallel option performing worse on cluster
I'm trying out oracles parallel option on a cluster and surprisingly I am getting worse results with the parallel option. I was expecting some improvement with the parallel option but certainly not ...
- 273
6
votes
2
answers
444
views
Non parallel plan only in Azure when SESSION_CONTEXT is used
I'm observing strange difference between query plans that I'm getting on my local machine and on Azure SQL. I'm trying to implement row level security, where I read user identifier from ...
- 263
6
votes
1
answer
9k
views
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.
...
- 55.7k
6
votes
1
answer
262
views
SQL Server Scalar UDF Parallelism Mystery!
Referencing this blog post by Erik Darling on the website of my favorite SQL Server guru - Brent Ozar:
When you select from that table alone, it shows "CouldNotGenerateValidParallelPlan".
...
6
votes
2
answers
567
views
Can a server wide setting of 'Max Degree of Parallelism' = 1 cause Brent Ozar's sp_BlitzCache to flag execution plan as 'forced-serialization'?
I am using Brent Ozar's sp_BlitzCache store procedure and I'm attempting to nail down why it is reporting:
"Something in your plan is forcing a serial query. Further
investigation is needed if ...
- 443
6
votes
3
answers
635
views
Parallel Plan Selection
I have a weird query plan problem. I have two databases (Let's call them DB1 and DB2) where both are sitting in the same SQL-Server instance and have identical schema. In there, we have a couple ...
- 101
6
votes
1
answer
1k
views
When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?
The manual for Postgres 12 says:
The following operations are always parallel restricted.
Scans of common table expressions (CTEs).
Scans of temporary tables.
...
Further down on ...
- 150k
6
votes
1
answer
1k
views
In SQL Server, what is "Partitioned Table Parallelism" that is only available in Enterprise Edition?
I am reviewing the differences between Standard and Enterprise editions of SQL Server and can't reproduce the differences advertised in this demo that explains the differences - the performance I ...
- 243
5
votes
3
answers
14k
views
How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?
Problem
We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year.
The SQL-Server instance in question has very high CPU usage (...
- 53
5
votes
1
answer
243
views
sp_who returns more rows than the value of "max degree of parallelism"
sp_configure returns the following values on a server.
name minimum maximum config_value run_value
max degree of parallelism 0 32767 8 8
However, ...
- 1,475
5
votes
3
answers
1k
views
Why is cost threshold for parallelism ignored?
SQL Server 2012 SP2 Enterprise Edition. Users complaining of slowness. Monitoring tool shows highest wait event is CXPACKET.
Instance settings
MAXDOP: 8
Cost Threshold for Parallelism: 175
Ran ...
- 108
5
votes
1
answer
333
views
Are there practical scenarios where inserts + deletes are faster than updates? (SQL Server)
I'm just wondering if there are common scenarios where an insert/ delete combination is faster than an update else insert function.
Here is my specific example.
I have to update a database with ...
- 1,493
5
votes
1
answer
291
views
What's the easiest and most accurate way to visualize parallel thread usage in SQL Server?
Following up on my other question here, I'd like an easy way to visualize the answer.
I've tried using a query like this, but the number of "running" threads can exceed DOP sometimes.
SELECT ost....
- 197
5
votes
1
answer
294
views
What is the minimum required compatibility level to use parallel SELECT INTO?
What is the minimum required compatibility level to use parallel SELECT INTO in SQL Server 2014?
- 7,140
5
votes
1
answer
3k
views
Executing SPs in parallel (maybe with a cursor) [closed]
We have an 3rd party accounting system that runs on SQL Server 2008 R2. To insert invoices into this accounting system, we have to use the API that comes with it (which is just a set of encrypted SPs)....
- 151
5
votes
1
answer
4k
views
SQL SERVER 2016 high CPU
I have a brand new server with SQL Server 2016.
It has 24 CPU, about 80go of RAM.
The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all.
If I look at the 'execute ...
4
votes
3
answers
1k
views
Setting maxdop 1 vs setting the threshold for parallelism high
I am looking at a possible fringe case for parallelism settings.
I've long since followed the credence that maxdop 1 is a viable option for Microsoft Dynamics CRM databases. As the nature of the ...
- 2,501
4
votes
2
answers
661
views
Can WAITFOR increase CXPACKET waits?
If I have some T-SQL code like this:
While @done<1
Begin
WAITFOR DELAY '00:00:30';
Set @done=<some select statement>
End
Is this likely to increase waits for CXPACKET? Presumably the other ...
- 669
4
votes
1
answer
406
views
No parallelism in Express Edition of SQL Server
I have SQL Server 2019 Express Edition (CU8) on Windows Server 2019 (1809 version) and all of my queries go serial with NoParallelPlansInDesktopOrExpressEdition in NoparallelPlanReason property.
Is it ...
- 935