Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [parallelism]

Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.

35 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
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 ...
Guillaume Philipp's user avatar
4 votes
0 answers
559 views

Non parallel plan with row level security predicate to check server role

While exploring Row Level Security I picked up that whenever I had it enabled it was not creating any parallel query plans. In SQL Server 2019 this gave me NonParallelPlanReason="...
bla9x's user avatar
  • 41
3 votes
0 answers
552 views

What is the cause of persistent SQL Server deadlocks with a chosen victim, where the victim is never stopped, and no transactions are rolled back?

We are experiencing an issue with Microsoft SQL Server where every few days of running our application against the database, it goes into a state where it keeps deadlocking. It logs a deadlock about ...
Mads K's user avatar
  • 31
3 votes
0 answers
180 views

Evaluating Success of CTFP Value Change

We have recently made a change to the CTFP setting on our test server with a view to rolling this out to live. Our current CTFP is 15 so we tried setting it to 50 which is often seen as a better value....
SE1986's user avatar
  • 1,970
3 votes
0 answers
242 views

Why does removing a RID by creating a clustered index lose parallelism and slow down execution by 2X

If I remove a RID lookup by replacing 2 non-clustered indices (non unique) with 2 identical clustered indicies (non-unique) where there weren't any before, why does this query slow down by a factor ...
crokusek's user avatar
  • 2,120
3 votes
0 answers
467 views

Are the following statements concerning sys.dm_exec_requests true?

Parallel plans mean that cpu_time can be greater than total_elapsed time. Therefore, per-query wait time is not possible to determine accurately from DMVs on servers with more than one CPU thread. ...
James Lupolt's user avatar
  • 4,258
2 votes
0 answers
983 views

Intra-Query Parallelism Deadlock Resolution

I want to know how SQL Server handles intra-query parallelism deadlocks, and whether it kills one of the threads involved in the deadlock? Will there be a change in the result set when this deadlock ...
tarun_'s user avatar
  • 21
2 votes
0 answers
6k views

Oracle parallel query: finding out which Producer a Consumer is waiting for

One of our materialized view refresh's (part of a daily ETL routine) was hanging today; it usually completes within 5 minutes, but this time, I had to kill it after 23 hours. V$SESSION contained this ...
Frank Schmitt's user avatar
2 votes
1 answer
99 views

Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance Limit

We regularly run a contest that is becoming quite popular. In the contest, we need to select the combinations of contestants from 8 groups whose cost is under a threshold, and who combined have the ...
Steven J. Garner's user avatar
2 votes
1 answer
523 views

Cost threshold for parallelism and wait stats

We have an Stored Proc on our prod server (SQL Server 2014) that takes 3hr 40m (on a Sunday: low server activity) to run (collection job costs on 220 jobs with each requiring 6 SP calls, so 13200 SP ...
mark1234's user avatar
  • 211
1 vote
1 answer
97 views

SSIS: Can't save "parallel load" option in Oracle destination with Attunity driver, how to solve this?

I am working on an existing SSIS package that has proven to run successfully, however, due to data volume issues, I would like to try enabling the "parallel load" option by selecting the ...
adolf garlic 's user avatar
1 vote
0 answers
547 views

Bottleneck when inserting many rows into different tables

TLDR: I want to bulk insert into many initially empty tables at once, without the performance of each insert being affected too badly. I seem to be hitting a performance bottleneck I can't yet explain....
Ben's user avatar
  • 11
1 vote
0 answers
109 views

Calculating forecast data in parallel

I'd like to ask about how to calculate forecast data in parallel in a relational DB like PostgreSQL, which seems like a very typical problem when doing forecasts. Let's say we're trying to get the ...
Jack's user avatar
  • 111
1 vote
1 answer
50 views

Is there a way to have SQL work in parallelism with two computers?

Amateur here. I have a couple of the same old 4 core computers that I am trying to use to beef up some automatic query scripts that run every evening for my hobby. I recently found MPICH to have the ...
David Frick's user avatar
1 vote
1 answer
780 views

Parallel insert and delete into staging tables

I work on a small system that receives quite a lot of data from multiple sources (hundreds of them). Depending on few factors I can get anywhere from few rows to a couple of thousands (in fact ...
Jędrzej Dudkiewicz's user avatar
1 vote
0 answers
165 views

GTID ignore error on parallel replication

Im getting an error in replication where GTID and parallel replications are enabled. Now I want to skip one transaction. I didn't find a way to do that. Last_SQL_Error: Worker 48 failed executing ...
TheDataGuy's user avatar
  • 1,940
1 vote
0 answers
351 views

Parallel LOAD DATA to different memory tables very slow compared to single one

I'm using a fairly simple bash script to execute mysql load data infile to 11 different MEMORY engine tables. Example: load.sh echo "start $@ $(date)" mysql -p"mypass" -e "use db; set ...
Crashus's user avatar
  • 11
1 vote
0 answers
393 views

Parallel insert in db2

I am trying to import from 8 files and insert into a single table in db2. I am running 8 .sql files that will do the task, but only one of them actually seem to be running and inserting into table. ...
Rangesh Ananthalwar's user avatar
0 votes
0 answers
54 views

Does the engine compare the cost threshold for parallelism setting with each operator's cost?

SQL engine decides to run things in parallel threads when the cost exceeds the CTFP setting. Say the CTFP value is 100. Does the engine compare this threshold with each operator cost and then decide ...
variable's user avatar
  • 3,634
0 votes
0 answers
40 views

How to fix high px deq slave session stats in Oracle RDS

We are seeing some high percentage of "px deq slave session stats" waits in our Production database.(this maintained on AWS RDS). Due to which outage is happening many times from searching ...
Arun's user avatar
  • 13
0 votes
0 answers
185 views

PostgreSQL: Parallel Workers Error

For the last two days one of our query causing parallel workers related errors as below, 2024-01-04 13:58:45.988 CST,"user_a","testdb",108428,"10.0.0.1:46480",6596490c....
mediocre's user avatar
  • 105
0 votes
0 answers
225 views

Forced Serialization on SQL Server 2019

I ran Brent Ozar's BlitzCache and it identified a query that is executing with Forced Serialization. I'm assuming it is because of the Calendar functions contained with the query. I was looking for ...
Don's user avatar
  • 161
0 votes
0 answers
77 views

What is the difference between statement level and object level parallel hint for a select Statement

Does object level parallel hint affect the table settings for parallelism? Will statement level parallel hint apply to only that particular query and won't affect any default settings?
Doofenshmirtz's user avatar
0 votes
1 answer
2k views

PostgreSQL: running SQL statements in parallel in a SQL script

I use a for loop in a script to compute geometries according to values in a series, and insert the results into a table (t1). Is there a way to rewrite the loop below using parallelism? What approach ...
Marc's user avatar
  • 3
0 votes
0 answers
178 views

Are mutliple client-based queries multithreaded internally in PostgreSQL?

This seems to suggest, that if i connect to the same instance of Postgres running on my machine, multiple connections from clients are handled in parallel. Is that true? Can I start 4 shells running ...
Clebo Sevic's user avatar
0 votes
0 answers
105 views

SQL Server query tuning strategy

I'm new to query tuning and have been using Brent Ozar's tools to identify costly queries on the server. One query that is executed numerous times per minute is triggering warnings for: Forced ...
Cody's user avatar
  • 11
0 votes
1 answer
182 views

Should I change parallelism (MaxDop and Threshold)?

Seems like I have problems with parallelism because largest wait types are CXCONSUMER and CXPACKET. Server has 8 cores. So I am planning to bump up Cost Threshold to 50 and MaxDop to 4 Currently I ...
Serdia's user avatar
  • 687
0 votes
0 answers
215 views

Update query performance improvement (MS-SQL)

UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1 ON M.MARKID = S.MARKID AND S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> '...
Luv Gupta's user avatar
0 votes
0 answers
452 views

How to trigger Mysql parallel query function to start?

Please help,how to trigger Mysql parallel query function to start? MySQL version is 8.0.19. mysql> show variables like 'innodb_parallel_read_threads'; +------------------------------+-------+ | ...
HappyGuitarBoy's user avatar
0 votes
0 answers
493 views

NonParallelPlanReason = MaxDOPSetToOne when maxdop set to 4

Running SQL Server 2014 on both servers. While I was attempting to find out why a procedure was running faster on Server B than it was on Server A I found that on Server B the query plan says the ...
Lumpy's user avatar
  • 2,109
0 votes
1 answer
1k views

Turn on multi-threading in Oracle for ETL purposes

As you can see from this question, I was able to configure multi-threading in SQL Server and build a workflow in Informatica to multi-thread a job to move data faster into SQL Server. Multi-thread ...
JustBeingHelpful's user avatar
0 votes
2 answers
3k views

How to run INSERT INTO AS SELECT in parallel on Oracle?

I have a very large table AUD$ that I want to copy it to another empty one with the same structure AUD_BACKUP. INSERT INTO TEST_USER.AUD_BACKUP SELECT * FROM SYS.AUD$; COMMIT; How do I use ...
korodani's user avatar
  • 123
-1 votes
2 answers
79 views

Index Seeks cost of OR operator

I have a query which is slow and it takes 15 seconds to execute. The query fetches data from a view and in the where clause, I have few conditions which includes OR and AND. exec sp_executesql N'...
user9516827's user avatar
  • 1,315
-1 votes
1 answer
275 views

SQL Server Parallelism Disabled

I came up with a strange issue where the performance of select queries was degraded after creating missing indexes on tables. Initially, it looks like all the queries are affected but when looked into ...
vishal savsani's user avatar
-1 votes
1 answer
462 views

MAXDOP and Physical Cores

I've got an instance built with 4 cores. We have some queries that are going parallel. I know changing the MAXDOP to 0 will single thread the queries. My question is whether the 4 cores will no ...
DSW's user avatar
  • 1