Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [parallelism]

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

0
votes
1answer
50 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
38 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 ...
1
vote
1answer
164 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
92 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 ...
0
votes
0answers
37 views

Read Deadlock XML - Intra-query Parallelism

I encountered several deadlocks in my application over the last 2 months and i checked the Extended Events to find the details. I tried to understand the dealock XML and i came across this great ...
0
votes
0answers
17 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
41 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
96 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
57 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
150 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 ...
3
votes
1answer
2k 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 ...
10
votes
2answers
2k 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
58 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 ...
1
vote
1answer
140 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
176 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
218 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
286 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
59 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
45 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
107 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
76 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 ...
4
votes
1answer
100 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....
5
votes
2answers
308 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 ...
3
votes
1answer
78 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 ...
0
votes
0answers
77 views

IMPDP Parallel insert operation

I am hoping someone can shed some light on this. When running an import into an oracle database, I sometimes see different behavior based on the parallel option. Some times, I will see multiple ...
1
vote
1answer
164 views

How do I synchronize parallel imports with COPY? [closed]

I have ~20 tables that get built at the same time but using different psql processes (via bash script) at the first stage with something like: create table foo_1 as (select ....); -- via psql process ...
8
votes
1answer
693 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, ...
0
votes
0answers
1k views

Oracle: insert /*+ parallel */ and delete /*+ parallel */ freeze during execution

I'm struggling with an issue with two queries executed on an Oracle DB. Both queries have the /*+ parallel */ directive. The two queries are executed in sequence: before I have the delete /* + ...
0
votes
1answer
383 views

Parallel Sequential Scan on SELECT statement, but not on SELECT … INTO [duplicate]

The following query EXPLAIN SELECT c.global_id FROM centers_res17 AS c LEFT JOIN regions_res17 AS r ON ST_Contains(r.wkb_geometry, c.wkb_geometry) WHERE r.global_id IS NULL; says that a Parallel ...
10
votes
2answers
1k 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 ...
-1
votes
1answer
78 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 ...
3
votes
1answer
2k views

SQL Parallel Stored Procedure execution using CLR - Performance

We have a main analytics SP that calls 100 other sub SPs, all of which act upon the same set of data (Claims), check it for some business rules, and output discrepancies. The SPs need not all be run ...
2
votes
2answers
202 views

Insert result of a parallellized select query into a table, in Postgresql

According to https://www.postgresql.org/docs/current/static/when-can-parallel-query-be-used.html, "Even when it is in general possible for parallel query plans to be generated, the planner will ...
0
votes
1answer
920 views

UNION Queries run in Parallel form or not

I have three tables and I want to select data from these three tables in parallel form to increase the performance. I'm running a query like this. SELECT * FROM table1 UNION ALL SELECT * FROM table2 ...
26
votes
2answers
2k 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 ...
1
vote
1answer
533 views

Oracle data pump import parallel configuration

I am trying to track down a cause for a difference in oracle impdp processing, and I am not finding anything, so I am wondering if anyone here can explain the cause of the differences. At times I ...
6
votes
2answers
864 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 ...
4
votes
1answer
236 views

Can multiple cores be used by PostgreSQL to run code in sections (cursors) of a table effecting parallelism?

Let's say I have a 1 million record table. Each record is uniquely identifiable by id. If the table was divided into 10 cursors and 10 requests were made by a web client for each cursor to say change ...
1
vote
0answers
201 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 ...
3
votes
1answer
3k views

SQL Server Threads and Degree Of Parallelism

My understanding about SQL Server threads is that SQL Server breaks down an execution request within a session into several tasks and associates each task with a worker thread. So SQL Server always ...
2
votes
1answer
78 views

SQL Server using Parallel Plan for Cost less than 5

I have a pivot query that uses a parallel plan even though the cost is less than 5. The Cost Threshold for Parallelism is set to default of 5, and MAXDOP is 0. SELECT Column1, Column2 Column3, ...
1
vote
0answers
197 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 ...
7
votes
6answers
11k 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 ...
2
votes
1answer
348 views

Why does my parallel query sometimes process billions of extra rows?

I was running some data prep queries when I noticed that one of them sometimes ran much longer than expected. The query should have finished in seconds but sometimes would take minutes to run. Here is ...
12
votes
2answers
596 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). ...
1
vote
4answers
603 views

Load testing: The performance of a query is decreasing as the number of parallel executions is increasing

I'm seeing a slow down when doing load testing on my app. I have a query that takes about 350ms to run, but what I run it in parallel 8 times (not to mention 32 times), it goes up to 2.5 seconds. I ...
-1
votes
1answer
183 views

Why parallel seq scan is not chosen by planner for jsonb type of fields? [closed]

I have a table with jsonb type of column (filter_data) and I set set force_parallel_mode to true set max_parallel_workers_per_gather to 8 Then I execute explain analyze select stream_packet_id from ...
1
vote
1answer
108 views

When I have 6 cores and they do a table scan and merge afterwards, does every core scan the whole table?

I remember that I once had a missing index on a table and one query was taking ages. In the execution plan I saw that a table scan and a merge was done. Statistics from IO showed me that every core ...
0
votes
1answer
130 views

The relations between sessions, DOP and sets of parallel execution servers

From How Parallel Execution Works: The number of parallel execution servers in one set is the degree of parallelism (DOP) What is the meaning of "one set" in the document? Does it have a ...
0
votes
1answer
414 views

PARALLEL_MIN_TIME_THRESHOLD parameter when AUTO or MANUAL mode

From How Parallel Execution Works: The following is a summary of parallel statement processing when parallel degree policy is set to automatic. A SQL statement is issued. The statement is ...