Questions tagged [parallelism]

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

Filter by
Sorted by
Tagged with
-1 votes
0 answers
43 views

What are some ways to manage High cxpacket waits followed by minimal latch_ex, sos_scheduler yield?

I have SQL server 2019 standard edition with 16 CPUs, 128GB RAM (max SQL memory), overall 176GB RAM, and SSD disks. CTFP is 50, and maxdop is 8. There is no cpu pressure in terms of worker threads. ...
variable's user avatar
  • 3,561
-1 votes
1 answer
92 views

How do we set the cost threshold of parallelism value in SQL Server?

Does this value, say 50, mean that parallelization will be activated when the 51st query comes?
Merve's user avatar
  • 55
0 votes
0 answers
50 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,561
0 votes
1 answer
44 views

Do threads that have finished processing rows, or threads that don't process any rows consume 4ms cpu time slice?

Say for a operator there are 8 threads. One control thread and 7 worker threads. Do threads that don't have any work, that is - either of the below consume the 4ms cpu time?: Thread that has finished ...
variable's user avatar
  • 3,561
0 votes
0 answers
13 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
91 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....
Basil Titus's user avatar
4 votes
0 answers
547 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
0 votes
1 answer
198 views

Postgres using a single process despite having 64 cores and setting max_worker_processes to 32

I'm running Postgres 14 on Ubuntu with 64 cores/ 128 threads and 128GB of memory. I need to run large aggregation jobs daily. The one table that I need to aggregate on is current 1.4 billion rows. ...
JohnAllen's user avatar
  • 101
0 votes
1 answer
35 views

Is there any method to perform something parallel in postgresql extension?

According to this link, to perform something parallel in postgresql extension, multi-thread isn't an option. So is there any other method? Such as some API and usage about multi-process? Maybe let me ...
Qiu Yangfan's user avatar
3 votes
1 answer
1k views

How to make a Postgresql function parallelly running?

I have thousands tables with same structure, and I need to add a index on each. In order to simplify my work, I code a function like this: CREATE OR REPLACE FUNCTION createIndexTD( IN tbl_name ...
Leon's user avatar
  • 347
3 votes
1 answer
455 views

Is this function PARALLEL SAFE?

Looking at the function f_jsonb_select_keys from this answer: How to select sub-object with given keys from JSONB? https://www.postgresql.org/docs/current/functions-aggregate.html Aggregate functions ...
jian's user avatar
  • 437
5 votes
1 answer
306 views

SQL Server generates a query plan with parallelism and TOP statement

I have two simple queries: SELECT TOP(20) * FROM Clients ORDER BY City SELECT TOP(20) Id, Name, City FROM Clients ORDER BY City In the first case I get a result like this Id Name City many more ...
Bert de Saffel's user avatar
1 vote
1 answer
421 views

Why does parallelism cause lock escalation and where is the tipping point?

I use the customized Stack Overflow database (180GB) and run a simple update query: (there is only a clustered index on the Users table) Begin Tran Update U set U.Reputation=100000 from StackOverflow....
Aksel Rohtsalu's user avatar
4 votes
2 answers
2k views

Is SQL Server able to use internal parallelism for an update statement?

I am struggling to find the best way to migrate some "varchar" columns to "nvarchar". One of the options i am using is to add new nvarchar column(s) then update the values from the ...
Edward Hayrabedian's user avatar
2 votes
1 answer
630 views

How to reap benefits of parallelism for PostgreSQL DELETE?

I have a fairly niche use case of a (AWS Aurora) PostgreSQL database and we are running into performance issues that I have discovered a "clever hack" (not actually clever) to work around ...
Alistair Hutton's user avatar
3 votes
0 answers
528 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
0 votes
0 answers
205 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
-3 votes
1 answer
75 views

What kind of operations parallelism occur?

What kind of operations parallelism occur?
LB_'s user avatar
  • 3
0 votes
1 answer
145 views

How to update multiple databases in parallel

As part of performance regression testing of a backup product, one stage of the workflow requires updating a single table that exists in multiple databases on a single SQL Server instance and then ...
MarcC's user avatar
  • 11
0 votes
1 answer
337 views

exclusiveness of multiple table locks for a single transaction

Consider an api that performs writes to multiple tables: BEGIN TRANSACTION; UPDATE ... TABLE1 ... VALUES ...; UPDATE ... TABLE2 ... VALUES ...; UPDATE ... TABLE3 ... VALUES ...; UPDATE ... ...
juztcode's user avatar
  • 109
0 votes
1 answer
157 views

Parallel and faster evaluation of SELECTing boolean comparing count

I have a table: CREATE TABLE IF NOT EXISTS blacklist ( id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('blacklist_sequence') PRIMARY KEY, device_id ...
Hasan Can Saral's user avatar
0 votes
1 answer
98 views

Parallel Insert is not showing in MSSQL Stored Procedure execution Plan

As you can see in the above screenshot, this query provides a parallel insert: INSERT INTO #StateAllocationData WITH (TABLOCK) (ProjectID,StateId,StateLineDescriptionId,PartnerID,Value) SELECT @...
sarath sanil's user avatar
3 votes
0 answers
158 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,960
1 vote
1 answer
89 views

Potentially Improper Row Count Read in Parallel Bitmap Hash Match Plan

I am trying to determine whether or not this is an execution plan reporting bug or an actual feature that is working as intended. In my execution plan, when using MAXDOP 1, I am seeing the entire ...
Data Dill's user avatar
  • 231
-1 votes
2 answers
530 views

Why does MAXDOP = 0 result in high CXPACKET wait time?

During a parallel query execution (whether or not to go parallel is decided by the cost threshold for parallelism) MAXDOP limits the number of tasks per request. Assuming cost threshold for ...
variable's user avatar
  • 3,561
2 votes
2 answers
93 views

Find Which Queries will be affected by Proposed Change to CTFP

The Cost Threshold for Parallelism Setting on one of our servers is set at what is generally considered too low (15) and we are considering increasing to 50 in hope to reduce CPU as it is becoming ...
SE1986's user avatar
  • 1,960
1 vote
3 answers
160 views

Optimizing a PostgreSQL query with many small lookups

I have a database about books and book sales. The tables look like this: CREATE TABLE purchase ( person_id integer, book_id integer, CONSTRAINT purchase_pk PRIMARY KEY (person_id, book_id) ...
Eerik Sven Puudist's user avatar
0 votes
0 answers
71 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
12 votes
1 answer
911 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 ...
Joe Obbish's user avatar
  • 32.1k
7 votes
2 answers
1k 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 ...
Lukas.Navratil's user avatar
3 votes
1 answer
1k views

Running parallel functions with union all

I'm trying to run 3 functions at the same time using parallelism and union all. I've read that union queries are candidates for parallelism but i can't get it to work. The query looks like this (the ...
Francisco Flores's user avatar
6 votes
1 answer
417 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". ...
Anil Raghavan's user avatar
1 vote
1 answer
96 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
0 votes
1 answer
1k 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
176 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
99 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
1 vote
3 answers
1k views

SQL Server not creating parallel plan with OPTION(MAXDOP 20)

We have a UAT3 server hosted on VM with 8 sockets and 20 processer, we have similar UAT2 server hosted on the same VM with same configuration. We are running a below query on both the server select ...
pradeep singh's user avatar
6 votes
2 answers
647 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 ...
D-K's user avatar
  • 503
0 votes
1 answer
175 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
2 answers
353 views

multiple tables INSERT and DELETE in parallel?

I have 25 tables I refresh in one transaction. Few of them refresh much longer than others. I wonder would it possible to refresh "short-timers" in parallel of "long-timers" in ...
Jakub P's user avatar
  • 157
1 vote
2 answers
2k views

How can a cheap function in a SELECT make the whole query slow?

I'm using Postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts). I can't figure out why Query2 below is so much slower than Query1. They ...
LaVache's user avatar
  • 303
0 votes
1 answer
403 views

How to confirm the positive impact of changing Cost Threshold for parallelism to a value higher than default value of 5

We have a DB Server where we found a simple update query going parallel concurrently and causing blockings and deadlock. The cost of the query was around 7. CTFP on the server was set to default value ...
sachin-SQLServernewbiee's user avatar
0 votes
1 answer
287 views

How to reduce the maximum degree of parallelism at session level?

I have inherited a legacy application that includes a job that creates performance issue at instance level. The DBA told us about this and our team needs to fix the stored procedure that is the most ...
Alexei's user avatar
  • 1,261
2 votes
0 answers
225 views

Unhandled Deadlocks with SQL Server 2017 [closed]

We’re running into issues with unresolved deadlocks on the most recent version of SQL-Server 2017, has anybody seen a problem like this before? Detailed explanation: We migrated our database from ...
benrik's user avatar
  • 21
4 votes
1 answer
772 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 ...
jericzech's user avatar
  • 945
0 votes
0 answers
211 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
2 answers
1k views

Cardinality , Parallel Hint alternative for MS-SQL

UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS (SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1 WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y'); ...
Luv Gupta's user avatar
3 votes
3 answers
2k views

Slow Parallel SQL Server query, almost instant in serial

I have a SQL Server query as follows (obfuscated): UPDATE [TABLE1] SET [COLUMN1] = CAST('N' AS CHAR(1)) FROM [TABLE1] WHERE (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR ...
SE1986's user avatar
  • 1,960
0 votes
1 answer
505 views

Does ' /*+parallel(<Specific Table >,4)*/ ' have better impact on query performance in comparison with simple parallel hint '/*+parallel(4)*/'?

I have three tables with below structure: create table customer_info (customer_num number, customer_firstName varchar2(100), customer_lastName varchar2(100), branch_code varchar2(...
Pantea's user avatar
  • 1,412
0 votes
1 answer
1k views

How to get a parallel nested loop in postgres 12?

I have a lateral join, and I would like each subselect to execute in parallel. According to the docs (https://www.postgresql.org/docs/12/parallel-plans.html), nested loops can be parallelized: Just ...
Charles Cooper's user avatar

1
2 3 4 5