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
2 answers
51 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 ...
user avatar
  • 2,130
2 votes
2 answers
31 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 ...
user avatar
  • 1,450
1 vote
3 answers
51 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) ...
user avatar
0 votes
0 answers
20 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?
user avatar
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 ...
user avatar
  • 30.3k
6 votes
2 answers
443 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 ...
user avatar
2 votes
1 answer
60 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 ...
user avatar
6 votes
1 answer
261 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". ...
user avatar
1 vote
1 answer
36 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 ...
user avatar
0 votes
1 answer
174 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 ...
user avatar
  • 3
0 votes
0 answers
31 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 ...
user avatar
0 votes
0 answers
36 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 ...
user avatar
  • 11
1 vote
3 answers
314 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 ...
user avatar
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 ...
user avatar
  • 443
0 votes
1 answer
71 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 ...
user avatar
  • 637
0 votes
2 answers
39 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 ...
user avatar
  • 155
1 vote
2 answers
516 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 ...
user avatar
  • 203
0 votes
1 answer
73 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 ...
user avatar
0 votes
1 answer
94 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 ...
user avatar
  • 1,199
2 votes
0 answers
133 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 ...
user avatar
  • 21
4 votes
1 answer
403 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 ...
user avatar
  • 935
0 votes
0 answers
55 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 <> '...
user avatar
0 votes
2 answers
254 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'); ...
user avatar
2 votes
3 answers
704 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 ...
user avatar
  • 1,450
0 votes
1 answer
50 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(...
user avatar
  • 1,294
0 votes
1 answer
687 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 ...
user avatar
1 vote
1 answer
81 views

how to sum times in EXPLAIN for Postgres parallel query

I'm having trouble understanding this EXPLAIN on Postgres 12.3: EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(1) FROM mytable WHERE page ~ 'foo'; This is a 22GB table with 30 million rows, on a ...
user avatar
  • 189
0 votes
2 answers
477 views

importing postgres tar file throws parallel not supported in postgres 12

I'm trying to import a db that someone sent me: testdb.tar (43GB size) First I've imported using: pg_restore --host localhost --port 5432 --username postgres --dbname testdb --role postgres --no-...
user avatar
  • 123
0 votes
0 answers
218 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....
user avatar
  • 1
0 votes
3 answers
65 views

Is there performance loss in out of sequence inserted rows (MySQL InnoDB)

I am trying to migrate from a MySQL AWS RDS instance with a huge SSD and too much excess space down to a small one, and data migration is the only method. There are four tables in the range of 330GB-...
user avatar
0 votes
1 answer
637 views

How many parallel workers can I have on Postgres?

I working on a server with Ubuntu, 320 GB SSD, 6 cores and 16GB Ram, but Postgres is having issues some queries that take very long time to run, and with parallel queries, that are increasing the ...
user avatar
0 votes
1 answer
45 views

Should an update statement query plan ever go parallel?

If an update statement's query plan does go parallel, does it guarantee a communication buffer resource deadlock, or is that determined by whether the parallel plan is using all cores?
user avatar
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 ...
user avatar
  • 243
3 votes
1 answer
436 views

How can INDEX rebuilds be going parallel when MAXDOP is set to 1

I am periodically running into a THREADPOOL wait issue with a SharePoint data store of hundreds of databases using a SQL Server 2008R2 Standard Edition instance (recently migrated to an Azure VM). It ...
user avatar
  • 2,008
0 votes
0 answers
220 views

parallelism in sql server in user defined function

a four page long query (that creates two temporal tables, and then joins them) in sql server runs in a dozen of seconds (thanks to parallel execution). The same query packed into a user defined ...
user avatar
  • 820
3 votes
2 answers
518 views

How many threads being used while query execution?

I have AdventureWorks2014 database installed on my test SQL Server 2014. I am planning to execute the following query: SELECT * FROM Sales.SalesOrderDetail sod INNER JOIN Production.Product p ON sod....
user avatar
  • 1,163
1 vote
0 answers
19 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 ...
user avatar
  • 111
1 vote
1 answer
757 views

Run stored procedures in other schemas from stored procedure [closed]

I have a current process: a large procedure in one schema. There is a plan to have A, B1, B2, B3 schemas (B-schemas are identical, could be B-n schemas) in Oracle 12c database. Schemas B1, B2, B3 have ...
user avatar
3 votes
2 answers
1k views

Is PostgreSQL function parallel safety intended for functions which update data?

I am having some trouble understanding function parallel safety. I can understand what it does - which is to specify whether the function will be executed by the parallel master node or whether the ...
user avatar
  • 407
0 votes
0 answers
187 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'; +------------------------------+-------+ | ...
user avatar
0 votes
1 answer
363 views

Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?

I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will ...
user avatar
  • 24.1k
3 votes
1 answer
3k views

Query runs in parallel but it is showing as blocked by itself

I have a query which runs in parallel with a MAXDOP of 8. When i looked at sp_who2 i see that the same session ID is being repeated several times(>8) with different connection id. I used the below ...
user avatar
  • 1,285
0 votes
1 answer
98 views

which command to execute to run multiple OLAP queries?

I have 10 sql files that contains different OLAP queries. I want to run them as a batch. Meaning by batch, Postgres will consider each file as a batch and execute the queries at once (depends on its ...
user avatar
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....
user avatar
  • 77.9k
3 votes
1 answer
115 views

Save CPU cycles on SQL Server Query Plans (Better without Stats than with on wide tables)

Assume that a query in question has a very wide set of columns. The query looks like the following: set statistics io,time on; Select a.Col1, a.Col2, b.Col3, b.Col4, c.Col5 From FirstWideTable a ...
user avatar
  • 131
0 votes
1 answer
286 views

Multiple SPIDs in sp_who2, Deadlocks and Parallelism

There were reports of slowness on our web application - screens hanging, some unable to log in. It's an OLTP system with approx 50 concurrent users. Upon investigation I found a few things: Number ...
user avatar
  • 11
2 votes
1 answer
973 views

Is parallel execution enabled or disabled by default in Oracle Standard Edition database version 12.1?

Oracle Standard Edition 12.1 on Red Hat Linux 7 According to the documentation, parallel execution is enabled by default in Oracle SE 12.1. However, when looking at the database parameters, the ...
user avatar
1 vote
1 answer
3k views

Number of Worker threads available in PostgreSQL

In SQL Server we can have many worker threads per CPU core. Does anyone know if the same is true in PostgreSQL and can point me at a reference? (assume version 11). The background is that we have ...
user avatar
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 ...
user avatar
1 vote
1 answer
42 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 ...
user avatar