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
vote
0answers
10 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 ...
1
vote
1answer
35 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 ...
2
votes
2answers
56 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 ...
0
votes
0answers
30 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'; +------------------------------+-------+ | ...
0
votes
1answer
67 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 ...
2
votes
1answer
2k 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 ...
0
votes
1answer
38 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 ...
11
votes
2answers
622 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....
3
votes
1answer
93 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 ...
0
votes
1answer
65 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 ...
2
votes
1answer
166 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 ...
0
votes
1answer
335 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 ...
2
votes
1answer
194 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 ...
0
votes
1answer
39 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 ...
0
votes
1answer
193 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 ...
0
votes
1answer
85 views

Query runs in serial and parallel mode in two identical databases on the same instance [duplicate]

I have two databases in the same SQL Server instance. One is a test database with a smaller number of records and total size of 500GB. Other one is production database with a size of 2.2 TB. Both ...
3
votes
2answers
109 views

Is it normal to share CPUs on a VM of SQL Server?

Our IT set up a SQL Server as a VM on a big VMWare box that contains other VMs. The CPUs are setup as shared. As a result, any queries that might need multiple CPUs take 30x longer than if I limited ...
4
votes
1answer
75 views

DMVs - Relating tasks to branches

In this excellent article, Paul White demonstrates branches in parallel plans. https://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads At the most fine grained level, we can look ...
1
vote
1answer
67 views

SQL Server - Update with Output not working in Parallel environment

I know that the output parameter does not guarantees parallelism logic. And the logic I want to implement is failing in a multi thread environment. To test this, just open 2 windows and execute them ...
0
votes
1answer
170 views

Postgres parallel seq scan making high CPU Utilization

When I run a select query which runs on a view in Postgres, it is starting 3 processes for a single query, which is causing high CPU utilization when multiple job runs in parallel. How to avoid using ...
0
votes
1answer
59 views

Part of parallel execution plan has serial portion

I have a big linq to sql query which runs in parallel mode. It is returning results in 8 seconds and it is pretty good. I noticed in the execution plan that the extreme right part(from where execution ...
1
vote
0answers
152 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 ...
0
votes
1answer
51 views

Assign foreign keys only after populating database

I am porting a MongoDB database over to a PostgreSQL (relational) database. I would like to perform this task concurrently, and I have developed code with parallelism in mind, but dependencies between ...
9
votes
2answers
476 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 ...
10
votes
3answers
411 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
vote
2answers
392 views

Parallel replication is not working on a 5.7 slave from a 5.7 master

Note: Following are the parameters that we turned ON on the 5.7 replica for parallel replication to happen: slave_preserve_commit_order = 1 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers =...
9
votes
1answer
193 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?
0
votes
1answer
58 views

Can a query whose execution plan uses parallelization use the cores of a processor on a another server that's part of the same Availability Group?

If I have two servers (server A and server B) that are part of the same Availability Group failover cluster, and a query running on server A has an execution plan that uses parallelization, is it ...
1
vote
0answers
51 views

Complex query won't run after migration [closed]

I have migrated an old, ugly query from a 2008 SQL Server to a new 2016 instance. The query runs fine on the old database even when the query plan has to be recreated. On the new database the query ...
3
votes
1answer
3k 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. ...
3
votes
1answer
504 views

Identify Query Waiting on resources

I have some query with CXCONSUMER wait time when i ran sp_whoisactive as shown in the screenshot below. When i checked the status it is suspended.That means it is waiting for some process to release ...
1
vote
0answers
80 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 ...
0
votes
1answer
421 views

Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?

I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without ...
4
votes
1answer
459 views

Eliminating a intra query deadlock, can I avoid restricting parallelism?

I have an update statement that is generating intra query parallelism deadlock. Based on this link I believe my two options to resolve this issue is to force SQL Server to avoid parallelism via an ...
1
vote
2answers
73 views

Functions causing Serial plan.What to use instead? [closed]

I have few stored procedures in my SQL Server database.I see that these stored procedures are running in serial mode from their execution plan. For example : CREATE PROCEDURE [dbo].[DemoProc] @...
3
votes
1answer
476 views

Forced serialization on inline table valued function

Apologies if this is a stupid question. We are attempting to convert our multi-statement table valued functions (MSTVFs) to inline table valued functions (ITVFs), to avoid the forced serialization of ...
6
votes
1answer
5k 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 ...
11
votes
2answers
3k 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 ...
1
vote
1answer
93 views

Finding Serial plan from Plan cache

My application goes slow sometimes and when i check the query plan plan for a particular operation,i see that serial pans got generated and gets stored in the plan cache and this is being taken for ...
2
votes
2answers
930 views

Why might a parallel pg_restore command take hours longer to complete its non-parallel equivalent?

I used pg_restore to restore a 50 GB db from a directory backup using the following command which used 4 jobs: pg_restore -d analytics -U postgres -j 4 -v "D:\Program Files\PostgreSQL\10\backups\...
2
votes
3answers
343 views

Serial Plan in plan cache

I have a query which was executing slowly earlier. Later I found out that it was not running parallel and this is making the query execution slow. The query involves a big view and then querying the ...
3
votes
1answer
581 views

How to handle SQL Server Intrasession Parallelism Deadlock?

I am struggling to deal with a intrasession deadlock coming from parallelism on SQL Server 2017. The query is not handwritten but generated by an ORM. Please have a look at the query plan over here....
2
votes
2answers
727 views

What can I do about high CXPACKET waits?

We are experiencing some slowness on our MS SQL Server 2016 database, I have been using Brent Ozar's first aid kit to do some initial troubleshooting. I am seeing a high amounts of CXPACKET wait ...
-1
votes
2answers
70 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'...
3
votes
2answers
53 views

Very simple read-only SELECT not run in parallel in postrgresql

I am trying to do a simple SELECT on a large table, but it is not being run in parallel: my_db=> explain (analyze, buffers) select value from my_table; Seq Scan on my_table (cost=0.00..94433357.34 ...
0
votes
0answers
212 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 ...
3
votes
1answer
133 views

CPU vs Elapsed & Parallelism

I have read threads here and here and I get that elapsed time is the total duration of the task - and if the elapsed time is less than CPU time, the query went parallel. After typing that, I was ...
5
votes
1answer
186 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....
6
votes
2answers
532 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 ...
4
votes
1answer
118 views

Degree of parallelism and scheduler usage

Is it ever possible for a SQL Server query running with an effective degree of parallelism x to have parallel workers assigned to more than x distinct schedulers? Even if the execution plan has many ...