Questions tagged [parallelism]
Reducing run times by breaking a problem into parts that can be completed by separate execution paths simultaneously.
197
questions
2
votes
1
answer
69
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 ...
- 261
3
votes
1
answer
69
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 ...
- 381
4
votes
1
answer
144
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 ...
1
vote
1
answer
148
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....
4
votes
2
answers
1k
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 ...
2
votes
1
answer
166
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 ...
3
votes
0
answers
277
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 ...
- 31
0
votes
0
answers
92
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 ...
- 159
-3
votes
1
answer
69
views
0
votes
1
answer
94
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 ...
- 11
0
votes
1
answer
174
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 ... ...
- 109
0
votes
1
answer
136
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 ...
- 161
0
votes
1
answer
49
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 @...
- 11
3
votes
0
answers
83
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....
- 1,741
1
vote
1
answer
55
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 ...
- 193
-1
votes
2
answers
298
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 ...
- 3,225
2
votes
2
answers
73
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 ...
- 1,741
1
vote
3
answers
119
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)
...
0
votes
0
answers
50
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?
12
votes
1
answer
860
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 ...
- 31.7k
6
votes
2
answers
771
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 ...
- 325
2
votes
1
answer
742
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 ...
6
votes
1
answer
352
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".
...
1
vote
1
answer
79
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 ...
- 257
0
votes
1
answer
854
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 ...
- 3
0
votes
0
answers
143
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 ...
- 113
0
votes
0
answers
75
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 ...
- 11
1
vote
3
answers
777
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 ...
6
votes
2
answers
619
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 ...
- 463
0
votes
1
answer
144
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 ...
- 647
0
votes
2
answers
231
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 ...
- 155
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 ...
- 283
0
votes
1
answer
177
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 ...
0
votes
1
answer
228
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 ...
- 1,261
2
votes
0
answers
190
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 ...
- 21
4
votes
1
answer
615
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 ...
- 945
0
votes
0
answers
121
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 <> '...
0
votes
2
answers
767
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');
...
2
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
...
- 1,741
0
votes
1
answer
182
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(...
- 1,402
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 ...
- 163
1
vote
1
answer
148
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 ...
- 189
0
votes
2
answers
934
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-...
- 123
1
vote
0
answers
413
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....
- 11
0
votes
3
answers
93
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-...
- 201
0
votes
1
answer
3k
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 ...
0
votes
1
answer
77
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?
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 ...
- 243
3
votes
1
answer
773
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 ...
- 2,139
3
votes
2
answers
1k
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....
- 1,233